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.collections.CollectionUtils;
033 import org.apache.commons.logging.LogFactory;
034
035 /**
036 * Configuration stored in a database. The properties are retrieved from a
037 * table containing at least one column for the keys, and one column for the
038 * values. It's possible to store several configurations in the same table by
039 * adding a column containing the name of the configuration. The name of the
040 * table and the columns is specified in the constructor.
041 *
042 * <h4>Example 1 - One configuration per table</h4>
043 *
044 * <pre>
045 * CREATE TABLE myconfig (
046 * `key` VARCHAR NOT NULL PRIMARY KEY,
047 * `value` VARCHAR
048 * );
049 *
050 * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
051 *
052 *
053 * Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
054 * String value = config.getString("foo");
055 * </pre>
056 *
057 * <h4>Example 2 - Multiple configurations per table</h4>
058 *
059 * <pre>
060 * CREATE TABLE myconfigs (
061 * `name` VARCHAR NOT NULL,
062 * `key` VARCHAR NOT NULL,
063 * `value` VARCHAR,
064 * CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
065 * );
066 *
067 * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
068 * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
069 *
070 *
071 * Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
072 * String value1 = conf.getString("key1");
073 *
074 * Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
075 * String value2 = conf.getString("key2");
076 * </pre>
077 * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
078 * @since 1.0
079 *
080 * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
081 * @version $Revision: 613503 $, $Date: 2008-01-20 05:39:08 +0100 (So, 20 Jan 2008) $
082 */
083 public class DatabaseConfiguration extends AbstractConfiguration
084 {
085 /** The datasource to connect to the database. */
086 private DataSource datasource;
087
088 /** The name of the table containing the configurations. */
089 private String table;
090
091 /** The column containing the name of the configuration. */
092 private String nameColumn;
093
094 /** The column containing the keys. */
095 private String keyColumn;
096
097 /** The column containing the values. */
098 private String valueColumn;
099
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 }