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 * The configuration can be instructed to perform commits after database updates. 078 * This is achieved by setting the <code>commits</code> parameter of the 079 * constructors to <b>true</b>. If commits should not be performed (which is the 080 * default behavior), it should be ensured that the connections returned by the 081 * <code>DataSource</code> are in auto-commit mode. 082 * 083 * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1> 084 * @since 1.0 085 * 086 * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a> 087 * @version $Revision: 1158118 $, $Date: 2011-08-16 08:13:22 +0200 (Di, 16. Aug 2011) $ 088 */ 089 public class DatabaseConfiguration extends AbstractConfiguration 090 { 091 /** The datasource to connect to the database. */ 092 private DataSource datasource; 093 094 /** The name of the table containing the configurations. */ 095 private String table; 096 097 /** The column containing the name of the configuration. */ 098 private String nameColumn; 099 100 /** The column containing the keys. */ 101 private String keyColumn; 102 103 /** The column containing the values. */ 104 private String valueColumn; 105 106 /** The name of the configuration. */ 107 private String name; 108 109 /** A flag whether commits should be performed by this configuration. */ 110 private final boolean doCommits; 111 112 /** 113 * Build a configuration from a table containing multiple configurations. 114 * No commits are performed by the new configuration instance. 115 * 116 * @param datasource the datasource to connect to the database 117 * @param table the name of the table containing the configurations 118 * @param nameColumn the column containing the name of the configuration 119 * @param keyColumn the column containing the keys of the configuration 120 * @param valueColumn the column containing the values of the configuration 121 * @param name the name of the configuration 122 */ 123 public DatabaseConfiguration(DataSource datasource, String table, String nameColumn, 124 String keyColumn, String valueColumn, String name) 125 { 126 this(datasource, table, nameColumn, keyColumn, valueColumn, name, false); 127 } 128 129 /** 130 * Creates a new instance of <code>DatabaseConfiguration</code> that operates on 131 * a database table containing multiple configurations. 132 * 133 * @param datasource the <code>DataSource</code> to connect to the database 134 * @param table the name of the table containing the configurations 135 * @param nameColumn the column containing the name of the configuration 136 * @param keyColumn the column containing the keys of the configuration 137 * @param valueColumn the column containing the values of the configuration 138 * @param name the name of the configuration 139 * @param commits a flag whether the configuration should perform a commit 140 * after a database update 141 */ 142 public DatabaseConfiguration(DataSource datasource, String table, 143 String nameColumn, String keyColumn, String valueColumn, 144 String name, boolean commits) 145 { 146 this.datasource = datasource; 147 this.table = table; 148 this.nameColumn = nameColumn; 149 this.keyColumn = keyColumn; 150 this.valueColumn = valueColumn; 151 this.name = name; 152 doCommits = commits; 153 setLogger(LogFactory.getLog(getClass())); 154 addErrorLogListener(); // log errors per default 155 } 156 157 /** 158 * Build a configuration from a table. 159 * 160 * @param datasource the datasource to connect to the database 161 * @param table the name of the table containing the configurations 162 * @param keyColumn the column containing the keys of the configuration 163 * @param valueColumn the column containing the values of the configuration 164 */ 165 public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn) 166 { 167 this(datasource, table, null, keyColumn, valueColumn, null); 168 } 169 170 /** 171 * Creates a new instance of <code>DatabaseConfiguration</code> that 172 * operates on a database table containing a single configuration only. 173 * 174 * @param datasource the <code>DataSource</code> to connect to the database 175 * @param table the name of the table containing the configurations 176 * @param keyColumn the column containing the keys of the configuration 177 * @param valueColumn the column containing the values of the configuration 178 * @param commits a flag whether the configuration should perform a commit 179 * after a database update 180 */ 181 public DatabaseConfiguration(DataSource datasource, String table, 182 String keyColumn, String valueColumn, boolean commits) 183 { 184 this(datasource, table, null, keyColumn, valueColumn, null, commits); 185 } 186 187 /** 188 * Returns a flag whether this configuration performs commits after database 189 * updates. 190 * 191 * @return a flag whether commits are performed 192 */ 193 public boolean isDoCommits() 194 { 195 return doCommits; 196 } 197 198 /** 199 * Returns the value of the specified property. If this causes a database 200 * error, an error event will be generated of type 201 * <code>EVENT_READ_PROPERTY</code> with the causing exception. The 202 * event's <code>propertyName</code> is set to the passed in property key, 203 * the <code>propertyValue</code> is undefined. 204 * 205 * @param key the key of the desired property 206 * @return the value of this property 207 */ 208 public Object getProperty(String key) 209 { 210 Object result = null; 211 212 // build the query 213 StringBuffer query = new StringBuffer("SELECT * FROM "); 214 query.append(table).append(" WHERE "); 215 query.append(keyColumn).append("=?"); 216 if (nameColumn != null) 217 { 218 query.append(" AND " + nameColumn + "=?"); 219 } 220 221 Connection conn = null; 222 PreparedStatement pstmt = 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 ResultSet rs = pstmt.executeQuery(); 237 238 List results = new ArrayList(); 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 containts the list delimiter 249 CollectionUtils.addAll(results, PropertyConverter.toIterator(value, getListDelimiter())); 250 } 251 } 252 253 if (!results.isEmpty()) 254 { 255 result = (results.size() > 1) ? results : results.get(0); 256 } 257 } 258 catch (SQLException e) 259 { 260 fireError(EVENT_READ_PROPERTY, key, null, e); 261 } 262 finally 263 { 264 close(conn, pstmt); 265 } 266 267 return result; 268 } 269 270 /** 271 * Adds a property to this configuration. If this causes a database error, 272 * an error event will be generated of type <code>EVENT_ADD_PROPERTY</code> 273 * with the causing exception. The event's <code>propertyName</code> is 274 * set to the passed in property key, the <code>propertyValue</code> 275 * points to the passed in value. 276 * 277 * @param key the property key 278 * @param obj the value of the property to add 279 */ 280 protected void addPropertyDirect(String key, Object obj) 281 { 282 // build the query 283 StringBuffer query = new StringBuffer("INSERT INTO " + table); 284 if (nameColumn != null) 285 { 286 query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)"); 287 } 288 else 289 { 290 query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)"); 291 } 292 293 Connection conn = null; 294 PreparedStatement pstmt = null; 295 296 try 297 { 298 conn = getConnection(); 299 300 // bind the parameters 301 pstmt = conn.prepareStatement(query.toString()); 302 int index = 1; 303 if (nameColumn != null) 304 { 305 pstmt.setString(index++, name); 306 } 307 pstmt.setString(index++, key); 308 pstmt.setString(index++, String.valueOf(obj)); 309 310 pstmt.executeUpdate(); 311 commitIfRequired(conn); 312 } 313 catch (SQLException e) 314 { 315 fireError(EVENT_ADD_PROPERTY, key, obj, e); 316 } 317 finally 318 { 319 // clean up 320 close(conn, pstmt); 321 } 322 } 323 324 /** 325 * Adds a property to this configuration. This implementation will 326 * temporarily disable list delimiter parsing, so that even if the value 327 * contains the list delimiter, only a single record will be written into 328 * the managed table. The implementation of <code>getProperty()</code> 329 * will take care about delimiters. So list delimiters are fully supported 330 * by <code>DatabaseConfiguration</code>, but internally treated a bit 331 * differently. 332 * 333 * @param key the key of the new property 334 * @param value the value to be added 335 */ 336 public void addProperty(String key, Object value) 337 { 338 boolean parsingFlag = isDelimiterParsingDisabled(); 339 try 340 { 341 if (value instanceof String) 342 { 343 // temporarily disable delimiter parsing 344 setDelimiterParsingDisabled(true); 345 } 346 super.addProperty(key, value); 347 } 348 finally 349 { 350 setDelimiterParsingDisabled(parsingFlag); 351 } 352 } 353 354 /** 355 * Checks if this configuration is empty. If this causes a database error, 356 * an error event will be generated of type <code>EVENT_READ_PROPERTY</code> 357 * with the causing exception. Both the event's <code>propertyName</code> 358 * and <code>propertyValue</code> will be undefined. 359 * 360 * @return a flag whether this configuration is empty. 361 */ 362 public boolean isEmpty() 363 { 364 boolean empty = true; 365 366 // build the query 367 StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table); 368 if (nameColumn != null) 369 { 370 query.append(" WHERE " + nameColumn + "=?"); 371 } 372 373 Connection conn = null; 374 PreparedStatement pstmt = null; 375 376 try 377 { 378 conn = getConnection(); 379 380 // bind the parameters 381 pstmt = conn.prepareStatement(query.toString()); 382 if (nameColumn != null) 383 { 384 pstmt.setString(1, name); 385 } 386 387 ResultSet rs = pstmt.executeQuery(); 388 389 if (rs.next()) 390 { 391 empty = rs.getInt(1) == 0; 392 } 393 } 394 catch (SQLException e) 395 { 396 fireError(EVENT_READ_PROPERTY, null, null, e); 397 } 398 finally 399 { 400 // clean up 401 close(conn, pstmt); 402 } 403 404 return empty; 405 } 406 407 /** 408 * Checks whether this configuration contains the specified key. If this 409 * causes a database error, an error event will be generated of type 410 * <code>EVENT_READ_PROPERTY</code> with the causing exception. The 411 * event's <code>propertyName</code> will be set to the passed in key, the 412 * <code>propertyValue</code> will be undefined. 413 * 414 * @param key the key to be checked 415 * @return a flag whether this key is defined 416 */ 417 public boolean containsKey(String key) 418 { 419 boolean found = false; 420 421 // build the query 422 StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?"); 423 if (nameColumn != null) 424 { 425 query.append(" AND " + nameColumn + "=?"); 426 } 427 428 Connection conn = null; 429 PreparedStatement pstmt = null; 430 431 try 432 { 433 conn = getConnection(); 434 435 // bind the parameters 436 pstmt = conn.prepareStatement(query.toString()); 437 pstmt.setString(1, key); 438 if (nameColumn != null) 439 { 440 pstmt.setString(2, name); 441 } 442 443 ResultSet rs = pstmt.executeQuery(); 444 445 found = rs.next(); 446 } 447 catch (SQLException e) 448 { 449 fireError(EVENT_READ_PROPERTY, key, null, e); 450 } 451 finally 452 { 453 // clean up 454 close(conn, pstmt); 455 } 456 457 return found; 458 } 459 460 /** 461 * Removes the specified value from this configuration. If this causes a 462 * database error, an error event will be generated of type 463 * <code>EVENT_CLEAR_PROPERTY</code> with the causing exception. The 464 * event's <code>propertyName</code> will be set to the passed in key, the 465 * <code>propertyValue</code> will be undefined. 466 * 467 * @param key the key of the property to be removed 468 */ 469 protected void clearPropertyDirect(String key) 470 { 471 // build the query 472 StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?"); 473 if (nameColumn != null) 474 { 475 query.append(" AND " + nameColumn + "=?"); 476 } 477 478 Connection conn = null; 479 PreparedStatement pstmt = null; 480 481 try 482 { 483 conn = getConnection(); 484 485 // bind the parameters 486 pstmt = conn.prepareStatement(query.toString()); 487 pstmt.setString(1, key); 488 if (nameColumn != null) 489 { 490 pstmt.setString(2, name); 491 } 492 493 pstmt.executeUpdate(); 494 commitIfRequired(conn); 495 } 496 catch (SQLException e) 497 { 498 fireError(EVENT_CLEAR_PROPERTY, key, null, e); 499 } 500 finally 501 { 502 // clean up 503 close(conn, pstmt); 504 } 505 } 506 507 /** 508 * Removes all entries from this configuration. If this causes a database 509 * error, an error event will be generated of type 510 * <code>EVENT_CLEAR</code> with the causing exception. Both the 511 * event's <code>propertyName</code> and the <code>propertyValue</code> 512 * will be undefined. 513 */ 514 public void clear() 515 { 516 fireEvent(EVENT_CLEAR, null, null, true); 517 // build the query 518 StringBuffer query = new StringBuffer("DELETE FROM " + table); 519 if (nameColumn != null) 520 { 521 query.append(" WHERE " + nameColumn + "=?"); 522 } 523 524 Connection conn = null; 525 PreparedStatement pstmt = null; 526 527 try 528 { 529 conn = getConnection(); 530 531 // bind the parameters 532 pstmt = conn.prepareStatement(query.toString()); 533 if (nameColumn != null) 534 { 535 pstmt.setString(1, name); 536 } 537 538 pstmt.executeUpdate(); 539 commitIfRequired(conn); 540 } 541 catch (SQLException e) 542 { 543 fireError(EVENT_CLEAR, null, null, e); 544 } 545 finally 546 { 547 // clean up 548 close(conn, pstmt); 549 } 550 fireEvent(EVENT_CLEAR, null, null, false); 551 } 552 553 /** 554 * Returns an iterator with the names of all properties contained in this 555 * configuration. If this causes a database 556 * error, an error event will be generated of type 557 * <code>EVENT_READ_PROPERTY</code> with the causing exception. Both the 558 * event's <code>propertyName</code> and the <code>propertyValue</code> 559 * will be undefined. 560 * @return an iterator with the contained keys (an empty iterator in case 561 * of an error) 562 */ 563 public Iterator getKeys() 564 { 565 Collection keys = new ArrayList(); 566 567 // build the query 568 StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table); 569 if (nameColumn != null) 570 { 571 query.append(" WHERE " + nameColumn + "=?"); 572 } 573 574 Connection conn = null; 575 PreparedStatement pstmt = null; 576 577 try 578 { 579 conn = getConnection(); 580 581 // bind the parameters 582 pstmt = conn.prepareStatement(query.toString()); 583 if (nameColumn != null) 584 { 585 pstmt.setString(1, name); 586 } 587 588 ResultSet rs = pstmt.executeQuery(); 589 590 while (rs.next()) 591 { 592 keys.add(rs.getString(1)); 593 } 594 } 595 catch (SQLException e) 596 { 597 fireError(EVENT_READ_PROPERTY, null, null, e); 598 } 599 finally 600 { 601 // clean up 602 close(conn, pstmt); 603 } 604 605 return keys.iterator(); 606 } 607 608 /** 609 * Returns the used <code>DataSource</code> object. 610 * 611 * @return the data source 612 * @since 1.4 613 */ 614 public DataSource getDatasource() 615 { 616 return datasource; 617 } 618 619 /** 620 * Returns a <code>Connection</code> object. This method is called when 621 * ever the database is to be accessed. This implementation returns a 622 * connection from the current <code>DataSource</code>. 623 * 624 * @return the <code>Connection</code> object to be used 625 * @throws SQLException if an error occurs 626 * @since 1.4 627 * @deprecated Use a custom data source to change the connection used by the 628 * class. To be removed in Commons Configuration 2.0 629 */ 630 protected Connection getConnection() throws SQLException 631 { 632 return getDatasource().getConnection(); 633 } 634 635 /** 636 * Close a <code>Connection</code> and, <code>Statement</code>. 637 * Avoid closing if null and hide any SQLExceptions that occur. 638 * 639 * @param conn The database connection to close 640 * @param stmt The statement to close 641 */ 642 private void close(Connection conn, Statement stmt) 643 { 644 try 645 { 646 if (stmt != null) 647 { 648 stmt.close(); 649 } 650 } 651 catch (SQLException e) 652 { 653 getLogger().error("An error occured on closing the statement", e); 654 } 655 656 try 657 { 658 if (conn != null) 659 { 660 conn.close(); 661 } 662 } 663 catch (SQLException e) 664 { 665 getLogger().error("An error occured on closing the connection", e); 666 } 667 } 668 669 /** 670 * Performs a commit if needed. This method is called after updates of the 671 * managed database table. If the configuration should perform commits, it 672 * does so now. 673 * 674 * @param conn the active connection 675 * @throws SQLException if an error occurs 676 */ 677 private void commitIfRequired(Connection conn) throws SQLException 678 { 679 if (isDoCommits()) 680 { 681 conn.commit(); 682 } 683 } 684 }