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$, $Date: 2005-02-26 13:56:39 +0100 (Sat, 26 Feb 2005) $
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     public Object getProperty(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 = true;
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 void clear()
344     {
345         // build the query
346         StringBuffer query = new StringBuffer("DELETE FROM " + table);
347         if (nameColumn != null)
348         {
349             query.append(" WHERE " + nameColumn + "=?");
350         }
351 
352         Connection conn = null;
353         PreparedStatement pstmt = null;
354 
355         try
356         {
357             conn = datasource.getConnection();
358 
359             // bind the parameters
360             pstmt = conn.prepareStatement(query.toString());
361             if (nameColumn != null)
362             {
363                 pstmt.setString(1, name);
364             }
365 
366             pstmt.executeUpdate();
367         }
368         catch (SQLException e)
369         {
370             log.error(e.getMessage(), e);
371         }
372         finally
373         {
374             // clean up
375             closeQuietly(conn, pstmt);
376         }
377     }
378 
379     /***
380      * {@inheritDoc}
381      */
382     public Iterator getKeys()
383     {
384         Collection keys = new ArrayList();
385 
386         // build the query
387         StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
388         if (nameColumn != null)
389         {
390             query.append(" WHERE " + nameColumn + "=?");
391         }
392 
393         Connection conn = null;
394         PreparedStatement pstmt = null;
395 
396         try
397         {
398             conn = datasource.getConnection();
399 
400             // bind the parameters
401             pstmt = conn.prepareStatement(query.toString());
402             if (nameColumn != null)
403             {
404                 pstmt.setString(1, name);
405             }
406 
407             ResultSet rs = pstmt.executeQuery();
408 
409             while (rs.next())
410             {
411                 keys.add(rs.getString(1));
412             }
413         }
414         catch (SQLException e)
415         {
416             log.error(e.getMessage(), e);
417         }
418         finally
419         {
420             // clean up
421             closeQuietly(conn, pstmt);
422         }
423 
424         return keys.iterator();
425     }
426 
427     /***
428      * Close a <code>Connection</code> and, <code>Statement</code>.
429      * Avoid closing if null and hide any SQLExceptions that occur.
430      *
431      * @param conn The database connection to close
432      * @param stmt The statement to close
433      */
434     private void closeQuietly(Connection conn, Statement stmt)
435     {
436         try
437         {
438             if (stmt != null)
439             {
440                 stmt.close();
441             }
442             if (conn != null)
443             {
444                 conn.close();
445             }
446         }
447         catch (SQLException e)
448         {
449             log.error(e.getMessage(), e);
450         }
451     }
452 }