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 }