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.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 1208807 2011-11-30 21:34:29Z oheger $ 087 */ 088 public class DatabaseConfiguration extends AbstractConfiguration 089 { 090 /** The datasource to connect to the database. */ 091 private DataSource datasource; 092 093 /** The name of the table containing the configurations. */ 094 private String table; 095 096 /** The column containing the name of the configuration. */ 097 private String nameColumn; 098 099 /** The column containing the keys. */ 100 private String keyColumn; 101 102 /** The column containing the values. */ 103 private String valueColumn; 104 105 /** The name of the configuration. */ 106 private 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 223 try 224 { 225 conn = getConnection(); 226 227 // bind the parameters 228 pstmt = conn.prepareStatement(query.toString()); 229 pstmt.setString(1, key); 230 if (nameColumn != null) 231 { 232 pstmt.setString(2, name); 233 } 234 235 ResultSet rs = pstmt.executeQuery(); 236 237 List<Object> results = new ArrayList<Object>(); 238 while (rs.next()) 239 { 240 Object value = rs.getObject(valueColumn); 241 if (isDelimiterParsingDisabled()) 242 { 243 results.add(value); 244 } 245 else 246 { 247 // Split value if it contains the list delimiter 248 Iterator<?> it = PropertyConverter.toIterator(value, getListDelimiter()); 249 while (it.hasNext()) 250 { 251 results.add(it.next()); 252 } 253 } 254 } 255 256 if (!results.isEmpty()) 257 { 258 result = (results.size() > 1) ? results : results.get(0); 259 } 260 } 261 catch (SQLException e) 262 { 263 fireError(EVENT_READ_PROPERTY, key, null, e); 264 } 265 finally 266 { 267 close(conn, pstmt); 268 } 269 270 return result; 271 } 272 273 /** 274 * Adds a property to this configuration. If this causes a database error, 275 * an error event will be generated of type {@code EVENT_ADD_PROPERTY} 276 * with the causing exception. The event's {@code propertyName} is 277 * set to the passed in property key, the {@code propertyValue} 278 * points to the passed in value. 279 * 280 * @param key the property key 281 * @param obj the value of the property to add 282 */ 283 @Override 284 protected void addPropertyDirect(String key, Object obj) 285 { 286 // build the query 287 StringBuilder query = new StringBuilder("INSERT INTO " + table); 288 if (nameColumn != null) 289 { 290 query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)"); 291 } 292 else 293 { 294 query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)"); 295 } 296 297 Connection conn = null; 298 PreparedStatement pstmt = null; 299 300 try 301 { 302 conn = getConnection(); 303 304 // bind the parameters 305 pstmt = conn.prepareStatement(query.toString()); 306 int index = 1; 307 if (nameColumn != null) 308 { 309 pstmt.setString(index++, name); 310 } 311 pstmt.setString(index++, key); 312 pstmt.setString(index++, String.valueOf(obj)); 313 314 pstmt.executeUpdate(); 315 commitIfRequired(conn); 316 } 317 catch (SQLException e) 318 { 319 fireError(EVENT_ADD_PROPERTY, key, obj, e); 320 } 321 finally 322 { 323 // clean up 324 close(conn, pstmt); 325 } 326 } 327 328 /** 329 * Adds a property to this configuration. This implementation will 330 * temporarily disable list delimiter parsing, so that even if the value 331 * contains the list delimiter, only a single record will be written into 332 * the managed table. The implementation of {@code getProperty()} 333 * will take care about delimiters. So list delimiters are fully supported 334 * by {@code DatabaseConfiguration}, but internally treated a bit 335 * differently. 336 * 337 * @param key the key of the new property 338 * @param value the value to be added 339 */ 340 @Override 341 public void addProperty(String key, Object value) 342 { 343 boolean parsingFlag = isDelimiterParsingDisabled(); 344 try 345 { 346 if (value instanceof String) 347 { 348 // temporarily disable delimiter parsing 349 setDelimiterParsingDisabled(true); 350 } 351 super.addProperty(key, value); 352 } 353 finally 354 { 355 setDelimiterParsingDisabled(parsingFlag); 356 } 357 } 358 359 /** 360 * Checks if this configuration is empty. If this causes a database error, 361 * an error event will be generated of type {@code EVENT_READ_PROPERTY} 362 * with the causing exception. Both the event's {@code propertyName} 363 * and {@code propertyValue} will be undefined. 364 * 365 * @return a flag whether this configuration is empty. 366 */ 367 public boolean isEmpty() 368 { 369 boolean empty = true; 370 371 // build the query 372 StringBuilder query = new StringBuilder("SELECT count(*) FROM " + table); 373 if (nameColumn != null) 374 { 375 query.append(" WHERE " + nameColumn + "=?"); 376 } 377 378 Connection conn = null; 379 PreparedStatement pstmt = null; 380 381 try 382 { 383 conn = getConnection(); 384 385 // bind the parameters 386 pstmt = conn.prepareStatement(query.toString()); 387 if (nameColumn != null) 388 { 389 pstmt.setString(1, name); 390 } 391 392 ResultSet rs = pstmt.executeQuery(); 393 394 if (rs.next()) 395 { 396 empty = rs.getInt(1) == 0; 397 } 398 } 399 catch (SQLException e) 400 { 401 fireError(EVENT_READ_PROPERTY, null, null, e); 402 } 403 finally 404 { 405 // clean up 406 close(conn, pstmt); 407 } 408 409 return empty; 410 } 411 412 /** 413 * Checks whether this configuration contains the specified key. If this 414 * causes a database error, an error event will be generated of type 415 * {@code EVENT_READ_PROPERTY} with the causing exception. The 416 * event's {@code propertyName} will be set to the passed in key, the 417 * {@code propertyValue} will be undefined. 418 * 419 * @param key the key to be checked 420 * @return a flag whether this key is defined 421 */ 422 public boolean containsKey(String key) 423 { 424 boolean found = false; 425 426 // build the query 427 StringBuilder query = new StringBuilder("SELECT * FROM " + table + " WHERE " + keyColumn + "=?"); 428 if (nameColumn != null) 429 { 430 query.append(" AND " + nameColumn + "=?"); 431 } 432 433 Connection conn = null; 434 PreparedStatement pstmt = null; 435 436 try 437 { 438 conn = getConnection(); 439 440 // bind the parameters 441 pstmt = conn.prepareStatement(query.toString()); 442 pstmt.setString(1, key); 443 if (nameColumn != null) 444 { 445 pstmt.setString(2, name); 446 } 447 448 ResultSet rs = pstmt.executeQuery(); 449 450 found = rs.next(); 451 } 452 catch (SQLException e) 453 { 454 fireError(EVENT_READ_PROPERTY, key, null, e); 455 } 456 finally 457 { 458 // clean up 459 close(conn, pstmt); 460 } 461 462 return found; 463 } 464 465 /** 466 * Removes the specified value from this configuration. If this causes a 467 * database error, an error event will be generated of type 468 * {@code EVENT_CLEAR_PROPERTY} with the causing exception. The 469 * event's {@code propertyName} will be set to the passed in key, the 470 * {@code propertyValue} will be undefined. 471 * 472 * @param key the key of the property to be removed 473 */ 474 @Override 475 protected void clearPropertyDirect(String key) 476 { 477 // build the query 478 StringBuilder query = new StringBuilder("DELETE FROM " + table + " WHERE " + keyColumn + "=?"); 479 if (nameColumn != null) 480 { 481 query.append(" AND " + nameColumn + "=?"); 482 } 483 484 Connection conn = null; 485 PreparedStatement pstmt = null; 486 487 try 488 { 489 conn = getConnection(); 490 491 // bind the parameters 492 pstmt = conn.prepareStatement(query.toString()); 493 pstmt.setString(1, key); 494 if (nameColumn != null) 495 { 496 pstmt.setString(2, name); 497 } 498 499 pstmt.executeUpdate(); 500 commitIfRequired(conn); 501 } 502 catch (SQLException e) 503 { 504 fireError(EVENT_CLEAR_PROPERTY, key, null, e); 505 } 506 finally 507 { 508 // clean up 509 close(conn, pstmt); 510 } 511 } 512 513 /** 514 * Removes all entries from this configuration. If this causes a database 515 * error, an error event will be generated of type 516 * {@code EVENT_CLEAR} with the causing exception. Both the 517 * event's {@code propertyName} and the {@code propertyValue} 518 * will be undefined. 519 */ 520 @Override 521 public void clear() 522 { 523 fireEvent(EVENT_CLEAR, null, null, true); 524 // build the query 525 StringBuilder query = new StringBuilder("DELETE FROM " + table); 526 if (nameColumn != null) 527 { 528 query.append(" WHERE " + nameColumn + "=?"); 529 } 530 531 Connection conn = null; 532 PreparedStatement pstmt = null; 533 534 try 535 { 536 conn = getConnection(); 537 538 // bind the parameters 539 pstmt = conn.prepareStatement(query.toString()); 540 if (nameColumn != null) 541 { 542 pstmt.setString(1, name); 543 } 544 545 pstmt.executeUpdate(); 546 commitIfRequired(conn); 547 } 548 catch (SQLException e) 549 { 550 fireError(EVENT_CLEAR, null, null, e); 551 } 552 finally 553 { 554 // clean up 555 close(conn, pstmt); 556 } 557 fireEvent(EVENT_CLEAR, null, null, false); 558 } 559 560 /** 561 * Returns an iterator with the names of all properties contained in this 562 * configuration. If this causes a database 563 * error, an error event will be generated of type 564 * {@code EVENT_READ_PROPERTY} with the causing exception. Both the 565 * event's {@code propertyName} and the {@code propertyValue} 566 * will be undefined. 567 * @return an iterator with the contained keys (an empty iterator in case 568 * of an error) 569 */ 570 public Iterator<String> getKeys() 571 { 572 Collection<String> keys = new ArrayList<String>(); 573 574 // build the query 575 StringBuilder query = new StringBuilder("SELECT DISTINCT " + keyColumn + " FROM " + table); 576 if (nameColumn != null) 577 { 578 query.append(" WHERE " + nameColumn + "=?"); 579 } 580 581 Connection conn = null; 582 PreparedStatement pstmt = null; 583 584 try 585 { 586 conn = getConnection(); 587 588 // bind the parameters 589 pstmt = conn.prepareStatement(query.toString()); 590 if (nameColumn != null) 591 { 592 pstmt.setString(1, name); 593 } 594 595 ResultSet rs = pstmt.executeQuery(); 596 597 while (rs.next()) 598 { 599 keys.add(rs.getString(1)); 600 } 601 } 602 catch (SQLException e) 603 { 604 fireError(EVENT_READ_PROPERTY, null, null, e); 605 } 606 finally 607 { 608 // clean up 609 close(conn, pstmt); 610 } 611 612 return keys.iterator(); 613 } 614 615 /** 616 * Returns the used {@code DataSource} object. 617 * 618 * @return the data source 619 * @since 1.4 620 */ 621 public DataSource getDatasource() 622 { 623 return datasource; 624 } 625 626 /** 627 * Returns a {@code Connection} object. This method is called when 628 * ever the database is to be accessed. This implementation returns a 629 * connection from the current {@code DataSource}. 630 * 631 * @return the {@code Connection} object to be used 632 * @throws SQLException if an error occurs 633 * @since 1.4 634 * @deprecated Use a custom data source to change the connection used by the 635 * class. To be removed in Commons Configuration 2.0 636 */ 637 @Deprecated 638 protected Connection getConnection() throws SQLException 639 { 640 return getDatasource().getConnection(); 641 } 642 643 /** 644 * Close a {@code Connection} and, {@code Statement}. 645 * Avoid closing if null and hide any SQLExceptions that occur. 646 * 647 * @param conn The database connection to close 648 * @param stmt The statement to close 649 */ 650 private void close(Connection conn, Statement stmt) 651 { 652 try 653 { 654 if (stmt != null) 655 { 656 stmt.close(); 657 } 658 } 659 catch (SQLException e) 660 { 661 getLogger().error("An error occured on closing the statement", e); 662 } 663 664 try 665 { 666 if (conn != null) 667 { 668 conn.close(); 669 } 670 } 671 catch (SQLException e) 672 { 673 getLogger().error("An error occured on closing the connection", e); 674 } 675 } 676 677 /** 678 * Performs a commit if needed. This method is called after updates of the 679 * managed database table. If the configuration should perform commits, it 680 * does so now. 681 * 682 * @param conn the active connection 683 * @throws SQLException if an error occurs 684 */ 685 private void commitIfRequired(Connection conn) throws SQLException 686 { 687 if (isDoCommits()) 688 { 689 conn.commit(); 690 } 691 } 692 }