1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 *
78 * @since 1.0
79 *
80 * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
81 * @version $Revision: 589380 $, $Date: 2007-10-28 17:37:35 +0100 (So, 28 Okt 2007) $
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();
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 }