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 }