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.configuration2; 019 020import java.sql.Clob; 021import java.sql.Connection; 022import java.sql.PreparedStatement; 023import java.sql.ResultSet; 024import java.sql.SQLException; 025import java.sql.Statement; 026import java.util.ArrayList; 027import java.util.Collection; 028import java.util.Iterator; 029import java.util.List; 030 031import javax.sql.DataSource; 032 033import org.apache.commons.configuration2.convert.DisabledListDelimiterHandler; 034import org.apache.commons.configuration2.convert.ListDelimiterHandler; 035import org.apache.commons.configuration2.event.ConfigurationErrorEvent; 036import org.apache.commons.configuration2.event.ConfigurationEvent; 037import org.apache.commons.configuration2.event.EventType; 038import org.apache.commons.configuration2.io.ConfigurationLogger; 039import org.apache.commons.lang3.StringUtils; 040 041/** 042 * Configuration stored in a database. The properties are retrieved from a table containing at least one column for the 043 * keys, and one column for the values. It's possible to store several configurations in the same table by adding a 044 * column containing the name of the configuration. The name of the table and the columns have to be specified using the 045 * corresponding properties. 046 * <p> 047 * The recommended way to create an instance of {@code DatabaseConfiguration} is to use a <em>configuration 048 * builder</em>. The builder is configured with a special parameters object defining the database structures used by the 049 * configuration. Such an object can be created using the {@code database()} method of the {@code Parameters} class. See 050 * the examples below for more details. 051 * </p> 052 * 053 * <p> 054 * <strong>Example 1 - One configuration per table</strong> 055 * </p> 056 * 057 * <pre> 058 * CREATE TABLE myconfig ( 059 * `key` VARCHAR NOT NULL PRIMARY KEY, 060 * `value` VARCHAR 061 * ); 062 * 063 * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar'); 064 * 065 * BasicConfigurationBuilder<DatabaseConfiguration> builder = 066 * new BasicConfigurationBuilder<DatabaseConfiguration>(DatabaseConfiguration.class); 067 * builder.configure( 068 * Parameters.database() 069 * .setDataSource(dataSource) 070 * .setTable("myconfig") 071 * .setKeyColumn("key") 072 * .setValueColumn("value") 073 * ); 074 * Configuration config = builder.getConfiguration(); 075 * String value = config.getString("foo"); 076 * </pre> 077 * 078 * <p> 079 * <strong>Example 2 - Multiple configurations per table</strong> 080 * </p> 081 * 082 * <pre> 083 * CREATE TABLE myconfigs ( 084 * `name` VARCHAR NOT NULL, 085 * `key` VARCHAR NOT NULL, 086 * `value` VARCHAR, 087 * CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`) 088 * ); 089 * 090 * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1'); 091 * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2'); 092 * 093 * BasicConfigurationBuilder<DatabaseConfiguration> builder = 094 * new BasicConfigurationBuilder<DatabaseConfiguration>(DatabaseConfiguration.class); 095 * builder.configure( 096 * Parameters.database() 097 * .setDataSource(dataSource) 098 * .setTable("myconfigs") 099 * .setKeyColumn("key") 100 * .setValueColumn("value") 101 * .setConfigurationNameColumn("name") 102 * .setConfigurationName("config1") 103 * ); 104 * Configuration config1 = new DatabaseConfiguration(dataSource, "myconfigs", "name", "key", "value", "config1"); 105 * String value1 = conf.getString("key1"); 106 * </pre> 107 * 108 * The configuration can be instructed to perform commits after database updates. This is achieved by setting the 109 * {@code commits} parameter of the constructors to <b>true</b>. If commits should not be performed (which is the 110 * default behavior), it should be ensured that the connections returned by the {@code DataSource} are in auto-commit 111 * mode. 112 * 113 * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1> 114 * 115 * @since 1.0 116 */ 117public class DatabaseConfiguration extends AbstractConfiguration { 118 /** Constant for the statement used by getProperty. */ 119 private static final String SQL_GET_PROPERTY = "SELECT * FROM %s WHERE %s =?"; 120 121 /** Constant for the statement used by isEmpty. */ 122 private static final String SQL_IS_EMPTY = "SELECT count(*) FROM %s WHERE 1 = 1"; 123 124 /** Constant for the statement used by clearProperty. */ 125 private static final String SQL_CLEAR_PROPERTY = "DELETE FROM %s WHERE %s =?"; 126 127 /** Constant for the statement used by clear. */ 128 private static final String SQL_CLEAR = "DELETE FROM %s WHERE 1 = 1"; 129 130 /** Constant for the statement used by getKeys. */ 131 private static final String SQL_GET_KEYS = "SELECT DISTINCT %s FROM %s WHERE 1 = 1"; 132 133 /** The data source to connect to the database. */ 134 private DataSource dataSource; 135 136 /** The configurationName of the table containing the configurations. */ 137 private String table; 138 139 /** The column containing the configurationName of the configuration. */ 140 private String configurationNameColumn; 141 142 /** The column containing the keys. */ 143 private String keyColumn; 144 145 /** The column containing the values. */ 146 private String valueColumn; 147 148 /** The configurationName of the configuration. */ 149 private String configurationName; 150 151 /** A flag whether commits should be performed by this configuration. */ 152 private boolean autoCommit; 153 154 /** 155 * Creates a new instance of {@code DatabaseConfiguration}. 156 */ 157 public DatabaseConfiguration() { 158 initLogger(new ConfigurationLogger(DatabaseConfiguration.class)); 159 addErrorLogListener(); 160 } 161 162 /** 163 * Gets the {@code DataSource} for obtaining database connections. 164 * 165 * @return the {@code DataSource} 166 */ 167 public DataSource getDataSource() { 168 return dataSource; 169 } 170 171 /** 172 * Sets the {@code DataSource} for obtaining database connections. 173 * 174 * @param dataSource the {@code DataSource} 175 */ 176 public void setDataSource(final DataSource dataSource) { 177 this.dataSource = dataSource; 178 } 179 180 /** 181 * Gets the name of the table containing configuration data. 182 * 183 * @return the name of the table to be queried 184 */ 185 public String getTable() { 186 return table; 187 } 188 189 /** 190 * Sets the name of the table containing configuration data. 191 * 192 * @param table the table name 193 */ 194 public void setTable(final String table) { 195 this.table = table; 196 } 197 198 /** 199 * Gets the name of the table column with the configuration name. 200 * 201 * @return the name of the configuration name column 202 */ 203 public String getConfigurationNameColumn() { 204 return configurationNameColumn; 205 } 206 207 /** 208 * Sets the name of the table column with the configuration name. 209 * 210 * @param configurationNameColumn the name of the column with the configuration name 211 */ 212 public void setConfigurationNameColumn(final String configurationNameColumn) { 213 this.configurationNameColumn = configurationNameColumn; 214 } 215 216 /** 217 * Gets the name of the column containing the configuration keys. 218 * 219 * @return the name of the key column 220 */ 221 public String getKeyColumn() { 222 return keyColumn; 223 } 224 225 /** 226 * Sets the name of the column containing the configuration keys. 227 * 228 * @param keyColumn the name of the key column 229 */ 230 public void setKeyColumn(final String keyColumn) { 231 this.keyColumn = keyColumn; 232 } 233 234 /** 235 * Gets the name of the column containing the configuration values. 236 * 237 * @return the name of the value column 238 */ 239 public String getValueColumn() { 240 return valueColumn; 241 } 242 243 /** 244 * Sets the name of the column containing the configuration values. 245 * 246 * @param valueColumn the name of the value column 247 */ 248 public void setValueColumn(final String valueColumn) { 249 this.valueColumn = valueColumn; 250 } 251 252 /** 253 * Gets the name of this configuration instance. 254 * 255 * @return the name of this configuration 256 */ 257 public String getConfigurationName() { 258 return configurationName; 259 } 260 261 /** 262 * Sets the name of this configuration instance. 263 * 264 * @param configurationName the name of this configuration 265 */ 266 public void setConfigurationName(final String configurationName) { 267 this.configurationName = configurationName; 268 } 269 270 /** 271 * Returns a flag whether this configuration performs commits after database updates. 272 * 273 * @return a flag whether commits are performed 274 */ 275 public boolean isAutoCommit() { 276 return autoCommit; 277 } 278 279 /** 280 * Sets the auto commit flag. If set to <b>true</b>, this configuration performs a commit after each database update. 281 * 282 * @param autoCommit the auto commit flag 283 */ 284 public void setAutoCommit(final boolean autoCommit) { 285 this.autoCommit = autoCommit; 286 } 287 288 /** 289 * Gets the value of the specified property. If this causes a database error, an error event will be generated of 290 * type {@code READ} with the causing exception. The event's {@code propertyName} is set to the passed in property key, 291 * the {@code propertyValue} is undefined. 292 * 293 * @param key the key of the desired property 294 * @return the value of this property 295 */ 296 @Override 297 protected Object getPropertyInternal(final String key) { 298 final JdbcOperation<Object> op = new JdbcOperation<Object>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, key, null) { 299 @Override 300 protected Object performOperation() throws SQLException { 301 final List<Object> results = new ArrayList<>(); 302 try (ResultSet rs = openResultSet(String.format(SQL_GET_PROPERTY, table, keyColumn), true, key)) { 303 while (rs.next()) { 304 // Split value if it contains the list delimiter 305 getListDelimiterHandler().parse(extractPropertyValue(rs)).forEach(results::add); 306 } 307 } 308 if (!results.isEmpty()) { 309 return results.size() > 1 ? results : results.get(0); 310 } 311 return null; 312 } 313 }; 314 315 return op.execute(); 316 } 317 318 /** 319 * Adds a property to this configuration. If this causes a database error, an error event will be generated of type 320 * {@code ADD_PROPERTY} with the causing exception. The event's {@code propertyName} is set to the passed in property 321 * key, the {@code propertyValue} points to the passed in value. 322 * 323 * @param key the property key 324 * @param obj the value of the property to add 325 */ 326 @Override 327 protected void addPropertyDirect(final String key, final Object obj) { 328 new JdbcOperation<Void>(ConfigurationErrorEvent.WRITE, ConfigurationEvent.ADD_PROPERTY, key, obj) { 329 @Override 330 protected Void performOperation() throws SQLException { 331 final StringBuilder query = new StringBuilder("INSERT INTO "); 332 query.append(table).append(" ("); 333 query.append(keyColumn).append(", "); 334 query.append(valueColumn); 335 if (configurationNameColumn != null) { 336 query.append(", ").append(configurationNameColumn); 337 } 338 query.append(") VALUES (?, ?"); 339 if (configurationNameColumn != null) { 340 query.append(", ?"); 341 } 342 query.append(")"); 343 344 try (PreparedStatement pstmt = initStatement(query.toString(), false, key, String.valueOf(obj))) { 345 if (configurationNameColumn != null) { 346 pstmt.setString(3, configurationName); 347 } 348 349 pstmt.executeUpdate(); 350 return null; 351 } 352 } 353 }.execute(); 354 } 355 356 /** 357 * Adds a property to this configuration. This implementation temporarily disables list delimiter parsing, so that even 358 * if the value contains the list delimiter, only a single record is written into the managed table. The implementation 359 * of {@code getProperty()} takes care about delimiters. So list delimiters are fully supported by 360 * {@code DatabaseConfiguration}, but internally treated a bit differently. 361 * 362 * @param key the key of the new property 363 * @param value the value to be added 364 */ 365 @Override 366 protected void addPropertyInternal(final String key, final Object value) { 367 final ListDelimiterHandler oldHandler = getListDelimiterHandler(); 368 try { 369 // temporarily disable delimiter parsing 370 setListDelimiterHandler(DisabledListDelimiterHandler.INSTANCE); 371 super.addPropertyInternal(key, value); 372 } finally { 373 setListDelimiterHandler(oldHandler); 374 } 375 } 376 377 /** 378 * Checks if this configuration is empty. If this causes a database error, an error event will be generated of type 379 * {@code READ} with the causing exception. Both the event's {@code propertyName} and {@code propertyValue} will be 380 * undefined. 381 * 382 * @return a flag whether this configuration is empty. 383 */ 384 @Override 385 protected boolean isEmptyInternal() { 386 final JdbcOperation<Integer> op = new JdbcOperation<Integer>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, null, null) { 387 @Override 388 protected Integer performOperation() throws SQLException { 389 try (ResultSet rs = openResultSet(String.format(SQL_IS_EMPTY, table), true)) { 390 return rs.next() ? Integer.valueOf(rs.getInt(1)) : null; 391 } 392 } 393 }; 394 395 final Integer count = op.execute(); 396 return count == null || count.intValue() == 0; 397 } 398 399 /** 400 * Checks whether this configuration contains the specified key. If this causes a database error, an error event will be 401 * generated of type {@code READ} with the causing exception. The event's {@code propertyName} will be set to the passed 402 * in key, the {@code propertyValue} will be undefined. 403 * 404 * @param key the key to be checked 405 * @return a flag whether this key is defined 406 */ 407 @Override 408 protected boolean containsKeyInternal(final String key) { 409 final JdbcOperation<Boolean> op = new JdbcOperation<Boolean>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, key, null) { 410 @Override 411 protected Boolean performOperation() throws SQLException { 412 try (ResultSet rs = openResultSet(String.format(SQL_GET_PROPERTY, table, keyColumn), true, key)) { 413 return rs.next(); 414 } 415 } 416 }; 417 418 final Boolean result = op.execute(); 419 return result != null && result.booleanValue(); 420 } 421 422 /** 423 * Removes the specified value from this configuration. If this causes a database error, an error event will be 424 * generated of type {@code CLEAR_PROPERTY} with the causing exception. The event's {@code propertyName} will be set to 425 * the passed in key, the {@code propertyValue} will be undefined. 426 * 427 * @param key the key of the property to be removed 428 */ 429 @Override 430 protected void clearPropertyDirect(final String key) { 431 new JdbcOperation<Void>(ConfigurationErrorEvent.WRITE, ConfigurationEvent.CLEAR_PROPERTY, key, null) { 432 @Override 433 protected Void performOperation() throws SQLException { 434 try (PreparedStatement ps = initStatement(String.format(SQL_CLEAR_PROPERTY, table, keyColumn), true, key)) { 435 ps.executeUpdate(); 436 return null; 437 } 438 } 439 }.execute(); 440 } 441 442 /** 443 * Removes all entries from this configuration. If this causes a database error, an error event will be generated of 444 * type {@code CLEAR} with the causing exception. Both the event's {@code propertyName} and the {@code propertyValue} 445 * will be undefined. 446 */ 447 @Override 448 protected void clearInternal() { 449 new JdbcOperation<Void>(ConfigurationErrorEvent.WRITE, ConfigurationEvent.CLEAR, null, null) { 450 @Override 451 protected Void performOperation() throws SQLException { 452 try (PreparedStatement statement = initStatement(String.format(SQL_CLEAR, table), true)) { 453 statement.executeUpdate(); 454 } 455 return null; 456 } 457 }.execute(); 458 } 459 460 /** 461 * Returns an iterator with the names of all properties contained in this configuration. If this causes a database 462 * error, an error event will be generated of type {@code READ} with the causing exception. Both the event's 463 * {@code propertyName} and the {@code propertyValue} will be undefined. 464 * 465 * @return an iterator with the contained keys (an empty iterator in case of an error) 466 */ 467 @Override 468 protected Iterator<String> getKeysInternal() { 469 final Collection<String> keys = new ArrayList<>(); 470 new JdbcOperation<Collection<String>>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, null, null) { 471 @Override 472 protected Collection<String> performOperation() throws SQLException { 473 try (ResultSet rs = openResultSet(String.format(SQL_GET_KEYS, keyColumn, table), true)) { 474 while (rs.next()) { 475 keys.add(rs.getString(1)); 476 } 477 return keys; 478 } 479 } 480 }.execute(); 481 482 return keys.iterator(); 483 } 484 485 /** 486 * Gets the used {@code DataSource} object. 487 * 488 * @return the data source 489 * @since 1.4 490 */ 491 public DataSource getDatasource() { 492 return dataSource; 493 } 494 495 /** 496 * Close the specified database objects. Avoid closing if null and hide any SQLExceptions that occur. 497 * 498 * @param conn The database connection to close 499 * @param stmt The statement to close 500 * @param rs the result set to close 501 */ 502 protected void close(final Connection conn, final Statement stmt, final ResultSet rs) { 503 try { 504 if (rs != null) { 505 rs.close(); 506 } 507 } catch (final SQLException e) { 508 getLogger().error("An error occurred on closing the result set", e); 509 } 510 511 try { 512 if (stmt != null) { 513 stmt.close(); 514 } 515 } catch (final SQLException e) { 516 getLogger().error("An error occurred on closing the statement", e); 517 } 518 519 try { 520 if (conn != null) { 521 conn.close(); 522 } 523 } catch (final SQLException e) { 524 getLogger().error("An error occurred on closing the connection", e); 525 } 526 } 527 528 /** 529 * Extracts the value of a property from the given result set. The passed in {@code ResultSet} was created by a SELECT 530 * statement on the underlying database table. This implementation reads the value of the column determined by the 531 * {@code valueColumn} property. Normally the contained value is directly returned. However, if it is of type 532 * {@code CLOB}, text is extracted as string. 533 * 534 * @param rs the current {@code ResultSet} 535 * @return the value of the property column 536 * @throws SQLException if an error occurs 537 */ 538 protected Object extractPropertyValue(final ResultSet rs) throws SQLException { 539 Object value = rs.getObject(valueColumn); 540 if (value instanceof Clob) { 541 value = convertClob((Clob) value); 542 } 543 return value; 544 } 545 546 /** 547 * Converts a CLOB to a string. 548 * 549 * @param clob the CLOB to be converted 550 * @return the extracted string value 551 * @throws SQLException if an error occurs 552 */ 553 private static Object convertClob(final Clob clob) throws SQLException { 554 final int len = (int) clob.length(); 555 return len > 0 ? clob.getSubString(1, len) : StringUtils.EMPTY; 556 } 557 558 /** 559 * An internally used helper class for simplifying database access through plain JDBC. This class provides a simple 560 * framework for creating and executing a JDBC statement. It especially takes care of proper handling of JDBC resources 561 * even in case of an error. 562 * 563 * @param <T> the type of the results produced by a JDBC operation 564 */ 565 private abstract class JdbcOperation<T> { 566 /** Stores the connection. */ 567 private Connection conn; 568 569 /** Stores the statement. */ 570 private PreparedStatement pstmt; 571 572 /** Stores the result set. */ 573 private ResultSet resultSet; 574 575 /** The type of the event to send in case of an error. */ 576 private final EventType<? extends ConfigurationErrorEvent> errorEventType; 577 578 /** The type of the operation which caused an error. */ 579 private final EventType<?> operationEventType; 580 581 /** The property configurationName for an error event. */ 582 private final String errorPropertyName; 583 584 /** The property value for an error event. */ 585 private final Object errorPropertyValue; 586 587 /** 588 * Creates a new instance of {@code JdbcOperation} and initializes the properties related to the error event. 589 * 590 * @param errEvType the type of the error event 591 * @param opType the operation event type 592 * @param errPropName the property configurationName for the error event 593 * @param errPropVal the property value for the error event 594 */ 595 protected JdbcOperation(final EventType<? extends ConfigurationErrorEvent> errEvType, final EventType<?> opType, final String errPropName, 596 final Object errPropVal) { 597 errorEventType = errEvType; 598 operationEventType = opType; 599 errorPropertyName = errPropName; 600 errorPropertyValue = errPropVal; 601 } 602 603 /** 604 * Executes this operation. This method obtains a database connection and then delegates to {@code performOperation()}. 605 * Afterwards it performs the necessary clean up. Exceptions that are thrown during the JDBC operation are caught and 606 * transformed into configuration error events. 607 * 608 * @return the result of the operation 609 */ 610 public T execute() { 611 T result = null; 612 613 try { 614 conn = getDatasource().getConnection(); 615 result = performOperation(); 616 617 if (isAutoCommit()) { 618 conn.commit(); 619 } 620 } catch (final SQLException e) { 621 fireError(errorEventType, operationEventType, errorPropertyName, errorPropertyValue, e); 622 } finally { 623 close(conn, pstmt, resultSet); 624 } 625 626 return result; 627 } 628 629 /** 630 * Gets the current connection. This method can be called while {@code execute()} is running. It returns <b>null</b> 631 * otherwise. 632 * 633 * @return the current connection 634 */ 635 protected Connection getConnection() { 636 return conn; 637 } 638 639 /** 640 * Creates a {@code PreparedStatement} object for executing the specified SQL statement. 641 * 642 * @param sql the statement to be executed 643 * @param nameCol a flag whether the configurationName column should be taken into account 644 * @return the prepared statement object 645 * @throws SQLException if an SQL error occurs 646 */ 647 protected PreparedStatement createStatement(final String sql, final boolean nameCol) throws SQLException { 648 final String statement; 649 if (nameCol && configurationNameColumn != null) { 650 final StringBuilder buf = new StringBuilder(sql); 651 buf.append(" AND ").append(configurationNameColumn).append("=?"); 652 statement = buf.toString(); 653 } else { 654 statement = sql; 655 } 656 657 pstmt = getConnection().prepareStatement(statement); 658 return pstmt; 659 } 660 661 /** 662 * Creates an initializes a {@code PreparedStatement} object for executing an SQL statement. This method first calls 663 * {@code createStatement()} for creating the statement and then initializes the statement's parameters. 664 * 665 * @param sql the statement to be executed 666 * @param nameCol a flag whether the configurationName column should be taken into account 667 * @param params the parameters for the statement 668 * @return the initialized statement object 669 * @throws SQLException if an SQL error occurs 670 */ 671 protected PreparedStatement initStatement(final String sql, final boolean nameCol, final Object... params) throws SQLException { 672 final PreparedStatement ps = createStatement(sql, nameCol); 673 674 int idx = 1; 675 for (final Object param : params) { 676 ps.setObject(idx++, param); 677 } 678 if (nameCol && configurationNameColumn != null) { 679 ps.setString(idx, configurationName); 680 } 681 682 return ps; 683 } 684 685 /** 686 * Creates a {@code PreparedStatement} for a query, initializes it and executes it. The resulting {@code ResultSet} is 687 * returned. 688 * 689 * @param sql the statement to be executed 690 * @param nameCol a flag whether the configurationName column should be taken into account 691 * @param params the parameters for the statement 692 * @return the {@code ResultSet} produced by the query 693 * @throws SQLException if an SQL error occurs 694 */ 695 protected ResultSet openResultSet(final String sql, final boolean nameCol, final Object... params) throws SQLException { 696 return resultSet = initStatement(sql, nameCol, params).executeQuery(); 697 } 698 699 /** 700 * Performs the JDBC operation. This method is called by {@code execute()} after this object has been fully initialized. 701 * Here the actual JDBC logic has to be placed. 702 * 703 * @return the result of the operation 704 * @throws SQLException if an SQL error occurs 705 */ 706 protected abstract T performOperation() throws SQLException; 707 } 708}