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 1344442 2012-05-30 20:17:35Z oheger $
087     */
088    public class DatabaseConfiguration extends AbstractConfiguration
089    {
090        /** The datasource to connect to the database. */
091        private final DataSource datasource;
092    
093        /** The name of the table containing the configurations. */
094        private final String table;
095    
096        /** The column containing the name of the configuration. */
097        private final String nameColumn;
098    
099        /** The column containing the keys. */
100        private final String keyColumn;
101    
102        /** The column containing the values. */
103        private final String valueColumn;
104    
105        /** The name of the configuration. */
106        private final 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            ResultSet rs = 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                rs = pstmt.executeQuery();
237    
238                List<Object> results = new ArrayList<Object>();
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 contains the list delimiter
249                        Iterator<?> it = PropertyConverter.toIterator(value, getListDelimiter());
250                        while (it.hasNext())
251                        {
252                            results.add(it.next());
253                        }
254                    }
255                }
256    
257                if (!results.isEmpty())
258                {
259                    result = (results.size() > 1) ? results : results.get(0);
260                }
261            }
262            catch (SQLException e)
263            {
264                fireError(EVENT_READ_PROPERTY, key, null, e);
265            }
266            finally
267            {
268                close(conn, pstmt, rs);
269            }
270    
271            return result;
272        }
273    
274        /**
275         * Adds a property to this configuration. If this causes a database error,
276         * an error event will be generated of type {@code EVENT_ADD_PROPERTY}
277         * with the causing exception. The event's {@code propertyName} is
278         * set to the passed in property key, the {@code propertyValue}
279         * points to the passed in value.
280         *
281         * @param key the property key
282         * @param obj the value of the property to add
283         */
284        @Override
285        protected void addPropertyDirect(String key, Object obj)
286        {
287            // build the query
288            StringBuilder query = new StringBuilder("INSERT INTO " + table);
289            if (nameColumn != null)
290            {
291                query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
292            }
293            else
294            {
295                query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
296            }
297    
298            Connection conn = null;
299            PreparedStatement pstmt = null;
300    
301            try
302            {
303                conn = getConnection();
304    
305                // bind the parameters
306                pstmt = conn.prepareStatement(query.toString());
307                int index = 1;
308                if (nameColumn != null)
309                {
310                    pstmt.setString(index++, name);
311                }
312                pstmt.setString(index++, key);
313                pstmt.setString(index++, String.valueOf(obj));
314    
315                pstmt.executeUpdate();
316                commitIfRequired(conn);
317            }
318            catch (SQLException e)
319            {
320                fireError(EVENT_ADD_PROPERTY, key, obj, e);
321            }
322            finally
323            {
324                // clean up
325                close(conn, pstmt, null);
326            }
327        }
328    
329        /**
330         * Adds a property to this configuration. This implementation will
331         * temporarily disable list delimiter parsing, so that even if the value
332         * contains the list delimiter, only a single record will be written into
333         * the managed table. The implementation of {@code getProperty()}
334         * will take care about delimiters. So list delimiters are fully supported
335         * by {@code DatabaseConfiguration}, but internally treated a bit
336         * differently.
337         *
338         * @param key the key of the new property
339         * @param value the value to be added
340         */
341        @Override
342        public void addProperty(String key, Object value)
343        {
344            boolean parsingFlag = isDelimiterParsingDisabled();
345            try
346            {
347                if (value instanceof String)
348                {
349                    // temporarily disable delimiter parsing
350                    setDelimiterParsingDisabled(true);
351                }
352                super.addProperty(key, value);
353            }
354            finally
355            {
356                setDelimiterParsingDisabled(parsingFlag);
357            }
358        }
359    
360        /**
361         * Checks if this configuration is empty. If this causes a database error,
362         * an error event will be generated of type {@code EVENT_READ_PROPERTY}
363         * with the causing exception. Both the event's {@code propertyName}
364         * and {@code propertyValue} will be undefined.
365         *
366         * @return a flag whether this configuration is empty.
367         */
368        public boolean isEmpty()
369        {
370            boolean empty = true;
371    
372            // build the query
373            StringBuilder query = new StringBuilder("SELECT count(*) FROM " + table);
374            if (nameColumn != null)
375            {
376                query.append(" WHERE " + nameColumn + "=?");
377            }
378    
379            Connection conn = null;
380            PreparedStatement pstmt = null;
381            ResultSet rs = null;
382    
383            try
384            {
385                conn = getConnection();
386    
387                // bind the parameters
388                pstmt = conn.prepareStatement(query.toString());
389                if (nameColumn != null)
390                {
391                    pstmt.setString(1, name);
392                }
393    
394                rs = pstmt.executeQuery();
395    
396                if (rs.next())
397                {
398                    empty = rs.getInt(1) == 0;
399                }
400            }
401            catch (SQLException e)
402            {
403                fireError(EVENT_READ_PROPERTY, null, null, e);
404            }
405            finally
406            {
407                // clean up
408                close(conn, pstmt, rs);
409            }
410    
411            return empty;
412        }
413    
414        /**
415         * Checks whether this configuration contains the specified key. If this
416         * causes a database error, an error event will be generated of type
417         * {@code EVENT_READ_PROPERTY} with the causing exception. The
418         * event's {@code propertyName} will be set to the passed in key, the
419         * {@code propertyValue} will be undefined.
420         *
421         * @param key the key to be checked
422         * @return a flag whether this key is defined
423         */
424        public boolean containsKey(String key)
425        {
426            boolean found = false;
427    
428            // build the query
429            StringBuilder query = new StringBuilder("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
430            if (nameColumn != null)
431            {
432                query.append(" AND " + nameColumn + "=?");
433            }
434    
435            Connection conn = null;
436            PreparedStatement pstmt = null;
437            ResultSet rs = null;
438    
439            try
440            {
441                conn = getConnection();
442    
443                // bind the parameters
444                pstmt = conn.prepareStatement(query.toString());
445                pstmt.setString(1, key);
446                if (nameColumn != null)
447                {
448                    pstmt.setString(2, name);
449                }
450    
451                rs = pstmt.executeQuery();
452    
453                found = rs.next();
454            }
455            catch (SQLException e)
456            {
457                fireError(EVENT_READ_PROPERTY, key, null, e);
458            }
459            finally
460            {
461                // clean up
462                close(conn, pstmt, rs);
463            }
464    
465            return found;
466        }
467    
468        /**
469         * Removes the specified value from this configuration. If this causes a
470         * database error, an error event will be generated of type
471         * {@code EVENT_CLEAR_PROPERTY} with the causing exception. The
472         * event's {@code propertyName} will be set to the passed in key, the
473         * {@code propertyValue} will be undefined.
474         *
475         * @param key the key of the property to be removed
476         */
477        @Override
478        protected void clearPropertyDirect(String key)
479        {
480            // build the query
481            StringBuilder query = new StringBuilder("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
482            if (nameColumn != null)
483            {
484                query.append(" AND " + nameColumn + "=?");
485            }
486    
487            Connection conn = null;
488            PreparedStatement pstmt = null;
489    
490            try
491            {
492                conn = getConnection();
493    
494                // bind the parameters
495                pstmt = conn.prepareStatement(query.toString());
496                pstmt.setString(1, key);
497                if (nameColumn != null)
498                {
499                    pstmt.setString(2, name);
500                }
501    
502                pstmt.executeUpdate();
503                commitIfRequired(conn);
504            }
505            catch (SQLException e)
506            {
507                fireError(EVENT_CLEAR_PROPERTY, key, null, e);
508            }
509            finally
510            {
511                // clean up
512                close(conn, pstmt, null);
513            }
514        }
515    
516        /**
517         * Removes all entries from this configuration. If this causes a database
518         * error, an error event will be generated of type
519         * {@code EVENT_CLEAR} with the causing exception. Both the
520         * event's {@code propertyName} and the {@code propertyValue}
521         * will be undefined.
522         */
523        @Override
524        public void clear()
525        {
526            fireEvent(EVENT_CLEAR, null, null, true);
527            // build the query
528            StringBuilder query = new StringBuilder("DELETE FROM " + table);
529            if (nameColumn != null)
530            {
531                query.append(" WHERE " + nameColumn + "=?");
532            }
533    
534            Connection conn = null;
535            PreparedStatement pstmt = null;
536    
537            try
538            {
539                conn = getConnection();
540    
541                // bind the parameters
542                pstmt = conn.prepareStatement(query.toString());
543                if (nameColumn != null)
544                {
545                    pstmt.setString(1, name);
546                }
547    
548                pstmt.executeUpdate();
549                commitIfRequired(conn);
550            }
551            catch (SQLException e)
552            {
553                fireError(EVENT_CLEAR, null, null, e);
554            }
555            finally
556            {
557                // clean up
558                close(conn, pstmt, null);
559            }
560            fireEvent(EVENT_CLEAR, null, null, false);
561        }
562    
563        /**
564         * Returns an iterator with the names of all properties contained in this
565         * configuration. If this causes a database
566         * error, an error event will be generated of type
567         * {@code EVENT_READ_PROPERTY} with the causing exception. Both the
568         * event's {@code propertyName} and the {@code propertyValue}
569         * will be undefined.
570         * @return an iterator with the contained keys (an empty iterator in case
571         * of an error)
572         */
573        public Iterator<String> getKeys()
574        {
575            Collection<String> keys = new ArrayList<String>();
576    
577            // build the query
578            StringBuilder query = new StringBuilder("SELECT DISTINCT " + keyColumn + " FROM " + table);
579            if (nameColumn != null)
580            {
581                query.append(" WHERE " + nameColumn + "=?");
582            }
583    
584            Connection conn = null;
585            PreparedStatement pstmt = null;
586            ResultSet rs = null;
587    
588            try
589            {
590                conn = getConnection();
591    
592                // bind the parameters
593                pstmt = conn.prepareStatement(query.toString());
594                if (nameColumn != null)
595                {
596                    pstmt.setString(1, name);
597                }
598    
599                rs = pstmt.executeQuery();
600    
601                while (rs.next())
602                {
603                    keys.add(rs.getString(1));
604                }
605            }
606            catch (SQLException e)
607            {
608                fireError(EVENT_READ_PROPERTY, null, null, e);
609            }
610            finally
611            {
612                // clean up
613                close(conn, pstmt, rs);
614            }
615    
616            return keys.iterator();
617        }
618    
619        /**
620         * Returns the used {@code DataSource} object.
621         *
622         * @return the data source
623         * @since 1.4
624         */
625        public DataSource getDatasource()
626        {
627            return datasource;
628        }
629    
630        /**
631         * Returns a {@code Connection} object. This method is called when
632         * ever the database is to be accessed. This implementation returns a
633         * connection from the current {@code DataSource}.
634         *
635         * @return the {@code Connection} object to be used
636         * @throws SQLException if an error occurs
637         * @since 1.4
638         * @deprecated Use a custom data source to change the connection used by the
639         * class. To be removed in Commons Configuration 2.0
640         */
641        @Deprecated
642        protected Connection getConnection() throws SQLException
643        {
644            return getDatasource().getConnection();
645        }
646    
647        /**
648         * Close the specified database objects.
649         * Avoid closing if null and hide any SQLExceptions that occur.
650         *
651         * @param conn The database connection to close
652         * @param stmt The statement to close
653         * @param rs the result set to close
654         */
655        private void close(Connection conn, Statement stmt, ResultSet rs)
656        {
657            try
658            {
659                if (rs != null)
660                {
661                    rs.close();
662                }
663            }
664            catch (SQLException e)
665            {
666                getLogger().error("An error occurred on closing the result set", e);
667            }
668    
669            try
670            {
671                if (stmt != null)
672                {
673                    stmt.close();
674                }
675            }
676            catch (SQLException e)
677            {
678                getLogger().error("An error occured on closing the statement", e);
679            }
680    
681            try
682            {
683                if (conn != null)
684                {
685                    conn.close();
686                }
687            }
688            catch (SQLException e)
689            {
690                getLogger().error("An error occured on closing the connection", e);
691            }
692        }
693    
694        /**
695         * Performs a commit if needed. This method is called after updates of the
696         * managed database table. If the configuration should perform commits, it
697         * does so now.
698         *
699         * @param conn the active connection
700         * @throws SQLException if an error occurs
701         */
702        private void commitIfRequired(Connection conn) throws SQLException
703        {
704            if (isDoCommits())
705            {
706                conn.commit();
707            }
708        }
709    }