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.logging.Log;
    33  import org.apache.commons.logging.LogFactory;
    34
    35  /***
    36   * Configuration stored in a database.
    37   *
    38   * @since 1.0
    39   *
    40   * @author Emmanuel Bourg
    41   * @version $Revision: 439648 $, $Date: 2006-09-02 22:42:10 +0200 (Sa, 02 Sep 2006) $
    42   */
    43  public class DatabaseConfiguration extends AbstractConfiguration
    44  {
    45      /*** Logger */
    46      private static Log log = LogFactory.getLog(DatabaseConfiguration.class);
    47
    48      /*** The datasource to connect to the database. */
    49      private DataSource datasource;
    50
    51      /*** The name of the table containing the configurations. */
    52      private String table;
    53
    54      /*** The column containing the name of the configuration. */
    55      private String nameColumn;
    56
    57      /*** The column containing the keys. */
    58      private String keyColumn;
    59
    60      /*** The column containing the values. */
    61      private String valueColumn;
    62
    63      /*** The name of the configuration. */
    64      private String name;
    65
    66      /***
    67       * Build a configuration from a table containing multiple configurations.
    68       *
    69       * @param datasource    the datasource to connect to the database
    70       * @param table         the name of the table containing the configurations
    71       * @param nameColumn    the column containing the name of the configuration
    72       * @param keyColumn     the column containing the keys of the configuration
    73       * @param valueColumn   the column containing the values of the configuration
    74       * @param name          the name of the configuration
    75       */
    76      public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
    77              String keyColumn, String valueColumn, String name)
    78      {
    79          this.datasource = datasource;
    80          this.table = table;
    81          this.nameColumn = nameColumn;
    82          this.keyColumn = keyColumn;
    83          this.valueColumn = valueColumn;
    84          this.name = name;
    85      }
    86
    87      /***
    88       * Build a configuration from a table.-
    89       *
    90       * @param datasource    the datasource to connect to the database
    91       * @param table         the name of the table containing the configurations
    92       * @param keyColumn     the column containing the keys of the configuration
    93       * @param valueColumn   the column containing the values of the configuration
    94       */
    95      public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
    96      {
    97          this(datasource, table, null, keyColumn, valueColumn, null);
    98      }
    99
    100     /***
    101      * {@inheritDoc}
    102      */
    103     public Object getProperty(String key)
    104     {
    105         Object result = null;
    106
    107         // build the query
    108         StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
    109         if (nameColumn != null)
    110         {
    111             query.append(" AND " + nameColumn + "=?");
    112         }
    113
    114         Connection conn = null;
    115         PreparedStatement pstmt = null;
    116
    117         try
    118         {
    119             conn = datasource.getConnection();
    120
    121             // bind the parameters
    122             pstmt = conn.prepareStatement(query.toString());
    123             pstmt.setString(1, key);
    124             if (nameColumn != null)
    125             {
    126                 pstmt.setString(2, name);
    127             }
    128
    129             ResultSet rs = pstmt.executeQuery();
    130
    131             if (rs.next())
    132             {
    133                 result = rs.getObject(valueColumn);
    134             }
    135
    136             // build a list if there is more than one row in the resultset
    137             if (rs.next())
    138             {
    139                 List results = new ArrayList();
    140                 results.add(result);
    141                 results.add(rs.getObject(valueColumn));
    142                 while (rs.next())
    143                 {
    144                     results.add(rs.getObject(valueColumn));
    145                 }
    146                 result = results;
    147             }
    148         }
    149         catch (SQLException e)
    150         {
    151             log.error(e.getMessage(), e);
    152         }
    153         finally
    154         {
    155             closeQuietly(conn, pstmt);
    156         }
    157
    158         return result;
    159     }
    160
    161     /***
    162      * {@inheritDoc}
    163      */
    164     protected void addPropertyDirect(String key, Object obj)
    165     {
    166         // build the query
    167         StringBuffer query = new StringBuffer("INSERT INTO " + table);
    168         if (nameColumn != null)
    169         {
    170             query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
    171         }
    172         else
    173         {
    174             query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
    175         }
    176
    177         Connection conn = null;
    178         PreparedStatement pstmt = null;
    179
    180         try
    181         {
    182             conn = datasource.getConnection();
    183
    184             // bind the parameters
    185             pstmt = conn.prepareStatement(query.toString());
    186             int index = 1;
    187             if (nameColumn != null)
    188             {
    189                 pstmt.setString(index++, name);
    190             }
    191             pstmt.setString(index++, key);
    192             pstmt.setString(index++, String.valueOf(obj));
    193
    194             pstmt.executeUpdate();
    195         }
    196         catch (SQLException e)
    197         {
    198             log.error(e.getMessage(), e);
    199         }
    200         finally
    201         {
    202             // clean up
    203             closeQuietly(conn, pstmt);
    204         }
    205     }
    206
    207     /***
    208      * {@inheritDoc}
    209      */
    210     public boolean isEmpty()
    211     {
    212         boolean empty = true;
    213
    214         // build the query
    215         StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table);
    216         if (nameColumn != null)
    217         {
    218             query.append(" WHERE " + nameColumn + "=?");
    219         }
    220
    221         Connection conn = null;
    222         PreparedStatement pstmt = null;
    223
    224         try
    225         {
    226             conn = datasource.getConnection();
    227
    228             // bind the parameters
    229             pstmt = conn.prepareStatement(query.toString());
    230             if (nameColumn != null)
    231             {
    232                 pstmt.setString(1, name);
    233             }
    234
    235             ResultSet rs = pstmt.executeQuery();
    236
    237             if (rs.next())
    238             {
    239                 empty = rs.getInt(1) == 0;
    240             }
    241         }
    242         catch (SQLException e)
    243         {
    244             log.error(e.getMessage(), e);
    245         }
    246         finally
    247         {
    248             // clean up
    249             closeQuietly(conn, pstmt);
    250         }
    251
    252         return empty;
    253     }
    254
    255     /***
    256      * {@inheritDoc}
    257      */
    258     public boolean containsKey(String key)
    259     {
    260         boolean found = false;
    261
    262         // build the query
    263         StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
    264         if (nameColumn != null)
    265         {
    266             query.append(" AND " + nameColumn + "=?");
    267         }
    268
    269         Connection conn = null;
    270         PreparedStatement pstmt = null;
    271
    272         try
    273         {
    274             conn = datasource.getConnection();
    275
    276             // bind the parameters
    277             pstmt = conn.prepareStatement(query.toString());
    278             pstmt.setString(1, key);
    279             if (nameColumn != null)
    280             {
    281                 pstmt.setString(2, name);
    282             }
    283
    284             ResultSet rs = pstmt.executeQuery();
    285
    286             found = rs.next();
    287         }
    288         catch (SQLException e)
    289         {
    290             log.error(e.getMessage(), e);
    291         }
    292         finally
    293         {
    294             // clean up
    295             closeQuietly(conn, pstmt);
    296         }
    297
    298         return found;
    299     }
    300
    301     /***
    302      * {@inheritDoc}
    303      */
    304     public void clearProperty(String key)
    305     {
    306         // build the query
    307         StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
    308         if (nameColumn != null)
    309         {
    310             query.append(" AND " + nameColumn + "=?");
    311         }
    312
    313         Connection conn = null;
    314         PreparedStatement pstmt = null;
    315
    316         try
    317         {
    318             conn = datasource.getConnection();
    319
    320             // bind the parameters
    321             pstmt = conn.prepareStatement(query.toString());
    322             pstmt.setString(1, key);
    323             if (nameColumn != null)
    324             {
    325                 pstmt.setString(2, name);
    326             }
    327
    328             pstmt.executeUpdate();
    329         }
    330         catch (SQLException e)
    331         {
    332             log.error(e.getMessage(), e);
    333         }
    334         finally
    335         {
    336             // clean up
    337             closeQuietly(conn, pstmt);
    338         }
    339     }
    340
    341     /***
    342      * {@inheritDoc}
    343      */
    344     public void clear()
    345     {
    346         // build the query
    347         StringBuffer query = new StringBuffer("DELETE FROM " + table);
    348         if (nameColumn != null)
    349         {
    350             query.append(" WHERE " + nameColumn + "=?");
    351         }
    352
    353         Connection conn = null;
    354         PreparedStatement pstmt = null;
    355
    356         try
    357         {
    358             conn = datasource.getConnection();
    359
    360             // bind the parameters
    361             pstmt = conn.prepareStatement(query.toString());
    362             if (nameColumn != null)
    363             {
    364                 pstmt.setString(1, name);
    365             }
    366
    367             pstmt.executeUpdate();
    368         }
    369         catch (SQLException e)
    370         {
    371             log.error(e.getMessage(), e);
    372         }
    373         finally
    374         {
    375             // clean up
    376             closeQuietly(conn, pstmt);
    377         }
    378     }
    379
    380     /***
    381      * {@inheritDoc}
    382      */
    383     public Iterator getKeys()
    384     {
    385         Collection keys = new ArrayList();
    386
    387         // build the query
    388         StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
    389         if (nameColumn != null)
    390         {
    391             query.append(" WHERE " + nameColumn + "=?");
    392         }
    393
    394         Connection conn = null;
    395         PreparedStatement pstmt = null;
    396
    397         try
    398         {
    399             conn = datasource.getConnection();
    400
    401             // bind the parameters
    402             pstmt = conn.prepareStatement(query.toString());
    403             if (nameColumn != null)
    404             {
    405                 pstmt.setString(1, name);
    406             }
    407
    408             ResultSet rs = pstmt.executeQuery();
    409
    410             while (rs.next())
    411             {
    412                 keys.add(rs.getString(1));
    413             }
    414         }
    415         catch (SQLException e)
    416         {
    417             log.error(e.getMessage(), e);
    418         }
    419         finally
    420         {
    421             // clean up
    422             closeQuietly(conn, pstmt);
    423         }
    424
    425         return keys.iterator();
    426     }
    427
    428     /***
    429      * Close a <code>Connection</code> and, <code>Statement</code>.
    430      * Avoid closing if null and hide any SQLExceptions that occur.
    431      *
    432      * @param conn The database connection to close
    433      * @param stmt The statement to close
    434      */
    435     private void closeQuietly(Connection conn, Statement stmt)
    436     {
    437         try
    438         {
    439             if (stmt != null)
    440             {
    441                 stmt.close();
    442             }
    443             if (conn != null)
    444             {
    445                 conn.close();
    446             }
    447         }
    448         catch (SQLException e)
    449         {
    450             log.error(e.getMessage(), e);
    451         }
    452     }
    453 }