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.LogFactory;
33  
34  /**
35   * Configuration stored in a database. The properties are retrieved from a
36   * table containing at least one column for the keys, and one column for the
37   * values. It's possible to store several configurations in the same table by
38   * adding a column containing the name of the configuration. The name of the
39   * table and the columns is specified in the constructor.
40   *
41   * <h4>Example 1 - One configuration per table</h4>
42   *
43   * <pre>
44   * CREATE TABLE myconfig (
45   *     `key`   VARCHAR NOT NULL PRIMARY KEY,
46   *     `value` VARCHAR
47   * );
48   *
49   * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
50   *
51   *
52   * Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
53   * String value = config.getString("foo");
54   * </pre>
55   *
56   * <h4>Example 2 - Multiple configurations per table</h4>
57   *
58   * <pre>
59   * CREATE TABLE myconfigs (
60   *     `name`  VARCHAR NOT NULL,
61   *     `key`   VARCHAR NOT NULL,
62   *     `value` VARCHAR,
63   *     CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
64   * );
65   *
66   * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
67   * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
68   *
69   *
70   * Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
71   * String value1 = conf.getString("key1");
72   *
73   * Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
74   * String value2 = conf.getString("key2");
75   * </pre>
76   * The configuration can be instructed to perform commits after database updates.
77   * This is achieved by setting the {@code commits} parameter of the
78   * constructors to <b>true</b>. If commits should not be performed (which is the
79   * default behavior), it should be ensured that the connections returned by the
80   * {@code DataSource} are in auto-commit mode.
81   *
82   * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
83   * @since 1.0
84   *
85   * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
86   * @version $Id: DatabaseConfiguration.java 1344442 2012-05-30 20:17:35Z oheger $
87   */
88  public class DatabaseConfiguration extends AbstractConfiguration
89  {
90      /** The datasource to connect to the database. */
91      private final DataSource datasource;
92  
93      /** The name of the table containing the configurations. */
94      private final String table;
95  
96      /** The column containing the name of the configuration. */
97      private final String nameColumn;
98  
99      /** The column containing the keys. */
100     private final String keyColumn;
101 
102     /** The column containing the values. */
103     private final String valueColumn;
104 
105     /** The name of the configuration. */
106     private final String name;
107 
108     /** A flag whether commits should be performed by this configuration. */
109     private final boolean doCommits;
110 
111     /**
112      * Build a configuration from a table containing multiple configurations.
113      * No commits are performed by the new configuration instance.
114      *
115      * @param datasource    the datasource to connect to the database
116      * @param table         the name of the table containing the configurations
117      * @param nameColumn    the column containing the name of the configuration
118      * @param keyColumn     the column containing the keys of the configuration
119      * @param valueColumn   the column containing the values of the configuration
120      * @param name          the name of the configuration
121      */
122     public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
123             String keyColumn, String valueColumn, String name)
124     {
125         this(datasource, table, nameColumn, keyColumn, valueColumn, name, false);
126     }
127 
128     /**
129      * Creates a new instance of {@code DatabaseConfiguration} that operates on
130      * a database table containing multiple configurations.
131      *
132      * @param datasource the {@code DataSource} to connect to the database
133      * @param table the name of the table containing the configurations
134      * @param nameColumn the column containing the name of the configuration
135      * @param keyColumn the column containing the keys of the configuration
136      * @param valueColumn the column containing the values of the configuration
137      * @param name the name of the configuration
138      * @param commits a flag whether the configuration should perform a commit
139      *        after a database update
140      */
141     public DatabaseConfiguration(DataSource datasource, String table,
142             String nameColumn, String keyColumn, String valueColumn,
143             String name, boolean commits)
144     {
145         this.datasource = datasource;
146         this.table = table;
147         this.nameColumn = nameColumn;
148         this.keyColumn = keyColumn;
149         this.valueColumn = valueColumn;
150         this.name = name;
151         doCommits = commits;
152         setLogger(LogFactory.getLog(getClass()));
153         addErrorLogListener();  // log errors per default
154     }
155 
156     /**
157      * Build a configuration from a table.
158      *
159      * @param datasource    the datasource to connect to the database
160      * @param table         the name of the table containing the configurations
161      * @param keyColumn     the column containing the keys of the configuration
162      * @param valueColumn   the column containing the values of the configuration
163      */
164     public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
165     {
166         this(datasource, table, null, keyColumn, valueColumn, null);
167     }
168 
169     /**
170      * Creates a new instance of {@code DatabaseConfiguration} that
171      * operates on a database table containing a single configuration only.
172      *
173      * @param datasource the {@code DataSource} to connect to the database
174      * @param table the name of the table containing the configurations
175      * @param keyColumn the column containing the keys of the configuration
176      * @param valueColumn the column containing the values of the configuration
177      * @param commits a flag whether the configuration should perform a commit
178      *        after a database update
179      */
180     public DatabaseConfiguration(DataSource datasource, String table,
181             String keyColumn, String valueColumn, boolean commits)
182     {
183         this(datasource, table, null, keyColumn, valueColumn, null, commits);
184     }
185 
186     /**
187      * Returns a flag whether this configuration performs commits after database
188      * updates.
189      *
190      * @return a flag whether commits are performed
191      */
192     public boolean isDoCommits()
193     {
194         return doCommits;
195     }
196 
197     /**
198      * Returns the value of the specified property. If this causes a database
199      * error, an error event will be generated of type
200      * {@code EVENT_READ_PROPERTY} with the causing exception. The
201      * event's {@code propertyName} is set to the passed in property key,
202      * the {@code propertyValue} is undefined.
203      *
204      * @param key the key of the desired property
205      * @return the value of this property
206      */
207     public Object getProperty(String key)
208     {
209         Object result = null;
210 
211         // build the query
212         StringBuilder query = new StringBuilder("SELECT * FROM ");
213         query.append(table).append(" WHERE ");
214         query.append(keyColumn).append("=?");
215         if (nameColumn != null)
216         {
217             query.append(" AND " + nameColumn + "=?");
218         }
219 
220         Connection conn = null;
221         PreparedStatement pstmt = null;
222         ResultSet rs = null;
223 
224         try
225         {
226             conn = getConnection();
227 
228             // bind the parameters
229             pstmt = conn.prepareStatement(query.toString());
230             pstmt.setString(1, key);
231             if (nameColumn != null)
232             {
233                 pstmt.setString(2, name);
234             }
235 
236             rs = pstmt.executeQuery();
237 
238             List<Object> results = new ArrayList<Object>();
239             while (rs.next())
240             {
241                 Object value = rs.getObject(valueColumn);
242                 if (isDelimiterParsingDisabled())
243                 {
244                     results.add(value);
245                 }
246                 else
247                 {
248                     // Split value if it contains the list delimiter
249                     Iterator<?> it = PropertyConverter.toIterator(value, getListDelimiter());
250                     while (it.hasNext())
251                     {
252                         results.add(it.next());
253                     }
254                 }
255             }
256 
257             if (!results.isEmpty())
258             {
259                 result = (results.size() > 1) ? results : results.get(0);
260             }
261         }
262         catch (SQLException e)
263         {
264             fireError(EVENT_READ_PROPERTY, key, null, e);
265         }
266         finally
267         {
268             close(conn, pstmt, rs);
269         }
270 
271         return result;
272     }
273 
274     /**
275      * Adds a property to this configuration. If this causes a database error,
276      * an error event will be generated of type {@code EVENT_ADD_PROPERTY}
277      * with the causing exception. The event's {@code propertyName} is
278      * set to the passed in property key, the {@code propertyValue}
279      * points to the passed in value.
280      *
281      * @param key the property key
282      * @param obj the value of the property to add
283      */
284     @Override
285     protected void addPropertyDirect(String key, Object obj)
286     {
287         // build the query
288         StringBuilder query = new StringBuilder("INSERT INTO " + table);
289         if (nameColumn != null)
290         {
291             query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
292         }
293         else
294         {
295             query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
296         }
297 
298         Connection conn = null;
299         PreparedStatement pstmt = null;
300 
301         try
302         {
303             conn = getConnection();
304 
305             // bind the parameters
306             pstmt = conn.prepareStatement(query.toString());
307             int index = 1;
308             if (nameColumn != null)
309             {
310                 pstmt.setString(index++, name);
311             }
312             pstmt.setString(index++, key);
313             pstmt.setString(index++, String.valueOf(obj));
314 
315             pstmt.executeUpdate();
316             commitIfRequired(conn);
317         }
318         catch (SQLException e)
319         {
320             fireError(EVENT_ADD_PROPERTY, key, obj, e);
321         }
322         finally
323         {
324             // clean up
325             close(conn, pstmt, null);
326         }
327     }
328 
329     /**
330      * Adds a property to this configuration. This implementation will
331      * temporarily disable list delimiter parsing, so that even if the value
332      * contains the list delimiter, only a single record will be written into
333      * the managed table. The implementation of {@code getProperty()}
334      * will take care about delimiters. So list delimiters are fully supported
335      * by {@code DatabaseConfiguration}, but internally treated a bit
336      * differently.
337      *
338      * @param key the key of the new property
339      * @param value the value to be added
340      */
341     @Override
342     public void addProperty(String key, Object value)
343     {
344         boolean parsingFlag = isDelimiterParsingDisabled();
345         try
346         {
347             if (value instanceof String)
348             {
349                 // temporarily disable delimiter parsing
350                 setDelimiterParsingDisabled(true);
351             }
352             super.addProperty(key, value);
353         }
354         finally
355         {
356             setDelimiterParsingDisabled(parsingFlag);
357         }
358     }
359 
360     /**
361      * Checks if this configuration is empty. If this causes a database error,
362      * an error event will be generated of type {@code EVENT_READ_PROPERTY}
363      * with the causing exception. Both the event's {@code propertyName}
364      * and {@code propertyValue} will be undefined.
365      *
366      * @return a flag whether this configuration is empty.
367      */
368     public boolean isEmpty()
369     {
370         boolean empty = true;
371 
372         // build the query
373         StringBuilder query = new StringBuilder("SELECT count(*) FROM " + table);
374         if (nameColumn != null)
375         {
376             query.append(" WHERE " + nameColumn + "=?");
377         }
378 
379         Connection conn = null;
380         PreparedStatement pstmt = null;
381         ResultSet rs = null;
382 
383         try
384         {
385             conn = getConnection();
386 
387             // bind the parameters
388             pstmt = conn.prepareStatement(query.toString());
389             if (nameColumn != null)
390             {
391                 pstmt.setString(1, name);
392             }
393 
394             rs = pstmt.executeQuery();
395 
396             if (rs.next())
397             {
398                 empty = rs.getInt(1) == 0;
399             }
400         }
401         catch (SQLException e)
402         {
403             fireError(EVENT_READ_PROPERTY, null, null, e);
404         }
405         finally
406         {
407             // clean up
408             close(conn, pstmt, rs);
409         }
410 
411         return empty;
412     }
413 
414     /**
415      * Checks whether this configuration contains the specified key. If this
416      * causes a database error, an error event will be generated of type
417      * {@code EVENT_READ_PROPERTY} with the causing exception. The
418      * event's {@code propertyName} will be set to the passed in key, the
419      * {@code propertyValue} will be undefined.
420      *
421      * @param key the key to be checked
422      * @return a flag whether this key is defined
423      */
424     public boolean containsKey(String key)
425     {
426         boolean found = false;
427 
428         // build the query
429         StringBuilder query = new StringBuilder("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
430         if (nameColumn != null)
431         {
432             query.append(" AND " + nameColumn + "=?");
433         }
434 
435         Connection conn = null;
436         PreparedStatement pstmt = null;
437         ResultSet rs = null;
438 
439         try
440         {
441             conn = getConnection();
442 
443             // bind the parameters
444             pstmt = conn.prepareStatement(query.toString());
445             pstmt.setString(1, key);
446             if (nameColumn != null)
447             {
448                 pstmt.setString(2, name);
449             }
450 
451             rs = pstmt.executeQuery();
452 
453             found = rs.next();
454         }
455         catch (SQLException e)
456         {
457             fireError(EVENT_READ_PROPERTY, key, null, e);
458         }
459         finally
460         {
461             // clean up
462             close(conn, pstmt, rs);
463         }
464 
465         return found;
466     }
467 
468     /**
469      * Removes the specified value from this configuration. If this causes a
470      * database error, an error event will be generated of type
471      * {@code EVENT_CLEAR_PROPERTY} with the causing exception. The
472      * event's {@code propertyName} will be set to the passed in key, the
473      * {@code propertyValue} will be undefined.
474      *
475      * @param key the key of the property to be removed
476      */
477     @Override
478     protected void clearPropertyDirect(String key)
479     {
480         // build the query
481         StringBuilder query = new StringBuilder("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
482         if (nameColumn != null)
483         {
484             query.append(" AND " + nameColumn + "=?");
485         }
486 
487         Connection conn = null;
488         PreparedStatement pstmt = null;
489 
490         try
491         {
492             conn = getConnection();
493 
494             // bind the parameters
495             pstmt = conn.prepareStatement(query.toString());
496             pstmt.setString(1, key);
497             if (nameColumn != null)
498             {
499                 pstmt.setString(2, name);
500             }
501 
502             pstmt.executeUpdate();
503             commitIfRequired(conn);
504         }
505         catch (SQLException e)
506         {
507             fireError(EVENT_CLEAR_PROPERTY, key, null, e);
508         }
509         finally
510         {
511             // clean up
512             close(conn, pstmt, null);
513         }
514     }
515 
516     /**
517      * Removes all entries from this configuration. If this causes a database
518      * error, an error event will be generated of type
519      * {@code EVENT_CLEAR} with the causing exception. Both the
520      * event's {@code propertyName} and the {@code propertyValue}
521      * will be undefined.
522      */
523     @Override
524     public void clear()
525     {
526         fireEvent(EVENT_CLEAR, null, null, true);
527         // build the query
528         StringBuilder query = new StringBuilder("DELETE FROM " + table);
529         if (nameColumn != null)
530         {
531             query.append(" WHERE " + nameColumn + "=?");
532         }
533 
534         Connection conn = null;
535         PreparedStatement pstmt = null;
536 
537         try
538         {
539             conn = getConnection();
540 
541             // bind the parameters
542             pstmt = conn.prepareStatement(query.toString());
543             if (nameColumn != null)
544             {
545                 pstmt.setString(1, name);
546             }
547 
548             pstmt.executeUpdate();
549             commitIfRequired(conn);
550         }
551         catch (SQLException e)
552         {
553             fireError(EVENT_CLEAR, null, null, e);
554         }
555         finally
556         {
557             // clean up
558             close(conn, pstmt, null);
559         }
560         fireEvent(EVENT_CLEAR, null, null, false);
561     }
562 
563     /**
564      * Returns an iterator with the names of all properties contained in this
565      * configuration. If this causes a database
566      * error, an error event will be generated of type
567      * {@code EVENT_READ_PROPERTY} with the causing exception. Both the
568      * event's {@code propertyName} and the {@code propertyValue}
569      * will be undefined.
570      * @return an iterator with the contained keys (an empty iterator in case
571      * of an error)
572      */
573     public Iterator<String> getKeys()
574     {
575         Collection<String> keys = new ArrayList<String>();
576 
577         // build the query
578         StringBuilder query = new StringBuilder("SELECT DISTINCT " + keyColumn + " FROM " + table);
579         if (nameColumn != null)
580         {
581             query.append(" WHERE " + nameColumn + "=?");
582         }
583 
584         Connection conn = null;
585         PreparedStatement pstmt = null;
586         ResultSet rs = null;
587 
588         try
589         {
590             conn = getConnection();
591 
592             // bind the parameters
593             pstmt = conn.prepareStatement(query.toString());
594             if (nameColumn != null)
595             {
596                 pstmt.setString(1, name);
597             }
598 
599             rs = pstmt.executeQuery();
600 
601             while (rs.next())
602             {
603                 keys.add(rs.getString(1));
604             }
605         }
606         catch (SQLException e)
607         {
608             fireError(EVENT_READ_PROPERTY, null, null, e);
609         }
610         finally
611         {
612             // clean up
613             close(conn, pstmt, rs);
614         }
615 
616         return keys.iterator();
617     }
618 
619     /**
620      * Returns the used {@code DataSource} object.
621      *
622      * @return the data source
623      * @since 1.4
624      */
625     public DataSource getDatasource()
626     {
627         return datasource;
628     }
629 
630     /**
631      * Returns a {@code Connection} object. This method is called when
632      * ever the database is to be accessed. This implementation returns a
633      * connection from the current {@code DataSource}.
634      *
635      * @return the {@code Connection} object to be used
636      * @throws SQLException if an error occurs
637      * @since 1.4
638      * @deprecated Use a custom data source to change the connection used by the
639      * class. To be removed in Commons Configuration 2.0
640      */
641     @Deprecated
642     protected Connection getConnection() throws SQLException
643     {
644         return getDatasource().getConnection();
645     }
646 
647     /**
648      * Close the specified database objects.
649      * Avoid closing if null and hide any SQLExceptions that occur.
650      *
651      * @param conn The database connection to close
652      * @param stmt The statement to close
653      * @param rs the result set to close
654      */
655     private void close(Connection conn, Statement stmt, ResultSet rs)
656     {
657         try
658         {
659             if (rs != null)
660             {
661                 rs.close();
662             }
663         }
664         catch (SQLException e)
665         {
666             getLogger().error("An error occurred on closing the result set", e);
667         }
668 
669         try
670         {
671             if (stmt != null)
672             {
673                 stmt.close();
674             }
675         }
676         catch (SQLException e)
677         {
678             getLogger().error("An error occured on closing the statement", e);
679         }
680 
681         try
682         {
683             if (conn != null)
684             {
685                 conn.close();
686             }
687         }
688         catch (SQLException e)
689         {
690             getLogger().error("An error occured on closing the connection", e);
691         }
692     }
693 
694     /**
695      * Performs a commit if needed. This method is called after updates of the
696      * managed database table. If the configuration should perform commits, it
697      * does so now.
698      *
699      * @param conn the active connection
700      * @throws SQLException if an error occurs
701      */
702     private void commitIfRequired(Connection conn) throws SQLException
703     {
704         if (isDoCommits())
705         {
706             conn.commit();
707         }
708     }
709 }