001 /*
002 * Licensed to the Apache Software Foundation (ASF) under one or more
003 * contributor license agreements. See the NOTICE file distributed with
004 * this work for additional information regarding copyright ownership.
005 * The ASF licenses this file to You under the Apache License, Version 2.0
006 * (the "License"); you may not use this file except in compliance with
007 * the License. You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017
018 package org.apache.commons.configuration;
019
020 import java.sql.Connection;
021 import java.sql.PreparedStatement;
022 import java.sql.ResultSet;
023 import java.sql.SQLException;
024 import java.sql.Statement;
025 import java.util.ArrayList;
026 import java.util.Collection;
027 import java.util.Iterator;
028 import java.util.List;
029
030 import javax.sql.DataSource;
031
032 import org.apache.commons.logging.LogFactory;
033
034 /**
035 * Configuration stored in a database. The properties are retrieved from a
036 * table containing at least one column for the keys, and one column for the
037 * values. It's possible to store several configurations in the same table by
038 * adding a column containing the name of the configuration. The name of the
039 * table and the columns is specified in the constructor.
040 *
041 * <h4>Example 1 - One configuration per table</h4>
042 *
043 * <pre>
044 * CREATE TABLE myconfig (
045 * `key` VARCHAR NOT NULL PRIMARY KEY,
046 * `value` VARCHAR
047 * );
048 *
049 * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
050 *
051 *
052 * Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
053 * String value = config.getString("foo");
054 * </pre>
055 *
056 * <h4>Example 2 - Multiple configurations per table</h4>
057 *
058 * <pre>
059 * CREATE TABLE myconfigs (
060 * `name` VARCHAR NOT NULL,
061 * `key` VARCHAR NOT NULL,
062 * `value` VARCHAR,
063 * CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
064 * );
065 *
066 * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
067 * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
068 *
069 *
070 * Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
071 * String value1 = conf.getString("key1");
072 *
073 * Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
074 * String value2 = conf.getString("key2");
075 * </pre>
076 * The configuration can be instructed to perform commits after database updates.
077 * This is achieved by setting the {@code commits} parameter of the
078 * constructors to <b>true</b>. If commits should not be performed (which is the
079 * default behavior), it should be ensured that the connections returned by the
080 * {@code DataSource} are in auto-commit mode.
081 *
082 * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
083 * @since 1.0
084 *
085 * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
086 * @version $Id: DatabaseConfiguration.java 1344442 2012-05-30 20:17:35Z oheger $
087 */
088 public class DatabaseConfiguration extends AbstractConfiguration
089 {
090 /** The datasource to connect to the database. */
091 private final DataSource datasource;
092
093 /** The name of the table containing the configurations. */
094 private final String table;
095
096 /** The column containing the name of the configuration. */
097 private final String nameColumn;
098
099 /** 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 }