DatabaseConfiguration.java
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.commons.configuration2;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.configuration2.convert.DisabledListDelimiterHandler;
import org.apache.commons.configuration2.convert.ListDelimiterHandler;
import org.apache.commons.configuration2.event.ConfigurationErrorEvent;
import org.apache.commons.configuration2.event.ConfigurationEvent;
import org.apache.commons.configuration2.event.EventType;
import org.apache.commons.configuration2.io.ConfigurationLogger;
import org.apache.commons.lang3.StringUtils;
/**
* Configuration stored in a database. The properties are retrieved from a table containing at least one column for the
* keys, and one column for the values. It's possible to store several configurations in the same table by adding a
* column containing the name of the configuration. The name of the table and the columns have to be specified using the
* corresponding properties.
* <p>
* The recommended way to create an instance of {@code DatabaseConfiguration} is to use a <em>configuration
* builder</em>. The builder is configured with a special parameters object defining the database structures used by the
* configuration. Such an object can be created using the {@code database()} method of the {@code Parameters} class. See
* the examples below for more details.
* </p>
*
* <p>
* <strong>Example 1 - One configuration per table</strong>
* </p>
*
* <pre>
* CREATE TABLE myconfig (
* `key` VARCHAR NOT NULL PRIMARY KEY,
* `value` VARCHAR
* );
*
* INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
*
* BasicConfigurationBuilder<DatabaseConfiguration> builder =
* new BasicConfigurationBuilder<DatabaseConfiguration>(DatabaseConfiguration.class);
* builder.configure(
* Parameters.database()
* .setDataSource(dataSource)
* .setTable("myconfig")
* .setKeyColumn("key")
* .setValueColumn("value")
* );
* Configuration config = builder.getConfiguration();
* String value = config.getString("foo");
* </pre>
*
* <p>
* <strong>Example 2 - Multiple configurations per table</strong>
* </p>
*
* <pre>
* CREATE TABLE myconfigs (
* `name` VARCHAR NOT NULL,
* `key` VARCHAR NOT NULL,
* `value` VARCHAR,
* CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
* );
*
* INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
* INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
*
* BasicConfigurationBuilder<DatabaseConfiguration> builder =
* new BasicConfigurationBuilder<DatabaseConfiguration>(DatabaseConfiguration.class);
* builder.configure(
* Parameters.database()
* .setDataSource(dataSource)
* .setTable("myconfigs")
* .setKeyColumn("key")
* .setValueColumn("value")
* .setConfigurationNameColumn("name")
* .setConfigurationName("config1")
* );
* Configuration config1 = new DatabaseConfiguration(dataSource, "myconfigs", "name", "key", "value", "config1");
* String value1 = conf.getString("key1");
* </pre>
*
* The configuration can be instructed to perform commits after database updates. This is achieved by setting the
* {@code commits} parameter of the constructors to <b>true</b>. If commits should not be performed (which is the
* default behavior), it should be ensured that the connections returned by the {@code DataSource} are in auto-commit
* mode.
* <p>
* <strong>Note: Like JDBC itself, protection against SQL injection is left to the user.</strong>
* </p>
*
* @since 1.0
*/
public class DatabaseConfiguration extends AbstractConfiguration {
/**
* An internally used helper class for simplifying database access through plain JDBC. This class provides a simple
* framework for creating and executing a JDBC statement. It especially takes care of proper handling of JDBC resources
* even in case of an error.
*
* @param <T> the type of the results produced by a JDBC operation
*/
private abstract class AbstractJdbcOperation<T> {
/** Stores the connection. */
private Connection connection;
/** Stores the statement. */
private PreparedStatement preparedStatement;
/** Stores the result set. */
private ResultSet resultSet;
/** The type of the event to send in case of an error. */
private final EventType<? extends ConfigurationErrorEvent> errorEventType;
/** The type of the operation which caused an error. */
private final EventType<?> operationEventType;
/** The property configurationName for an error event. */
private final String errorPropertyName;
/** The property value for an error event. */
private final Object errorPropertyValue;
/**
* Creates a new instance of {@code JdbcOperation} and initializes the properties related to the error event.
*
* @param errEvType the type of the error event
* @param opType the operation event type
* @param errPropName the property configurationName for the error event
* @param errPropVal the property value for the error event
*/
protected AbstractJdbcOperation(final EventType<? extends ConfigurationErrorEvent> errEvType, final EventType<?> opType, final String errPropName,
final Object errPropVal) {
errorEventType = errEvType;
operationEventType = opType;
errorPropertyName = errPropName;
errorPropertyValue = errPropVal;
}
/**
* Creates a {@code PreparedStatement} object for executing the specified SQL statement.
*
* @param sql the statement to be executed
* @param nameCol a flag whether the configurationName column should be taken into account
* @return the prepared statement object
* @throws SQLException if an SQL error occurs
*/
protected PreparedStatement createStatement(final String sql, final boolean nameCol) throws SQLException {
final String statement;
if (nameCol && configurationNameColumn != null) {
final StringBuilder buf = new StringBuilder(sql);
buf.append(" AND ").append(configurationNameColumn).append("=?");
statement = buf.toString();
} else {
statement = sql;
}
preparedStatement = getConnection().prepareStatement(statement);
return preparedStatement;
}
/**
* Executes this operation. This method obtains a database connection and then delegates to {@code performOperation()}.
* Afterwards it performs the necessary clean up. Exceptions that are thrown during the JDBC operation are caught and
* transformed into configuration error events.
*
* @return the result of the operation
*/
public T execute() {
T result = null;
if (getDataSource() != null) {
try {
connection = getDataSource().getConnection();
result = performOperation();
if (isAutoCommit()) {
connection.commit();
}
} catch (final SQLException e) {
fireError(errorEventType, operationEventType, errorPropertyName, errorPropertyValue, e);
} finally {
close(connection, preparedStatement, resultSet);
}
}
return result;
}
/**
* Gets the current connection. This method can be called while {@code execute()} is running. It returns <b>null</b>
* otherwise.
*
* @return the current connection
*/
protected Connection getConnection() {
return connection;
}
/**
* Creates an initializes a {@code PreparedStatement} object for executing an SQL statement. This method first calls
* {@code createStatement()} for creating the statement and then initializes the statement's parameters.
*
* @param sql the statement to be executed
* @param nameCol a flag whether the configurationName column should be taken into account
* @param params the parameters for the statement
* @return the initialized statement object
* @throws SQLException if an SQL error occurs
*/
protected PreparedStatement initStatement(final String sql, final boolean nameCol, final Object... params) throws SQLException {
final PreparedStatement ps = createStatement(sql, nameCol);
int idx = 1;
for (final Object param : params) {
ps.setObject(idx++, param);
}
if (nameCol && configurationNameColumn != null) {
ps.setString(idx, configurationName);
}
return ps;
}
/**
* Creates a {@code PreparedStatement} for a query, initializes it and executes it. The resulting {@code ResultSet} is
* returned.
*
* @param sql the statement to be executed
* @param nameCol a flag whether the configurationName column should be taken into account
* @param params the parameters for the statement
* @return the {@code ResultSet} produced by the query
* @throws SQLException if an SQL error occurs
*/
protected ResultSet openResultSet(final String sql, final boolean nameCol, final Object... params) throws SQLException {
return resultSet = initStatement(sql, nameCol, params).executeQuery();
}
/**
* Performs the JDBC operation. This method is called by {@code execute()} after this object has been fully initialized.
* Here the actual JDBC logic has to be placed.
*
* @return the result of the operation
* @throws SQLException if an SQL error occurs
*/
protected abstract T performOperation() throws SQLException;
}
/** Constant for the statement used by getProperty. */
private static final String SQL_GET_PROPERTY = "SELECT * FROM %s WHERE %s =?";
/** Constant for the statement used by isEmpty. */
private static final String SQL_IS_EMPTY = "SELECT count(*) FROM %s WHERE 1 = 1";
/** Constant for the statement used by clearProperty. */
private static final String SQL_CLEAR_PROPERTY = "DELETE FROM %s WHERE %s =?";
/** Constant for the statement used by clear. */
private static final String SQL_CLEAR = "DELETE FROM %s WHERE 1 = 1";
/** Constant for the statement used by getKeys. */
private static final String SQL_GET_KEYS = "SELECT DISTINCT %s FROM %s WHERE 1 = 1";
/**
* Converts a CLOB to a string.
*
* @param clob the CLOB to be converted
* @return the extracted string value
* @throws SQLException if an error occurs
*/
private static Object convertClob(final Clob clob) throws SQLException {
final int len = (int) clob.length();
return len > 0 ? clob.getSubString(1, len) : StringUtils.EMPTY;
}
/** The data source to connect to the database. */
private DataSource dataSource;
/** The configurationName of the table containing the configurations. */
private String table;
/** The column containing the configurationName of the configuration. */
private String configurationNameColumn;
/** The column containing the keys. */
private String keyColumn;
/** The column containing the values. */
private String valueColumn;
/** The configurationName of the configuration. */
private String configurationName;
/** A flag whether commits should be performed by this configuration. */
private boolean autoCommit;
/**
* Creates a new instance of {@code DatabaseConfiguration}.
*/
public DatabaseConfiguration() {
initLogger(new ConfigurationLogger(DatabaseConfiguration.class));
addErrorLogListener();
}
/**
* Adds a property to this configuration. If this causes a database error, an error event will be generated of type
* {@code ADD_PROPERTY} with the causing exception. The event's {@code propertyName} is set to the passed in property
* key, the {@code propertyValue} points to the passed in value.
*
* @param key the property key
* @param obj the value of the property to add
*/
@Override
protected void addPropertyDirect(final String key, final Object obj) {
new AbstractJdbcOperation<Void>(ConfigurationErrorEvent.WRITE, ConfigurationEvent.ADD_PROPERTY, key, obj) {
@Override
protected Void performOperation() throws SQLException {
final StringBuilder query = new StringBuilder("INSERT INTO ");
query.append(table).append(" (");
query.append(keyColumn).append(", ");
query.append(valueColumn);
if (configurationNameColumn != null) {
query.append(", ").append(configurationNameColumn);
}
query.append(") VALUES (?, ?");
if (configurationNameColumn != null) {
query.append(", ?");
}
query.append(")");
try (PreparedStatement pstmt = initStatement(query.toString(), false, key, String.valueOf(obj))) {
if (configurationNameColumn != null) {
pstmt.setString(3, configurationName);
}
pstmt.executeUpdate();
return null;
}
}
}.execute();
}
/**
* Adds a property to this configuration. This implementation temporarily disables list delimiter parsing, so that even
* if the value contains the list delimiter, only a single record is written into the managed table. The implementation
* of {@code getProperty()} takes care about delimiters. So list delimiters are fully supported by
* {@code DatabaseConfiguration}, but internally treated a bit differently.
*
* @param key the key of the new property
* @param value the value to be added
*/
@Override
protected void addPropertyInternal(final String key, final Object value) {
final ListDelimiterHandler oldHandler = getListDelimiterHandler();
try {
// temporarily disable delimiter parsing
setListDelimiterHandler(DisabledListDelimiterHandler.INSTANCE);
super.addPropertyInternal(key, value);
} finally {
setListDelimiterHandler(oldHandler);
}
}
/**
* Removes all entries from this configuration. If this causes a database error, an error event will be generated of
* type {@code CLEAR} with the causing exception. Both the event's {@code propertyName} and the {@code propertyValue}
* will be undefined.
*/
@Override
protected void clearInternal() {
new AbstractJdbcOperation<Void>(ConfigurationErrorEvent.WRITE, ConfigurationEvent.CLEAR, null, null) {
@Override
protected Void performOperation() throws SQLException {
try (PreparedStatement statement = initStatement(String.format(SQL_CLEAR, table), true)) {
statement.executeUpdate();
}
return null;
}
}.execute();
}
/**
* Removes the specified value from this configuration. If this causes a database error, an error event will be
* generated of type {@code CLEAR_PROPERTY} with the causing exception. The event's {@code propertyName} will be set to
* the passed in key, the {@code propertyValue} will be undefined.
*
* @param key the key of the property to be removed
*/
@Override
protected void clearPropertyDirect(final String key) {
new AbstractJdbcOperation<Void>(ConfigurationErrorEvent.WRITE, ConfigurationEvent.CLEAR_PROPERTY, key, null) {
@Override
protected Void performOperation() throws SQLException {
try (PreparedStatement ps = initStatement(String.format(SQL_CLEAR_PROPERTY, table, keyColumn), true, key)) {
ps.executeUpdate();
return null;
}
}
}.execute();
}
/**
* Close the specified database objects. Avoid closing if null and hide any SQLExceptions that occur.
*
* @param conn The database connection to close
* @param stmt The statement to close
* @param rs the result set to close
*/
protected void close(final Connection conn, final Statement stmt, final ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (final SQLException e) {
getLogger().error("An error occurred on closing the result set", e);
}
try {
if (stmt != null) {
stmt.close();
}
} catch (final SQLException e) {
getLogger().error("An error occurred on closing the statement", e);
}
try {
if (conn != null) {
conn.close();
}
} catch (final SQLException e) {
getLogger().error("An error occurred on closing the connection", e);
}
}
/**
* Checks whether this configuration contains the specified key. If this causes a database error, an error event will be
* generated of type {@code READ} with the causing exception. The event's {@code propertyName} will be set to the passed
* in key, the {@code propertyValue} will be undefined.
*
* @param key the key to be checked
* @return a flag whether this key is defined
*/
@Override
protected boolean containsKeyInternal(final String key) {
final AbstractJdbcOperation<Boolean> op = new AbstractJdbcOperation<Boolean>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, key, null) {
@Override
protected Boolean performOperation() throws SQLException {
try (ResultSet rs = openResultSet(String.format(SQL_GET_PROPERTY, table, keyColumn), true, key)) {
return rs.next();
}
}
};
final Boolean result = op.execute();
return result != null && result.booleanValue();
}
/**
* Tests whether this configuration contains one or more matches to this value. This operation stops at first
* match but may be more expensive than the containsKey method.
* @since 2.11.0
*/
@Override
protected boolean containsValueInternal(final Object value) {
final AbstractJdbcOperation<Boolean> op = new AbstractJdbcOperation<Boolean>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, null, value) {
@Override
protected Boolean performOperation() throws SQLException {
try (ResultSet rs = openResultSet(String.format(SQL_GET_PROPERTY, table, valueColumn), false, value)) {
return rs.next();
}
}
};
final Boolean result = op.execute();
return result != null && result.booleanValue();
}
/**
* Extracts the value of a property from the given result set. The passed in {@code ResultSet} was created by a SELECT
* statement on the underlying database table. This implementation reads the value of the column determined by the
* {@code valueColumn} property. Normally the contained value is directly returned. However, if it is of type
* {@code CLOB}, text is extracted as string.
*
* @param rs the current {@code ResultSet}
* @return the value of the property column
* @throws SQLException if an error occurs
*/
protected Object extractPropertyValue(final ResultSet rs) throws SQLException {
Object value = rs.getObject(valueColumn);
if (value instanceof Clob) {
value = convertClob((Clob) value);
}
return value;
}
/**
* Gets the name of this configuration instance.
*
* @return the name of this configuration
*/
public String getConfigurationName() {
return configurationName;
}
/**
* Gets the name of the table column with the configuration name.
*
* @return the name of the configuration name column
*/
public String getConfigurationNameColumn() {
return configurationNameColumn;
}
/**
* Gets the used {@code DataSource} object.
*
* @return the data source
* @since 1.4
* @deprecated Use {@link #getDataSource()}
*/
@Deprecated
public DataSource getDatasource() {
return dataSource;
}
/**
* Gets the {@code DataSource} for obtaining database connections.
*
* @return the {@code DataSource}
*/
public DataSource getDataSource() {
return dataSource;
}
/**
* Gets the name of the column containing the configuration keys.
*
* @return the name of the key column
*/
public String getKeyColumn() {
return keyColumn;
}
/**
* Returns an iterator with the names of all properties contained in this configuration. If this causes a database
* error, an error event will be generated of type {@code READ} with the causing exception. Both the event's
* {@code propertyName} and the {@code propertyValue} will be undefined.
*
* @return an iterator with the contained keys (an empty iterator in case of an error)
*/
@Override
protected Iterator<String> getKeysInternal() {
final Collection<String> keys = new ArrayList<>();
new AbstractJdbcOperation<Collection<String>>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, null, null) {
@Override
protected Collection<String> performOperation() throws SQLException {
try (ResultSet rs = openResultSet(String.format(SQL_GET_KEYS, keyColumn, table), true)) {
while (rs.next()) {
keys.add(rs.getString(1));
}
return keys;
}
}
}.execute();
return keys.iterator();
}
/**
* Gets the value of the specified property. If this causes a database error, an error event will be generated of
* type {@code READ} with the causing exception. The event's {@code propertyName} is set to the passed in property key,
* the {@code propertyValue} is undefined.
*
* @param key the key of the desired property
* @return the value of this property
*/
@Override
protected Object getPropertyInternal(final String key) {
final AbstractJdbcOperation<Object> op = new AbstractJdbcOperation<Object>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, key, null) {
@Override
protected Object performOperation() throws SQLException {
final List<Object> results = new ArrayList<>();
try (ResultSet rs = openResultSet(String.format(SQL_GET_PROPERTY, table, keyColumn), true, key)) {
while (rs.next()) {
// Split value if it contains the list delimiter
getListDelimiterHandler().parse(extractPropertyValue(rs)).forEach(results::add);
}
}
if (!results.isEmpty()) {
return results.size() > 1 ? results : results.get(0);
}
return null;
}
};
return op.execute();
}
/**
* Gets the name of the table containing configuration data.
*
* @return the name of the table to be queried
*/
public String getTable() {
return table;
}
/**
* Gets the name of the column containing the configuration values.
*
* @return the name of the value column
*/
public String getValueColumn() {
return valueColumn;
}
/**
* Returns a flag whether this configuration performs commits after database updates.
*
* @return a flag whether commits are performed
*/
public boolean isAutoCommit() {
return autoCommit;
}
/**
* Checks if this configuration is empty. If this causes a database error, an error event will be generated of type
* {@code READ} with the causing exception. Both the event's {@code propertyName} and {@code propertyValue} will be
* undefined.
*
* @return a flag whether this configuration is empty.
*/
@Override
protected boolean isEmptyInternal() {
final AbstractJdbcOperation<Integer> op = new AbstractJdbcOperation<Integer>(ConfigurationErrorEvent.READ, ConfigurationErrorEvent.READ, null, null) {
@Override
protected Integer performOperation() throws SQLException {
try (ResultSet rs = openResultSet(String.format(SQL_IS_EMPTY, table), true)) {
return rs.next() ? Integer.valueOf(rs.getInt(1)) : null;
}
}
};
final Integer count = op.execute();
return count == null || count.intValue() == 0;
}
/**
* Sets the auto commit flag. If set to <b>true</b>, this configuration performs a commit after each database update.
*
* @param autoCommit the auto commit flag
*/
public void setAutoCommit(final boolean autoCommit) {
this.autoCommit = autoCommit;
}
/**
* Sets the name of this configuration instance.
*
* @param configurationName the name of this configuration
*/
public void setConfigurationName(final String configurationName) {
this.configurationName = configurationName;
}
/**
* Sets the name of the table column with the configuration name.
*
* @param configurationNameColumn the name of the column with the configuration name
*/
public void setConfigurationNameColumn(final String configurationNameColumn) {
this.configurationNameColumn = configurationNameColumn;
}
/**
* Sets the {@code DataSource} for obtaining database connections.
*
* @param dataSource the {@code DataSource}
*/
public void setDataSource(final DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* Sets the name of the column containing the configuration keys.
*
* @param keyColumn the name of the key column
*/
public void setKeyColumn(final String keyColumn) {
this.keyColumn = keyColumn;
}
/**
* Sets the name of the table containing configuration data.
*
* @param table the table name
*/
public void setTable(final String table) {
this.table = table;
}
/**
* Sets the name of the column containing the configuration values.
*
* @param valueColumn the name of the value column
*/
public void setValueColumn(final String valueColumn) {
this.valueColumn = valueColumn;
}
}