    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    }