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