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