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