    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    }