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.
37 *
38 * @since 1.0
39 *
40 * @author Emmanuel Bourg
41 * @version $Revision: 514234 $, $Date: 2007-03-03 21:18:14 +0100 (Sa, 03 Mrz 2007) $
42 */
43 public class DatabaseConfiguration extends AbstractConfiguration
44 {
45 /*** The datasource to connect to the database. */
46 private DataSource datasource;
47
48 /*** The name of the table containing the configurations. */
49 private String table;
50
51 /*** The column containing the name of the configuration. */
52 private String nameColumn;
53
54 /*** The column containing the keys. */
55 private String keyColumn;
56
57 /*** The column containing the values. */
58 private String valueColumn;
59
60 /*** The name of the configuration. */
61 private String name;
62
63 /***
64 * Build a configuration from a table containing multiple configurations.
65 *
66 * @param datasource the datasource to connect to the database
67 * @param table the name of the table containing the configurations
68 * @param nameColumn the column containing the name of the configuration
69 * @param keyColumn the column containing the keys of the configuration
70 * @param valueColumn the column containing the values of the configuration
71 * @param name the name of the configuration
72 */
73 public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
74 String keyColumn, String valueColumn, String name)
75 {
76 this.datasource = datasource;
77 this.table = table;
78 this.nameColumn = nameColumn;
79 this.keyColumn = keyColumn;
80 this.valueColumn = valueColumn;
81 this.name = name;
82 setLogger(LogFactory.getLog(getClass()));
83 addErrorLogListener();
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 * Returns the value of the specified property. If this causes a database
101 * error, an error event will be generated of type
102 * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
103 * event's <code>propertyName</code> is set to the passed in property key,
104 * the <code>propertyValue</code> is undefined.
105 *
106 * @param key the key of the desired property
107 * @return the value of this property
108 */
109 public Object getProperty(String key)
110 {
111 Object result = null;
112
113
114 StringBuffer query = new StringBuffer("SELECT * FROM ");
115 query.append(table).append(" WHERE ");
116 query.append(keyColumn).append("=?");
117 if (nameColumn != null)
118 {
119 query.append(" AND " + nameColumn + "=?");
120 }
121
122 Connection conn = null;
123 PreparedStatement pstmt = null;
124
125 try
126 {
127 conn = getConnection();
128
129
130 pstmt = conn.prepareStatement(query.toString());
131 pstmt.setString(1, key);
132 if (nameColumn != null)
133 {
134 pstmt.setString(2, name);
135 }
136
137 ResultSet rs = pstmt.executeQuery();
138
139 List results = new ArrayList();
140 while (rs.next())
141 {
142 Object val = rs.getObject(valueColumn);
143 if (isDelimiterParsingDisabled())
144 {
145 results.add(val);
146 }
147 else
148 {
149
150 CollectionUtils.addAll(results, PropertyConverter
151 .toIterator(val, getListDelimiter()));
152 }
153 }
154
155 if (!results.isEmpty())
156 {
157 result = (results.size() > 1) ? results : results.get(0);
158 }
159 }
160 catch (SQLException e)
161 {
162 fireError(EVENT_READ_PROPERTY, key, null, e);
163 }
164 finally
165 {
166 closeQuietly(conn, pstmt);
167 }
168
169 return result;
170 }
171
172 /***
173 * Adds a property to this configuration. If this causes a database error,
174 * an error event will be generated of type <code>EVENT_ADD_PROPERTY</code>
175 * with the causing exception. The event's <code>propertyName</code> is
176 * set to the passed in property key, the <code>propertyValue</code>
177 * points to the passed in value.
178 *
179 * @param key the property key
180 * @param obj the value of the property to add
181 */
182 protected void addPropertyDirect(String key, Object obj)
183 {
184
185 StringBuffer query = new StringBuffer("INSERT INTO " + table);
186 if (nameColumn != null)
187 {
188 query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
189 }
190 else
191 {
192 query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
193 }
194
195 Connection conn = null;
196 PreparedStatement pstmt = null;
197
198 try
199 {
200 conn = getConnection();
201
202
203 pstmt = conn.prepareStatement(query.toString());
204 int index = 1;
205 if (nameColumn != null)
206 {
207 pstmt.setString(index++, name);
208 }
209 pstmt.setString(index++, key);
210 pstmt.setString(index++, String.valueOf(obj));
211
212 pstmt.executeUpdate();
213 }
214 catch (SQLException e)
215 {
216 fireError(EVENT_ADD_PROPERTY, key, obj, e);
217 }
218 finally
219 {
220
221 closeQuietly(conn, pstmt);
222 }
223 }
224
225 /***
226 * Adds a property to this configuration. This implementation will
227 * temporarily disable list delimiter parsing, so that even if the value
228 * contains the list delimiter, only a single record will be written into
229 * the managed table. The implementation of <code>getProperty()</code>
230 * will take care about delimiters. So list delimiters are fully supported
231 * by <code>DatabaseConfiguration</code>, but internally treated a bit
232 * differently.
233 *
234 * @param key the key of the new property
235 * @param value the value to be added
236 */
237 public void addProperty(String key, Object value)
238 {
239 boolean parsingFlag = isDelimiterParsingDisabled();
240 try
241 {
242 if (value instanceof String)
243 {
244
245 setDelimiterParsingDisabled(true);
246 }
247 super.addProperty(key, value);
248 }
249 finally
250 {
251 setDelimiterParsingDisabled(parsingFlag);
252 }
253 }
254
255 /***
256 * Checks if this configuration is empty. If this causes a database error,
257 * an error event will be generated of type <code>EVENT_READ_PROPERTY</code>
258 * with the causing exception. Both the event's <code>propertyName</code>
259 * and <code>propertyValue</code> will be undefined.
260 *
261 * @return a flag whether this configuration is empty.
262 */
263 public boolean isEmpty()
264 {
265 boolean empty = true;
266
267
268 StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table);
269 if (nameColumn != null)
270 {
271 query.append(" WHERE " + nameColumn + "=?");
272 }
273
274 Connection conn = null;
275 PreparedStatement pstmt = null;
276
277 try
278 {
279 conn = getConnection();
280
281
282 pstmt = conn.prepareStatement(query.toString());
283 if (nameColumn != null)
284 {
285 pstmt.setString(1, name);
286 }
287
288 ResultSet rs = pstmt.executeQuery();
289
290 if (rs.next())
291 {
292 empty = rs.getInt(1) == 0;
293 }
294 }
295 catch (SQLException e)
296 {
297 fireError(EVENT_READ_PROPERTY, null, null, e);
298 }
299 finally
300 {
301
302 closeQuietly(conn, pstmt);
303 }
304
305 return empty;
306 }
307
308 /***
309 * Checks whether this configuration contains the specified key. If this
310 * causes a database error, an error event will be generated of type
311 * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
312 * event's <code>propertyName</code> will be set to the passed in key, the
313 * <code>propertyValue</code> will be undefined.
314 *
315 * @param key the key to be checked
316 * @return a flag whether this key is defined
317 */
318 public boolean containsKey(String key)
319 {
320 boolean found = false;
321
322
323 StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
324 if (nameColumn != null)
325 {
326 query.append(" AND " + nameColumn + "=?");
327 }
328
329 Connection conn = null;
330 PreparedStatement pstmt = null;
331
332 try
333 {
334 conn = getConnection();
335
336
337 pstmt = conn.prepareStatement(query.toString());
338 pstmt.setString(1, key);
339 if (nameColumn != null)
340 {
341 pstmt.setString(2, name);
342 }
343
344 ResultSet rs = pstmt.executeQuery();
345
346 found = rs.next();
347 }
348 catch (SQLException e)
349 {
350 fireError(EVENT_READ_PROPERTY, key, null, e);
351 }
352 finally
353 {
354
355 closeQuietly(conn, pstmt);
356 }
357
358 return found;
359 }
360
361 /***
362 * Removes the specified value from this configuration. If this causes a
363 * database error, an error event will be generated of type
364 * <code>EVENT_CLEAR_PROPERTY</code> with the causing exception. The
365 * event's <code>propertyName</code> will be set to the passed in key, the
366 * <code>propertyValue</code> will be undefined.
367 *
368 * @param key the key of the property to be removed
369 */
370 public void clearProperty(String key)
371 {
372
373 StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
374 if (nameColumn != null)
375 {
376 query.append(" AND " + nameColumn + "=?");
377 }
378
379 Connection conn = null;
380 PreparedStatement pstmt = null;
381
382 try
383 {
384 conn = getConnection();
385
386
387 pstmt = conn.prepareStatement(query.toString());
388 pstmt.setString(1, key);
389 if (nameColumn != null)
390 {
391 pstmt.setString(2, name);
392 }
393
394 pstmt.executeUpdate();
395 }
396 catch (SQLException e)
397 {
398 fireError(EVENT_CLEAR_PROPERTY, key, null, e);
399 }
400 finally
401 {
402
403 closeQuietly(conn, pstmt);
404 }
405 }
406
407 /***
408 * Removes all entries from this configuration. If this causes a database
409 * error, an error event will be generated of type
410 * <code>EVENT_CLEAR</code> with the causing exception. Both the
411 * event's <code>propertyName</code> and the <code>propertyValue</code>
412 * will be undefined.
413 */
414 public void clear()
415 {
416
417 StringBuffer query = new StringBuffer("DELETE FROM " + table);
418 if (nameColumn != null)
419 {
420 query.append(" WHERE " + nameColumn + "=?");
421 }
422
423 Connection conn = null;
424 PreparedStatement pstmt = null;
425
426 try
427 {
428 conn = getConnection();
429
430
431 pstmt = conn.prepareStatement(query.toString());
432 if (nameColumn != null)
433 {
434 pstmt.setString(1, name);
435 }
436
437 pstmt.executeUpdate();
438 }
439 catch (SQLException e)
440 {
441 fireError(EVENT_CLEAR, null, null, e);
442 }
443 finally
444 {
445
446 closeQuietly(conn, pstmt);
447 }
448 }
449
450 /***
451 * Returns an iterator with the names of all properties contained in this
452 * configuration. If this causes a database
453 * error, an error event will be generated of type
454 * <code>EVENT_READ_PROPERTY</code> with the causing exception. Both the
455 * event's <code>propertyName</code> and the <code>propertyValue</code>
456 * will be undefined.
457 * @return an iterator with the contained keys (an empty iterator in case
458 * of an error)
459 */
460 public Iterator getKeys()
461 {
462 Collection keys = new ArrayList();
463
464
465 StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
466 if (nameColumn != null)
467 {
468 query.append(" WHERE " + nameColumn + "=?");
469 }
470
471 Connection conn = null;
472 PreparedStatement pstmt = null;
473
474 try
475 {
476 conn = getConnection();
477
478
479 pstmt = conn.prepareStatement(query.toString());
480 if (nameColumn != null)
481 {
482 pstmt.setString(1, name);
483 }
484
485 ResultSet rs = pstmt.executeQuery();
486
487 while (rs.next())
488 {
489 keys.add(rs.getString(1));
490 }
491 }
492 catch (SQLException e)
493 {
494 fireError(EVENT_READ_PROPERTY, null, null, e);
495 }
496 finally
497 {
498
499 closeQuietly(conn, pstmt);
500 }
501
502 return keys.iterator();
503 }
504
505 /***
506 * Returns the used <code>DataSource</code> object.
507 *
508 * @return the data source
509 * @since 1.4
510 */
511 public DataSource getDatasource()
512 {
513 return datasource;
514 }
515
516 /***
517 * Returns a <code>Connection</code> object. This method is called when
518 * ever the database is to be accessed. This implementation returns a
519 * connection from the current <code>DataSource</code>.
520 *
521 * @return the <code>Connection</code> object to be used
522 * @throws SQLException if an error occurs
523 * @since 1.4
524 */
525 protected Connection getConnection() throws SQLException
526 {
527 return getDatasource().getConnection();
528 }
529
530 /***
531 * Close a <code>Connection</code> and, <code>Statement</code>.
532 * Avoid closing if null and hide any SQLExceptions that occur.
533 *
534 * @param conn The database connection to close
535 * @param stmt The statement to close
536 */
537 private void closeQuietly(Connection conn, Statement stmt)
538 {
539 try
540 {
541 if (stmt != null)
542 {
543 stmt.close();
544 }
545 if (conn != null)
546 {
547 conn.close();
548 }
549 }
550 catch (SQLException e)
551 {
552 getLogger().error(e.getMessage(), e);
553 }
554 }
555 }