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 }