View Javadoc

    1   /*
    2    * Licensed to the Apache Software Foundation (ASF) under one or more
    3    * contributor license agreements.  See the NOTICE file distributed with
    4    * this work for additional information regarding copyright ownership.
    5    * The ASF licenses this file to You under the Apache License, Version 2.0
    6    * (the "License"); you may not use this file except in compliance with
    7    * the License.  You may obtain a copy of the License at
    8    *
    9    *     http://www.apache.org/licenses/LICENSE-2.0
    10   *
    11   * Unless required by applicable law or agreed to in writing, software
    12   * distributed under the License is distributed on an "AS IS" BASIS,
    13   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    14   * See the License for the specific language governing permissions and
    15   * limitations under the License.
    16   */
    17
    18  package org.apache.commons.configuration;
    19
    20  import java.sql.Connection;
    21  import java.sql.PreparedStatement;
    22  import java.sql.ResultSet;
    23  import java.sql.SQLException;
    24  import java.sql.Statement;
    25  import java.util.ArrayList;
    26  import java.util.Collection;
    27  import java.util.Iterator;
    28  import java.util.List;
    29
    30  import javax.sql.DataSource;
    31
    32  import org.apache.commons.collections.CollectionUtils;
    33  import org.apache.commons.logging.LogFactory;
    34
    35  /***
    36   * Configuration stored in a database. The properties are retrieved from a
    37   * table containing at least one column for the keys, and one column for the
    38   * values. It's possible to store several configurations in the same table by
    39   * adding a column containing the name of the configuration. The name of the
    40   * table and the columns is specified in the constructor.
    41   *
    42   * <h4>Example 1 - One configuration per table</h4>
    43   *
    44   * <pre>
    45   * CREATE TABLE myconfig (
    46   *     `key`   VARCHAR NOT NULL PRIMARY KEY,
    47   *     `value` VARCHAR
    48   * );
    49   *
    50   * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
    51   *
    52   *
    53   * Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
    54   * String value = config.getString("foo");
    55   * </pre>
    56   *
    57   * <h4>Example 2 - Multiple configurations per table</h4>
    58   *
    59   * <pre>
    60   * CREATE TABLE myconfigs (
    61   *     `name`  VARCHAR NOT NULL,
    62   *     `key`   VARCHAR NOT NULL,
    63   *     `value` VARCHAR,
    64   *     CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
    65   * );
    66   *
    67   * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
    68   * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
    69   *
    70   *
    71   * Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
    72   * String value1 = conf.getString("key1");
    73   *
    74   * Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
    75   * String value2 = conf.getString("key2");
    76   * </pre>
    77   * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
    78   * @since 1.0
    79   *
    80   * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
    81   * @version $Revision: 613503 $, $Date: 2008-01-20 05:39:08 +0100 (So, 20 Jan 2008) $
    82   */
    83  public class DatabaseConfiguration extends AbstractConfiguration
    84  {
    85      /*** The datasource to connect to the database. */
    86      private DataSource datasource;
    87
    88      /*** The name of the table containing the configurations. */
    89      private String table;
    90
    91      /*** The column containing the name of the configuration. */
    92      private String nameColumn;
    93
    94      /*** The column containing the keys. */
    95      private String keyColumn;
    96
    97      /*** The column containing the values. */
    98      private String valueColumn;
    99
    100     /*** The name of the configuration. */
    101     private String name;
    102
    103     /***
    104      * Build a configuration from a table containing multiple configurations.
    105      *
    106      * @param datasource    the datasource to connect to the database
    107      * @param table         the name of the table containing the configurations
    108      * @param nameColumn    the column containing the name of the configuration
    109      * @param keyColumn     the column containing the keys of the configuration
    110      * @param valueColumn   the column containing the values of the configuration
    111      * @param name          the name of the configuration
    112      */
    113     public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
    114             String keyColumn, String valueColumn, String name)
    115     {
    116         this.datasource = datasource;
    117         this.table = table;
    118         this.nameColumn = nameColumn;
    119         this.keyColumn = keyColumn;
    120         this.valueColumn = valueColumn;
    121         this.name = name;
    122         setLogger(LogFactory.getLog(getClass()));
    123         addErrorLogListener();  // log errors per default
    124     }
    125
    126     /***
    127      * Build a configuration from a table.-
    128      *
    129      * @param datasource    the datasource to connect to the database
    130      * @param table         the name of the table containing the configurations
    131      * @param keyColumn     the column containing the keys of the configuration
    132      * @param valueColumn   the column containing the values of the configuration
    133      */
    134     public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
    135     {
    136         this(datasource, table, null, keyColumn, valueColumn, null);
    137     }
    138
    139     /***
    140      * Returns the value of the specified property. If this causes a database
    141      * error, an error event will be generated of type
    142      * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
    143      * event's <code>propertyName</code> is set to the passed in property key,
    144      * the <code>propertyValue</code> is undefined.
    145      *
    146      * @param key the key of the desired property
    147      * @return the value of this property
    148      */
    149     public Object getProperty(String key)
    150     {
    151         Object result = null;
    152
    153         // build the query
    154         StringBuffer query = new StringBuffer("SELECT * FROM ");
    155         query.append(table).append(" WHERE ");
    156         query.append(keyColumn).append("=?");
    157         if (nameColumn != null)
    158         {
    159             query.append(" AND " + nameColumn + "=?");
    160         }
    161
    162         Connection conn = null;
    163         PreparedStatement pstmt = null;
    164
    165         try
    166         {
    167             conn = getConnection();
    168
    169             // bind the parameters
    170             pstmt = conn.prepareStatement(query.toString());
    171             pstmt.setString(1, key);
    172             if (nameColumn != null)
    173             {
    174                 pstmt.setString(2, name);
    175             }
    176
    177             ResultSet rs = pstmt.executeQuery();
    178
    179             List results = new ArrayList();
    180             while (rs.next())
    181             {
    182                 Object value = rs.getObject(valueColumn);
    183                 if (isDelimiterParsingDisabled())
    184                 {
    185                     results.add(value);
    186                 }
    187                 else
    188                 {
    189                     // Split value if it containts the list delimiter
    190                     CollectionUtils.addAll(results, PropertyConverter.toIterator(value, getListDelimiter()));
    191                 }
    192             }
    193
    194             if (!results.isEmpty())
    195             {
    196                 result = (results.size() > 1) ? results : results.get(0);
    197             }
    198         }
    199         catch (SQLException e)
    200         {
    201             fireError(EVENT_READ_PROPERTY, key, null, e);
    202         }
    203         finally
    204         {
    205             close(conn, pstmt);
    206         }
    207
    208         return result;
    209     }
    210
    211     /***
    212      * Adds a property to this configuration. If this causes a database error,
    213      * an error event will be generated of type <code>EVENT_ADD_PROPERTY</code>
    214      * with the causing exception. The event's <code>propertyName</code> is
    215      * set to the passed in property key, the <code>propertyValue</code>
    216      * points to the passed in value.
    217      *
    218      * @param key the property key
    219      * @param obj the value of the property to add
    220      */
    221     protected void addPropertyDirect(String key, Object obj)
    222     {
    223         // build the query
    224         StringBuffer query = new StringBuffer("INSERT INTO " + table);
    225         if (nameColumn != null)
    226         {
    227             query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
    228         }
    229         else
    230         {
    231             query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
    232         }
    233
    234         Connection conn = null;
    235         PreparedStatement pstmt = null;
    236
    237         try
    238         {
    239             conn = getConnection();
    240
    241             // bind the parameters
    242             pstmt = conn.prepareStatement(query.toString());
    243             int index = 1;
    244             if (nameColumn != null)
    245             {
    246                 pstmt.setString(index++, name);
    247             }
    248             pstmt.setString(index++, key);
    249             pstmt.setString(index++, String.valueOf(obj));
    250
    251             pstmt.executeUpdate();
    252         }
    253         catch (SQLException e)
    254         {
    255             fireError(EVENT_ADD_PROPERTY, key, obj, e);
    256         }
    257         finally
    258         {
    259             // clean up
    260             close(conn, pstmt);
    261         }
    262     }
    263
    264     /***
    265      * Adds a property to this configuration. This implementation will
    266      * temporarily disable list delimiter parsing, so that even if the value
    267      * contains the list delimiter, only a single record will be written into
    268      * the managed table. The implementation of <code>getProperty()</code>
    269      * will take care about delimiters. So list delimiters are fully supported
    270      * by <code>DatabaseConfiguration</code>, but internally treated a bit
    271      * differently.
    272      *
    273      * @param key the key of the new property
    274      * @param value the value to be added
    275      */
    276     public void addProperty(String key, Object value)
    277     {
    278         boolean parsingFlag = isDelimiterParsingDisabled();
    279         try
    280         {
    281             if (value instanceof String)
    282             {
    283                 // temporarily disable delimiter parsing
    284                 setDelimiterParsingDisabled(true);
    285             }
    286             super.addProperty(key, value);
    287         }
    288         finally
    289         {
    290             setDelimiterParsingDisabled(parsingFlag);
    291         }
    292     }
    293
    294     /***
    295      * Checks if this configuration is empty. If this causes a database error,
    296      * an error event will be generated of type <code>EVENT_READ_PROPERTY</code>
    297      * with the causing exception. Both the event's <code>propertyName</code>
    298      * and <code>propertyValue</code> will be undefined.
    299      *
    300      * @return a flag whether this configuration is empty.
    301      */
    302     public boolean isEmpty()
    303     {
    304         boolean empty = true;
    305
    306         // build the query
    307         StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table);
    308         if (nameColumn != null)
    309         {
    310             query.append(" WHERE " + nameColumn + "=?");
    311         }
    312
    313         Connection conn = null;
    314         PreparedStatement pstmt = null;
    315
    316         try
    317         {
    318             conn = getConnection();
    319
    320             // bind the parameters
    321             pstmt = conn.prepareStatement(query.toString());
    322             if (nameColumn != null)
    323             {
    324                 pstmt.setString(1, name);
    325             }
    326
    327             ResultSet rs = pstmt.executeQuery();
    328
    329             if (rs.next())
    330             {
    331                 empty = rs.getInt(1) == 0;
    332             }
    333         }
    334         catch (SQLException e)
    335         {
    336             fireError(EVENT_READ_PROPERTY, null, null, e);
    337         }
    338         finally
    339         {
    340             // clean up
    341             close(conn, pstmt);
    342         }
    343
    344         return empty;
    345     }
    346
    347     /***
    348      * Checks whether this configuration contains the specified key. If this
    349      * causes a database error, an error event will be generated of type
    350      * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
    351      * event's <code>propertyName</code> will be set to the passed in key, the
    352      * <code>propertyValue</code> will be undefined.
    353      *
    354      * @param key the key to be checked
    355      * @return a flag whether this key is defined
    356      */
    357     public boolean containsKey(String key)
    358     {
    359         boolean found = false;
    360
    361         // build the query
    362         StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
    363         if (nameColumn != null)
    364         {
    365             query.append(" AND " + nameColumn + "=?");
    366         }
    367
    368         Connection conn = null;
    369         PreparedStatement pstmt = null;
    370
    371         try
    372         {
    373             conn = getConnection();
    374
    375             // bind the parameters
    376             pstmt = conn.prepareStatement(query.toString());
    377             pstmt.setString(1, key);
    378             if (nameColumn != null)
    379             {
    380                 pstmt.setString(2, name);
    381             }
    382
    383             ResultSet rs = pstmt.executeQuery();
    384
    385             found = rs.next();
    386         }
    387         catch (SQLException e)
    388         {
    389             fireError(EVENT_READ_PROPERTY, key, null, e);
    390         }
    391         finally
    392         {
    393             // clean up
    394             close(conn, pstmt);
    395         }
    396
    397         return found;
    398     }
    399
    400     /***
    401      * Removes the specified value from this configuration. If this causes a
    402      * database error, an error event will be generated of type
    403      * <code>EVENT_CLEAR_PROPERTY</code> with the causing exception. The
    404      * event's <code>propertyName</code> will be set to the passed in key, the
    405      * <code>propertyValue</code> will be undefined.
    406      *
    407      * @param key the key of the property to be removed
    408      */
    409     public void clearProperty(String key)
    410     {
    411         // build the query
    412         StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
    413         if (nameColumn != null)
    414         {
    415             query.append(" AND " + nameColumn + "=?");
    416         }
    417
    418         Connection conn = null;
    419         PreparedStatement pstmt = null;
    420
    421         try
    422         {
    423             conn = getConnection();
    424
    425             // bind the parameters
    426             pstmt = conn.prepareStatement(query.toString());
    427             pstmt.setString(1, key);
    428             if (nameColumn != null)
    429             {
    430                 pstmt.setString(2, name);
    431             }
    432
    433             pstmt.executeUpdate();
    434         }
    435         catch (SQLException e)
    436         {
    437             fireError(EVENT_CLEAR_PROPERTY, key, null, e);
    438         }
    439         finally
    440         {
    441             // clean up
    442             close(conn, pstmt);
    443         }
    444     }
    445
    446     /***
    447      * Removes all entries from this configuration. If this causes a database
    448      * error, an error event will be generated of type
    449      * <code>EVENT_CLEAR</code> with the causing exception. Both the
    450      * event's <code>propertyName</code> and the <code>propertyValue</code>
    451      * will be undefined.
    452      */
    453     public void clear()
    454     {
    455         // build the query
    456         StringBuffer query = new StringBuffer("DELETE FROM " + table);
    457         if (nameColumn != null)
    458         {
    459             query.append(" WHERE " + nameColumn + "=?");
    460         }
    461
    462         Connection conn = null;
    463         PreparedStatement pstmt = null;
    464
    465         try
    466         {
    467             conn = getConnection();
    468
    469             // bind the parameters
    470             pstmt = conn.prepareStatement(query.toString());
    471             if (nameColumn != null)
    472             {
    473                 pstmt.setString(1, name);
    474             }
    475
    476             pstmt.executeUpdate();
    477         }
    478         catch (SQLException e)
    479         {
    480             fireError(EVENT_CLEAR, null, null, e);
    481         }
    482         finally
    483         {
    484             // clean up
    485             close(conn, pstmt);
    486         }
    487     }
    488
    489     /***
    490      * Returns an iterator with the names of all properties contained in this
    491      * configuration. If this causes a database
    492      * error, an error event will be generated of type
    493      * <code>EVENT_READ_PROPERTY</code> with the causing exception. Both the
    494      * event's <code>propertyName</code> and the <code>propertyValue</code>
    495      * will be undefined.
    496      * @return an iterator with the contained keys (an empty iterator in case
    497      * of an error)
    498      */
    499     public Iterator getKeys()
    500     {
    501         Collection keys = new ArrayList();
    502
    503         // build the query
    504         StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
    505         if (nameColumn != null)
    506         {
    507             query.append(" WHERE " + nameColumn + "=?");
    508         }
    509
    510         Connection conn = null;
    511         PreparedStatement pstmt = null;
    512
    513         try
    514         {
    515             conn = getConnection();
    516
    517             // bind the parameters
    518             pstmt = conn.prepareStatement(query.toString());
    519             if (nameColumn != null)
    520             {
    521                 pstmt.setString(1, name);
    522             }
    523
    524             ResultSet rs = pstmt.executeQuery();
    525
    526             while (rs.next())
    527             {
    528                 keys.add(rs.getString(1));
    529             }
    530         }
    531         catch (SQLException e)
    532         {
    533             fireError(EVENT_READ_PROPERTY, null, null, e);
    534         }
    535         finally
    536         {
    537             // clean up
    538             close(conn, pstmt);
    539         }
    540
    541         return keys.iterator();
    542     }
    543
    544     /***
    545      * Returns the used <code>DataSource</code> object.
    546      *
    547      * @return the data source
    548      * @since 1.4
    549      */
    550     public DataSource getDatasource()
    551     {
    552         return datasource;
    553     }
    554
    555     /***
    556      * Returns a <code>Connection</code> object. This method is called when
    557      * ever the database is to be accessed. This implementation returns a
    558      * connection from the current <code>DataSource</code>.
    559      *
    560      * @return the <code>Connection</code> object to be used
    561      * @throws SQLException if an error occurs
    562      * @since 1.4
    563      * @deprecated Use a custom data source to change the connection used by the
    564      * class. To be removed in Commons Configuration 2.0
    565      */
    566     protected Connection getConnection() throws SQLException
    567     {
    568         return getDatasource().getConnection();
    569     }
    570
    571     /***
    572      * Close a <code>Connection</code> and, <code>Statement</code>.
    573      * Avoid closing if null and hide any SQLExceptions that occur.
    574      *
    575      * @param conn The database connection to close
    576      * @param stmt The statement to close
    577      */
    578     private void close(Connection conn, Statement stmt)
    579     {
    580         try
    581         {
    582             if (stmt != null)
    583             {
    584                 stmt.close();
    585             }
    586         }
    587         catch (SQLException e)
    588         {
    589             getLogger().error("An error occured on closing the statement", e);
    590         }
    591
    592         try
    593         {
    594             if (conn != null)
    595             {
    596                 conn.close();
    597             }
    598         }
    599         catch (SQLException e)
    600         {
    601             getLogger().error("An error occured on closing the connection", e);
    602         }
    603     }
    604 }