View Javadoc

    1   /*
    2    * Copyright 2001-2004 The Apache Software Foundation.
    3    *
    4    * Licensed under the Apache License, Version 2.0 (the "License")
    5    * you may not use this file except in compliance with the License.
    6    * You may obtain a copy of the License at
    7    *
    8    *     http://www.apache.org/licenses/LICENSE-2.0
    9    *
    10   * Unless required by applicable law or agreed to in writing, software
    11   * distributed under the License is distributed on an "AS IS" BASIS,
    12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13   * See the License for the specific language governing permissions and
    14   * limitations under the License.
    15   */
    16
    17  package org.apache.commons.configuration;
    18
    19  import java.sql.Connection;
    20  import java.sql.PreparedStatement;
    21  import java.sql.ResultSet;
    22  import java.sql.SQLException;
    23  import java.sql.Statement;
    24  import java.util.ArrayList;
    25  import java.util.Collection;
    26  import java.util.Iterator;
    27  import java.util.List;
    28
    29  import javax.sql.DataSource;
    30
    31  import org.apache.commons.logging.Log;
    32  import org.apache.commons.logging.LogFactory;
    33
    34  /***
    35   * Configuration stored in a database.
    36   *
    37   * @since 1.0
    38   *
    39   * @author Emmanuel Bourg
    40   * @version $Revision: 1.10 $, $Date: 2004/09/20 09:37:07 $
    41   */
    42  public class DatabaseConfiguration extends AbstractConfiguration
    43  {
    44      /*** Logger */
    45      private static Log log = LogFactory.getLog(DatabaseConfiguration.class);
    46
    47      /*** The datasource to connect to the database. */
    48      private DataSource datasource;
    49
    50      /*** The name of the table containing the configurations. */
    51      private String table;
    52
    53      /*** The column containing the name of the configuration. */
    54      private String nameColumn;
    55
    56      /*** The column containing the keys. */
    57      private String keyColumn;
    58
    59      /*** The column containing the values. */
    60      private String valueColumn;
    61
    62      /*** The name of the configuration. */
    63      private String name;
    64
    65      /***
    66       * Build a configuration from a table containing multiple configurations.
    67       *
    68       * @param datasource    the datasource to connect to the database
    69       * @param table         the name of the table containing the configurations
    70       * @param nameColumn    the column containing the name of the configuration
    71       * @param keyColumn     the column containing the keys of the configuration
    72       * @param valueColumn   the column containing the values of the configuration
    73       * @param name          the name of the configuration
    74       */
    75      public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
    76              String keyColumn, String valueColumn, String name)
    77      {
    78          this.datasource = datasource;
    79          this.table = table;
    80          this.nameColumn = nameColumn;
    81          this.keyColumn = keyColumn;
    82          this.valueColumn = valueColumn;
    83          this.name = name;
    84      }
    85
    86      /***
    87       * Build a configuration from a table.-
    88       *
    89       * @param datasource    the datasource to connect to the database
    90       * @param table         the name of the table containing the configurations
    91       * @param keyColumn     the column containing the keys of the configuration
    92       * @param valueColumn   the column containing the values of the configuration
    93       */
    94      public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
    95      {
    96          this(datasource, table, null, keyColumn, valueColumn, null);
    97      }
    98
    99      /***
    100      * {@inheritDoc}
    101      */
    102     protected Object getPropertyDirect(String key)
    103     {
    104         Object result = null;
    105
    106         // build the query
    107         StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
    108         if (nameColumn != null)
    109         {
    110             query.append(" AND " + nameColumn + "=?");
    111         }
    112
    113         Connection conn = null;
    114         PreparedStatement pstmt = null;
    115
    116         try
    117         {
    118             conn = datasource.getConnection();
    119
    120             // bind the parameters
    121             pstmt = conn.prepareStatement(query.toString());
    122             pstmt.setString(1, key);
    123             if (nameColumn != null)
    124             {
    125                 pstmt.setString(2, name);
    126             }
    127
    128             ResultSet rs = pstmt.executeQuery();
    129
    130             if (rs.next())
    131             {
    132                 result = rs.getObject(valueColumn);
    133             }
    134
    135             // build a list if there is more than one row in the resultset
    136             if (rs.next())
    137             {
    138                 List results = new ArrayList();
    139                 results.add(result);
    140                 results.add(rs.getObject(valueColumn));
    141                 while (rs.next())
    142                 {
    143                     results.add(rs.getObject(valueColumn));
    144                 }
    145                 result = results;
    146             }
    147         }
    148         catch (SQLException e)
    149         {
    150             log.error(e.getMessage(), e);
    151         }
    152         finally
    153         {
    154             closeQuietly(conn, pstmt);
    155         }
    156
    157         return result;
    158     }
    159
    160     /***
    161      * {@inheritDoc}
    162      */
    163     protected void addPropertyDirect(String key, Object obj)
    164     {
    165         // build the query
    166         StringBuffer query = new StringBuffer("INSERT INTO " + table);
    167         if (nameColumn != null)
    168         {
    169             query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
    170         }
    171         else
    172         {
    173             query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
    174         }
    175
    176         Connection conn = null;
    177         PreparedStatement pstmt = null;
    178
    179         try
    180         {
    181             conn = datasource.getConnection();
    182
    183             // bind the parameters
    184             pstmt = conn.prepareStatement(query.toString());
    185             int index = 1;
    186             if (nameColumn != null)
    187             {
    188                 pstmt.setString(index++, name);
    189             }
    190             pstmt.setString(index++, key);
    191             pstmt.setString(index++, String.valueOf(obj));
    192
    193             pstmt.executeUpdate();
    194         }
    195         catch (SQLException e)
    196         {
    197             log.error(e.getMessage(), e);
    198         }
    199         finally
    200         {
    201             // clean up
    202             closeQuietly(conn, pstmt);
    203         }
    204     }
    205
    206     /***
    207      * {@inheritDoc}
    208      */
    209     public boolean isEmpty()
    210     {
    211         boolean empty = false;
    212
    213         // build the query
    214         StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table);
    215         if (nameColumn != null)
    216         {
    217             query.append(" WHERE " + nameColumn + "=?");
    218         }
    219
    220         Connection conn = null;
    221         PreparedStatement pstmt = null;
    222
    223         try
    224         {
    225             conn = datasource.getConnection();
    226
    227             // bind the parameters
    228             pstmt = conn.prepareStatement(query.toString());
    229             if (nameColumn != null)
    230             {
    231                 pstmt.setString(1, name);
    232             }
    233
    234             ResultSet rs = pstmt.executeQuery();
    235
    236             if (rs.next())
    237             {
    238                 empty = rs.getInt(1) == 0;
    239             }
    240         }
    241         catch (SQLException e)
    242         {
    243             log.error(e.getMessage(), e);
    244         }
    245         finally
    246         {
    247             // clean up
    248             closeQuietly(conn, pstmt);
    249         }
    250
    251         return empty;
    252     }
    253
    254     /***
    255      * {@inheritDoc}
    256      */
    257     public boolean containsKey(String key)
    258     {
    259         boolean found = false;
    260
    261         // build the query
    262         StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
    263         if (nameColumn != null)
    264         {
    265             query.append(" AND " + nameColumn + "=?");
    266         }
    267
    268         Connection conn = null;
    269         PreparedStatement pstmt = null;
    270
    271         try
    272         {
    273             conn = datasource.getConnection();
    274
    275             // bind the parameters
    276             pstmt = conn.prepareStatement(query.toString());
    277             pstmt.setString(1, key);
    278             if (nameColumn != null)
    279             {
    280                 pstmt.setString(2, name);
    281             }
    282
    283             ResultSet rs = pstmt.executeQuery();
    284
    285             found = rs.next();
    286         }
    287         catch (SQLException e)
    288         {
    289             log.error(e.getMessage(), e);
    290         }
    291         finally
    292         {
    293             // clean up
    294             closeQuietly(conn, pstmt);
    295         }
    296
    297         return found;
    298     }
    299
    300     /***
    301      * {@inheritDoc}
    302      */
    303     public void clearProperty(String key)
    304     {
    305         // build the query
    306         StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
    307         if (nameColumn != null)
    308         {
    309             query.append(" AND " + nameColumn + "=?");
    310         }
    311
    312         Connection conn = null;
    313         PreparedStatement pstmt = null;
    314
    315         try
    316         {
    317             conn = datasource.getConnection();
    318
    319             // bind the parameters
    320             pstmt = conn.prepareStatement(query.toString());
    321             pstmt.setString(1, key);
    322             if (nameColumn != null)
    323             {
    324                 pstmt.setString(2, name);
    325             }
    326
    327             pstmt.executeUpdate();
    328         }
    329         catch (SQLException e)
    330         {
    331             log.error(e.getMessage(), e);
    332         }
    333         finally
    334         {
    335             // clean up
    336             closeQuietly(conn, pstmt);
    337         }
    338     }
    339
    340     /***
    341      * {@inheritDoc}
    342      */
    343     public Iterator getKeys()
    344     {
    345         Collection keys = new ArrayList();
    346
    347         // build the query
    348         StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
    349         if (nameColumn != null)
    350         {
    351             query.append(" WHERE " + nameColumn + "=?");
    352         }
    353
    354         Connection conn = null;
    355         PreparedStatement pstmt = null;
    356
    357         try
    358         {
    359             conn = datasource.getConnection();
    360
    361             // bind the parameters
    362             pstmt = conn.prepareStatement(query.toString());
    363             if (nameColumn != null)
    364             {
    365                 pstmt.setString(1, name);
    366             }
    367
    368             ResultSet rs = pstmt.executeQuery();
    369
    370             while (rs.next())
    371             {
    372                 keys.add(rs.getString(1));
    373             }
    374         }
    375         catch (SQLException e)
    376         {
    377             log.error(e.getMessage(), e);
    378         }
    379         finally
    380         {
    381             // clean up
    382             closeQuietly(conn, pstmt);
    383         }
    384
    385         return keys.iterator();
    386     }
    387
    388     /***
    389      * Close a <code>Connection</code> and, <code>Statement</code>.
    390      * Avoid closing if null and hide any SQLExceptions that occur.
    391      *
    392      * @param conn The database connection to close
    393      * @param stmt The statement to close
    394      */
    395     private void closeQuietly(Connection conn, Statement stmt)
    396     {
    397         try
    398         {
    399             if (stmt != null)
    400             {
    401                 stmt.close();
    402             }
    403             if (conn != null)
    404             {
    405                 conn.close();
    406             }
    407         }
    408         catch (SQLException e)
    409         {
    410             log.error(e.getMessage(), e);
    411         }
    412     }
    413 }