001    /*
002     * Licensed to the Apache Software Foundation (ASF) under one or more
003     * contributor license agreements.  See the NOTICE file distributed with
004     * this work for additional information regarding copyright ownership.
005     * The ASF licenses this file to You under the Apache License, Version 2.0
006     * (the "License"); you may not use this file except in compliance with
007     * the License.  You may obtain a copy of the License at
008     *
009     *     http://www.apache.org/licenses/LICENSE-2.0
010     *
011     * Unless required by applicable law or agreed to in writing, software
012     * distributed under the License is distributed on an "AS IS" BASIS,
013     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014     * See the License for the specific language governing permissions and
015     * limitations under the License.
016     */
017    
018    package org.apache.commons.configuration;
019    
020    import java.sql.Connection;
021    import java.sql.PreparedStatement;
022    import java.sql.ResultSet;
023    import java.sql.SQLException;
024    import java.sql.Statement;
025    import java.util.ArrayList;
026    import java.util.Collection;
027    import java.util.Iterator;
028    import java.util.List;
029    
030    import javax.sql.DataSource;
031    
032    import org.apache.commons.collections.CollectionUtils;
033    import org.apache.commons.logging.LogFactory;
034    
035    /**
036     * Configuration stored in a database. The properties are retrieved from a
037     * table containing at least one column for the keys, and one column for the
038     * values. It's possible to store several configurations in the same table by
039     * adding a column containing the name of the configuration. The name of the
040     * table and the columns is specified in the constructor.
041     *
042     * <h4>Example 1 - One configuration per table</h4>
043     *
044     * <pre>
045     * CREATE TABLE myconfig (
046     *     `key`   VARCHAR NOT NULL PRIMARY KEY,
047     *     `value` VARCHAR
048     * );
049     *
050     * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
051     *
052     *
053     * Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
054     * String value = config.getString("foo");
055     * </pre>
056     *
057     * <h4>Example 2 - Multiple configurations per table</h4>
058     *
059     * <pre>
060     * CREATE TABLE myconfigs (
061     *     `name`  VARCHAR NOT NULL,
062     *     `key`   VARCHAR NOT NULL,
063     *     `value` VARCHAR,
064     *     CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
065     * );
066     *
067     * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
068     * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
069     *
070     *
071     * Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
072     * String value1 = conf.getString("key1");
073     *
074     * Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
075     * String value2 = conf.getString("key2");
076     * </pre>
077     * The configuration can be instructed to perform commits after database updates.
078     * This is achieved by setting the <code>commits</code> parameter of the
079     * constructors to <b>true</b>. If commits should not be performed (which is the
080     * default behavior), it should be ensured that the connections returned by the
081     * <code>DataSource</code> are in auto-commit mode.
082     *
083     * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
084     * @since 1.0
085     *
086     * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
087     * @version $Revision: 1158118 $, $Date: 2011-08-16 08:13:22 +0200 (Di, 16. Aug 2011) $
088     */
089    public class DatabaseConfiguration extends AbstractConfiguration
090    {
091        /** The datasource to connect to the database. */
092        private DataSource datasource;
093    
094        /** The name of the table containing the configurations. */
095        private String table;
096    
097        /** The column containing the name of the configuration. */
098        private String nameColumn;
099    
100        /** The column containing the keys. */
101        private String keyColumn;
102    
103        /** The column containing the values. */
104        private String valueColumn;
105    
106        /** The name of the configuration. */
107        private String name;
108    
109        /** A flag whether commits should be performed by this configuration. */
110        private final boolean doCommits;
111    
112        /**
113         * Build a configuration from a table containing multiple configurations.
114         * No commits are performed by the new configuration instance.
115         *
116         * @param datasource    the datasource to connect to the database
117         * @param table         the name of the table containing the configurations
118         * @param nameColumn    the column containing the name of the configuration
119         * @param keyColumn     the column containing the keys of the configuration
120         * @param valueColumn   the column containing the values of the configuration
121         * @param name          the name of the configuration
122         */
123        public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
124                String keyColumn, String valueColumn, String name)
125        {
126            this(datasource, table, nameColumn, keyColumn, valueColumn, name, false);
127        }
128    
129        /**
130         * Creates a new instance of <code>DatabaseConfiguration</code> that operates on
131         * a database table containing multiple configurations.
132         *
133         * @param datasource the <code>DataSource</code> to connect to the database
134         * @param table the name of the table containing the configurations
135         * @param nameColumn the column containing the name of the configuration
136         * @param keyColumn the column containing the keys of the configuration
137         * @param valueColumn the column containing the values of the configuration
138         * @param name the name of the configuration
139         * @param commits a flag whether the configuration should perform a commit
140         *        after a database update
141         */
142        public DatabaseConfiguration(DataSource datasource, String table,
143                String nameColumn, String keyColumn, String valueColumn,
144                String name, boolean commits)
145        {
146            this.datasource = datasource;
147            this.table = table;
148            this.nameColumn = nameColumn;
149            this.keyColumn = keyColumn;
150            this.valueColumn = valueColumn;
151            this.name = name;
152            doCommits = commits;
153            setLogger(LogFactory.getLog(getClass()));
154            addErrorLogListener();  // log errors per default
155        }
156    
157        /**
158         * Build a configuration from a table.
159         *
160         * @param datasource    the datasource to connect to the database
161         * @param table         the name of the table containing the configurations
162         * @param keyColumn     the column containing the keys of the configuration
163         * @param valueColumn   the column containing the values of the configuration
164         */
165        public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
166        {
167            this(datasource, table, null, keyColumn, valueColumn, null);
168        }
169    
170        /**
171         * Creates a new instance of <code>DatabaseConfiguration</code> that
172         * operates on a database table containing a single configuration only.
173         *
174         * @param datasource the <code>DataSource</code> to connect to the database
175         * @param table the name of the table containing the configurations
176         * @param keyColumn the column containing the keys of the configuration
177         * @param valueColumn the column containing the values of the configuration
178         * @param commits a flag whether the configuration should perform a commit
179         *        after a database update
180         */
181        public DatabaseConfiguration(DataSource datasource, String table,
182                String keyColumn, String valueColumn, boolean commits)
183        {
184            this(datasource, table, null, keyColumn, valueColumn, null, commits);
185        }
186    
187        /**
188         * Returns a flag whether this configuration performs commits after database
189         * updates.
190         *
191         * @return a flag whether commits are performed
192         */
193        public boolean isDoCommits()
194        {
195            return doCommits;
196        }
197    
198        /**
199         * Returns the value of the specified property. If this causes a database
200         * error, an error event will be generated of type
201         * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
202         * event's <code>propertyName</code> is set to the passed in property key,
203         * the <code>propertyValue</code> is undefined.
204         *
205         * @param key the key of the desired property
206         * @return the value of this property
207         */
208        public Object getProperty(String key)
209        {
210            Object result = null;
211    
212            // build the query
213            StringBuffer query = new StringBuffer("SELECT * FROM ");
214            query.append(table).append(" WHERE ");
215            query.append(keyColumn).append("=?");
216            if (nameColumn != null)
217            {
218                query.append(" AND " + nameColumn + "=?");
219            }
220    
221            Connection conn = null;
222            PreparedStatement pstmt = null;
223    
224            try
225            {
226                conn = getConnection();
227    
228                // bind the parameters
229                pstmt = conn.prepareStatement(query.toString());
230                pstmt.setString(1, key);
231                if (nameColumn != null)
232                {
233                    pstmt.setString(2, name);
234                }
235    
236                ResultSet rs = pstmt.executeQuery();
237    
238                List results = new ArrayList();
239                while (rs.next())
240                {
241                    Object value = rs.getObject(valueColumn);
242                    if (isDelimiterParsingDisabled())
243                    {
244                        results.add(value);
245                    }
246                    else
247                    {
248                        // Split value if it containts the list delimiter
249                        CollectionUtils.addAll(results, PropertyConverter.toIterator(value, getListDelimiter()));
250                    }
251                }
252    
253                if (!results.isEmpty())
254                {
255                    result = (results.size() > 1) ? results : results.get(0);
256                }
257            }
258            catch (SQLException e)
259            {
260                fireError(EVENT_READ_PROPERTY, key, null, e);
261            }
262            finally
263            {
264                close(conn, pstmt);
265            }
266    
267            return result;
268        }
269    
270        /**
271         * Adds a property to this configuration. If this causes a database error,
272         * an error event will be generated of type <code>EVENT_ADD_PROPERTY</code>
273         * with the causing exception. The event's <code>propertyName</code> is
274         * set to the passed in property key, the <code>propertyValue</code>
275         * points to the passed in value.
276         *
277         * @param key the property key
278         * @param obj the value of the property to add
279         */
280        protected void addPropertyDirect(String key, Object obj)
281        {
282            // build the query
283            StringBuffer query = new StringBuffer("INSERT INTO " + table);
284            if (nameColumn != null)
285            {
286                query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
287            }
288            else
289            {
290                query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
291            }
292    
293            Connection conn = null;
294            PreparedStatement pstmt = null;
295    
296            try
297            {
298                conn = getConnection();
299    
300                // bind the parameters
301                pstmt = conn.prepareStatement(query.toString());
302                int index = 1;
303                if (nameColumn != null)
304                {
305                    pstmt.setString(index++, name);
306                }
307                pstmt.setString(index++, key);
308                pstmt.setString(index++, String.valueOf(obj));
309    
310                pstmt.executeUpdate();
311                commitIfRequired(conn);
312            }
313            catch (SQLException e)
314            {
315                fireError(EVENT_ADD_PROPERTY, key, obj, e);
316            }
317            finally
318            {
319                // clean up
320                close(conn, pstmt);
321            }
322        }
323    
324        /**
325         * Adds a property to this configuration. This implementation will
326         * temporarily disable list delimiter parsing, so that even if the value
327         * contains the list delimiter, only a single record will be written into
328         * the managed table. The implementation of <code>getProperty()</code>
329         * will take care about delimiters. So list delimiters are fully supported
330         * by <code>DatabaseConfiguration</code>, but internally treated a bit
331         * differently.
332         *
333         * @param key the key of the new property
334         * @param value the value to be added
335         */
336        public void addProperty(String key, Object value)
337        {
338            boolean parsingFlag = isDelimiterParsingDisabled();
339            try
340            {
341                if (value instanceof String)
342                {
343                    // temporarily disable delimiter parsing
344                    setDelimiterParsingDisabled(true);
345                }
346                super.addProperty(key, value);
347            }
348            finally
349            {
350                setDelimiterParsingDisabled(parsingFlag);
351            }
352        }
353    
354        /**
355         * Checks if this configuration is empty. If this causes a database error,
356         * an error event will be generated of type <code>EVENT_READ_PROPERTY</code>
357         * with the causing exception. Both the event's <code>propertyName</code>
358         * and <code>propertyValue</code> will be undefined.
359         *
360         * @return a flag whether this configuration is empty.
361         */
362        public boolean isEmpty()
363        {
364            boolean empty = true;
365    
366            // build the query
367            StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table);
368            if (nameColumn != null)
369            {
370                query.append(" WHERE " + nameColumn + "=?");
371            }
372    
373            Connection conn = null;
374            PreparedStatement pstmt = null;
375    
376            try
377            {
378                conn = getConnection();
379    
380                // bind the parameters
381                pstmt = conn.prepareStatement(query.toString());
382                if (nameColumn != null)
383                {
384                    pstmt.setString(1, name);
385                }
386    
387                ResultSet rs = pstmt.executeQuery();
388    
389                if (rs.next())
390                {
391                    empty = rs.getInt(1) == 0;
392                }
393            }
394            catch (SQLException e)
395            {
396                fireError(EVENT_READ_PROPERTY, null, null, e);
397            }
398            finally
399            {
400                // clean up
401                close(conn, pstmt);
402            }
403    
404            return empty;
405        }
406    
407        /**
408         * Checks whether this configuration contains the specified key. If this
409         * causes a database error, an error event will be generated of type
410         * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
411         * event's <code>propertyName</code> will be set to the passed in key, the
412         * <code>propertyValue</code> will be undefined.
413         *
414         * @param key the key to be checked
415         * @return a flag whether this key is defined
416         */
417        public boolean containsKey(String key)
418        {
419            boolean found = false;
420    
421            // build the query
422            StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
423            if (nameColumn != null)
424            {
425                query.append(" AND " + nameColumn + "=?");
426            }
427    
428            Connection conn = null;
429            PreparedStatement pstmt = null;
430    
431            try
432            {
433                conn = getConnection();
434    
435                // bind the parameters
436                pstmt = conn.prepareStatement(query.toString());
437                pstmt.setString(1, key);
438                if (nameColumn != null)
439                {
440                    pstmt.setString(2, name);
441                }
442    
443                ResultSet rs = pstmt.executeQuery();
444    
445                found = rs.next();
446            }
447            catch (SQLException e)
448            {
449                fireError(EVENT_READ_PROPERTY, key, null, e);
450            }
451            finally
452            {
453                // clean up
454                close(conn, pstmt);
455            }
456    
457            return found;
458        }
459    
460        /**
461         * Removes the specified value from this configuration. If this causes a
462         * database error, an error event will be generated of type
463         * <code>EVENT_CLEAR_PROPERTY</code> with the causing exception. The
464         * event's <code>propertyName</code> will be set to the passed in key, the
465         * <code>propertyValue</code> will be undefined.
466         *
467         * @param key the key of the property to be removed
468         */
469        protected void clearPropertyDirect(String key)
470        {
471            // build the query
472            StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
473            if (nameColumn != null)
474            {
475                query.append(" AND " + nameColumn + "=?");
476            }
477    
478            Connection conn = null;
479            PreparedStatement pstmt = null;
480    
481            try
482            {
483                conn = getConnection();
484    
485                // bind the parameters
486                pstmt = conn.prepareStatement(query.toString());
487                pstmt.setString(1, key);
488                if (nameColumn != null)
489                {
490                    pstmt.setString(2, name);
491                }
492    
493                pstmt.executeUpdate();
494                commitIfRequired(conn);
495            }
496            catch (SQLException e)
497            {
498                fireError(EVENT_CLEAR_PROPERTY, key, null, e);
499            }
500            finally
501            {
502                // clean up
503                close(conn, pstmt);
504            }
505        }
506    
507        /**
508         * Removes all entries from this configuration. If this causes a database
509         * error, an error event will be generated of type
510         * <code>EVENT_CLEAR</code> with the causing exception. Both the
511         * event's <code>propertyName</code> and the <code>propertyValue</code>
512         * will be undefined.
513         */
514        public void clear()
515        {
516            fireEvent(EVENT_CLEAR, null, null, true);
517            // build the query
518            StringBuffer query = new StringBuffer("DELETE FROM " + table);
519            if (nameColumn != null)
520            {
521                query.append(" WHERE " + nameColumn + "=?");
522            }
523    
524            Connection conn = null;
525            PreparedStatement pstmt = null;
526    
527            try
528            {
529                conn = getConnection();
530    
531                // bind the parameters
532                pstmt = conn.prepareStatement(query.toString());
533                if (nameColumn != null)
534                {
535                    pstmt.setString(1, name);
536                }
537    
538                pstmt.executeUpdate();
539                commitIfRequired(conn);
540            }
541            catch (SQLException e)
542            {
543                fireError(EVENT_CLEAR, null, null, e);
544            }
545            finally
546            {
547                // clean up
548                close(conn, pstmt);
549            }
550            fireEvent(EVENT_CLEAR, null, null, false);
551        }
552    
553        /**
554         * Returns an iterator with the names of all properties contained in this
555         * configuration. If this causes a database
556         * error, an error event will be generated of type
557         * <code>EVENT_READ_PROPERTY</code> with the causing exception. Both the
558         * event's <code>propertyName</code> and the <code>propertyValue</code>
559         * will be undefined.
560         * @return an iterator with the contained keys (an empty iterator in case
561         * of an error)
562         */
563        public Iterator getKeys()
564        {
565            Collection keys = new ArrayList();
566    
567            // build the query
568            StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
569            if (nameColumn != null)
570            {
571                query.append(" WHERE " + nameColumn + "=?");
572            }
573    
574            Connection conn = null;
575            PreparedStatement pstmt = null;
576    
577            try
578            {
579                conn = getConnection();
580    
581                // bind the parameters
582                pstmt = conn.prepareStatement(query.toString());
583                if (nameColumn != null)
584                {
585                    pstmt.setString(1, name);
586                }
587    
588                ResultSet rs = pstmt.executeQuery();
589    
590                while (rs.next())
591                {
592                    keys.add(rs.getString(1));
593                }
594            }
595            catch (SQLException e)
596            {
597                fireError(EVENT_READ_PROPERTY, null, null, e);
598            }
599            finally
600            {
601                // clean up
602                close(conn, pstmt);
603            }
604    
605            return keys.iterator();
606        }
607    
608        /**
609         * Returns the used <code>DataSource</code> object.
610         *
611         * @return the data source
612         * @since 1.4
613         */
614        public DataSource getDatasource()
615        {
616            return datasource;
617        }
618    
619        /**
620         * Returns a <code>Connection</code> object. This method is called when
621         * ever the database is to be accessed. This implementation returns a
622         * connection from the current <code>DataSource</code>.
623         *
624         * @return the <code>Connection</code> object to be used
625         * @throws SQLException if an error occurs
626         * @since 1.4
627         * @deprecated Use a custom data source to change the connection used by the
628         * class. To be removed in Commons Configuration 2.0
629         */
630        protected Connection getConnection() throws SQLException
631        {
632            return getDatasource().getConnection();
633        }
634    
635        /**
636         * Close a <code>Connection</code> and, <code>Statement</code>.
637         * Avoid closing if null and hide any SQLExceptions that occur.
638         *
639         * @param conn The database connection to close
640         * @param stmt The statement to close
641         */
642        private void close(Connection conn, Statement stmt)
643        {
644            try
645            {
646                if (stmt != null)
647                {
648                    stmt.close();
649                }
650            }
651            catch (SQLException e)
652            {
653                getLogger().error("An error occured on closing the statement", e);
654            }
655    
656            try
657            {
658                if (conn != null)
659                {
660                    conn.close();
661                }
662            }
663            catch (SQLException e)
664            {
665                getLogger().error("An error occured on closing the connection", e);
666            }
667        }
668    
669        /**
670         * Performs a commit if needed. This method is called after updates of the
671         * managed database table. If the configuration should perform commits, it
672         * does so now.
673         *
674         * @param conn the active connection
675         * @throws SQLException if an error occurs
676         */
677        private void commitIfRequired(Connection conn) throws SQLException
678        {
679            if (isDoCommits())
680            {
681                conn.commit();
682            }
683        }
684    }