/*
 * 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 javax.sql.DataSource;
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 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.
 The recommended way to create an instance of DatabaseConfiguration is to use a configuration builder. 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 database() method of the Parameters class. See the examples below for more details. 

Example 1 - One configuration per table

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");


Example 2 - Multiple configurations per table

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");
 The configuration can be instructed to perform commits after database updates. This is achieved by setting the commits parameter of the constructors to true. If commits should not be performed (which is the default behavior), it should be ensured that the connections returned by the DataSource are in auto-commit mode. 
Note: Like JDBC itself, protection against SQL injection is left to the user.
Since:
1.0/**
 * 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&lt;DatabaseConfiguration&gt; builder =
 *     new BasicConfigurationBuilder&lt;DatabaseConfiguration&gt;(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&lt;DatabaseConfiguration&gt; builder =
 *     new BasicConfigurationBuilder&lt;DatabaseConfiguration&gt;(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.
 *
 * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
 * @since 1.0
 *
 */
public class DatabaseConfiguration extends AbstractConfiguration
{
    
Constant for the statement used by getProperty./** 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./** 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./** 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./** 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./** Constant for the statement used by getKeys.*/
    private static final String SQL_GET_KEYS = "SELECT DISTINCT %s FROM %s WHERE 1 = 1";

    
The data source to connect to the database. /** The data source to connect to the database. */
    private DataSource dataSource;

    
The configurationName of the table containing the configurations. /** The configurationName of the table containing the configurations. */
    private String table;

    
The column containing the configurationName of the configuration. /** The column containing the configurationName of the configuration. */
    private String configurationNameColumn;

    
The column containing the keys. /** The column containing the keys. */
    private String keyColumn;

    
The column containing the values. /** The column containing the values. */
    private String valueColumn;

    
The configurationName of the configuration. /** The configurationName of the configuration. */
    private String configurationName;

    
A flag whether commits should be performed by this configuration. /** A flag whether commits should be performed by this configuration. */
    private boolean autoCommit;

    
Creates a new instance of DatabaseConfiguration. /**
     * Creates a new instance of {@code DatabaseConfiguration}.
     */
    public DatabaseConfiguration()
    {
        initLogger(new ConfigurationLogger(DatabaseConfiguration.class));
        addErrorLogListener();
    }

    
Returns the DataSource for obtaining database connections. 
Returns:
the DataSource/**
     * Returns the {@code DataSource} for obtaining database connections.
     *
     * @return the {@code DataSource}
     */
    public DataSource getDataSource()
    {
        return dataSource;
    }

    
Sets the DataSource for obtaining database connections. 
Params:
dataSource – the DataSource/**
     * Sets the {@code DataSource} for obtaining database connections.
     *
     * @param dataSource the {@code DataSource}
     */
    public void setDataSource(final DataSource dataSource)
    {
        this.dataSource = dataSource;
    }

    
Returns the name of the table containing configuration data.
Returns:
the name of the table to be queried/**
     * Returns the name of the table containing configuration data.
     *
     * @return the name of the table to be queried
     */
    public String getTable()
    {
        return table;
    }

    
Sets the name of the table containing configuration data.
Params:
table – the table name/**
     * Sets the name of the table containing configuration data.
     *
     * @param table the table name
     */
    public void setTable(final String table)
    {
        this.table = table;
    }

    
Returns the name of the table column with the configuration name.
Returns:
the name of the configuration name column/**
     * Returns the name of the table column with the configuration name.
     *
     * @return the name of the configuration name column
     */
    public String getConfigurationNameColumn()
    {
        return configurationNameColumn;
    }

    
Sets the name of the table column with the configuration name.
Params:
configurationNameColumn – the name of the column with the
       configuration name/**
     * 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;
    }

    
Returns the name of the column containing the configuration keys.
Returns:
the name of the key column/**
     * Returns the name of the column containing the configuration keys.
     *
     * @return the name of the key column
     */
    public String getKeyColumn()
    {
        return keyColumn;
    }

    
Sets the name of the column containing the configuration keys.
Params:
keyColumn – the name of the key column/**
     * 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;
    }

    
Returns the name of the column containing the configuration values.
Returns:
the name of the value column/**
     * Returns the name of the column containing the configuration values.
     *
     * @return the name of the value column
     */
    public String getValueColumn()
    {
        return valueColumn;
    }

    
Sets the name of the column containing the configuration values.
Params:
valueColumn – the name of the value column/**
     * 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;
    }

    
Returns the name of this configuration instance.
Returns:
the name of this configuration/**
     * Returns the name of this configuration instance.
     *
     * @return the name of this configuration
     */
    public String getConfigurationName()
    {
        return configurationName;
    }

    
Sets the name of this configuration instance.
Params:
configurationName – the name of this configuration/**
     * Sets the name of this configuration instance.
     *
     * @param configurationName the name of this configuration
     */
    public void setConfigurationName(final String configurationName)
    {
        this.configurationName = configurationName;
    }

    
Returns a flag whether this configuration performs commits after database
updates.
Returns:
a flag whether commits are performed/**
     * Returns a flag whether this configuration performs commits after database
     * updates.
     *
     * @return a flag whether commits are performed
     */
    public boolean isAutoCommit()
    {
        return autoCommit;
    }

    
Sets the auto commit flag. If set to true, this configuration
performs a commit after each database update.
Params:
autoCommit – the auto commit flag/**
     * 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;
    }

    
Returns the value of the specified property. If this causes a database error, an error event will be generated of type READ with the causing exception. The event's propertyName is set to the passed in property key, the propertyValue is undefined. 
Params:
key – the key of the desired property
Returns:
the value of this property/**
     * Returns 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 JdbcOperation<Object> op =
                new JdbcOperation<Object>(ConfigurationErrorEvent.READ,
                        ConfigurationErrorEvent.READ, key, null)
        {
            @Override
            protected Object performOperation() throws SQLException
            {
                final List<Object> results = new ArrayList<>();
                try (final ResultSet rs =
                        openResultSet(String.format(SQL_GET_PROPERTY,
                                table, keyColumn), true, key))
                {
                    while (rs.next())
                    {
                        final Object value = extractPropertyValue(rs);
                        // Split value if it contains the list delimiter
                        for (final Object o : getListDelimiterHandler().parse(value))
                        {
                            results.add(o);
                        }
                    }
                }
                if (!results.isEmpty())
                {
                    return results.size() > 1 ? results : results
                            .get(0);
                }
                return null;
            }
        };

        return op.execute();
    }

    
Adds a property to this configuration. If this causes a database error, an error event will be generated of type ADD_PROPERTY with the causing exception. The event's propertyName is set to the passed in property key, the propertyValue points to the passed in value. 
Params:
key – the property key
obj – the value of the property to add/**
     * 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 JdbcOperation<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 (final 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 getProperty() takes care about delimiters. So list delimiters are fully supported by DatabaseConfiguration, but internally treated a bit differently. 
Params:
key – the key of the new property
value – the value to be added/**
     * 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);
        }
    }

    
Checks if this configuration is empty. If this causes a database error, an error event will be generated of type READ with the causing exception. Both the event's propertyName and propertyValue will be undefined. 
Returns:
a flag whether this configuration is empty./**
     * 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 JdbcOperation<Integer> op =
                new JdbcOperation<Integer>(ConfigurationErrorEvent.READ,
                        ConfigurationErrorEvent.READ, null, null)
        {
            @Override
            protected Integer performOperation() throws SQLException
            {
                try (final 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;
    }

    
Checks whether this configuration contains the specified key. If this causes a database error, an error event will be generated of type READ with the causing exception. The event's propertyName will be set to the passed in key, the propertyValue will be undefined. 
Params:
key – the key to be checked
Returns:
a flag whether this key is defined/**
     * 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 JdbcOperation<Boolean> op =
                new JdbcOperation<Boolean>(ConfigurationErrorEvent.READ,
                        ConfigurationErrorEvent.READ, key, null)
        {
            @Override
            protected Boolean performOperation() throws SQLException
            {
                try (final 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();
    }

    
Removes the specified value from this configuration. If this causes a database error, an error event will be generated of type CLEAR_PROPERTY with the causing exception. The event's propertyName will be set to the passed in key, the propertyValue will be undefined. 
Params:
key – the key of the property to be removed/**
     * 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 JdbcOperation<Void>(ConfigurationErrorEvent.WRITE,
                ConfigurationEvent.CLEAR_PROPERTY, key, null)
        {
            @Override
            protected Void performOperation() throws SQLException
            {
                try (final PreparedStatement ps = initStatement(String.format(
                        SQL_CLEAR_PROPERTY, table, keyColumn), true, key))
                {
                    ps.executeUpdate();
                    return null;
                }
            }
        }
        .execute();
    }

    
Removes all entries from this configuration. If this causes a database error, an error event will be generated of type CLEAR with the causing exception. Both the event's propertyName and the propertyValue will be undefined. /**
     * 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 JdbcOperation<Void>(ConfigurationErrorEvent.WRITE,
                ConfigurationEvent.CLEAR, null, null)
        {
            @Override
            protected Void performOperation() throws SQLException
            {
                initStatement(String.format(SQL_CLEAR,
                        table), true).executeUpdate();
                return null;
            }
        }
        .execute();
    }

    
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 READ with the causing exception. Both the event's propertyName and the propertyValue will be undefined. 
Returns:
an iterator with the contained keys (an empty iterator in case
of an error)/**
     * 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 JdbcOperation<Collection<String>>(ConfigurationErrorEvent.READ,
                ConfigurationErrorEvent.READ, null, null)
        {
            @Override
            protected Collection<String> performOperation() throws SQLException
            {
                try (final 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();
    }

    
Returns the used DataSource object. 
Returns:
the data source
Since:
1.4/**
     * Returns the used {@code DataSource} object.
     *
     * @return the data source
     * @since 1.4
     */
    public DataSource getDatasource()
    {
        return dataSource;
    }

    
Close the specified database objects.
Avoid closing if null and hide any SQLExceptions that occur.
Params:
conn – The database connection to close
stmt – The statement to close
rs – the result set to close/**
     * 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 occured on closing the statement", e);
        }

        try
        {
            if (conn != null)
            {
                conn.close();
            }
        }
        catch (final SQLException e)
        {
            getLogger().error("An error occured on closing the connection", e);
        }
    }

    
Extracts the value of a property from the given result set. The passed in ResultSet was created by a SELECT statement on the underlying database table. This implementation reads the value of the column determined by the valueColumn property. Normally the contained value is directly returned. However, if it is of type CLOB, text is extracted as string. 
Params:
rs – the current ResultSet
Throws:
SQLException – if an error occurs
Returns:
the value of the property column/**
     * 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;
    }

    
Converts a CLOB to a string.
Params:
clob – the CLOB to be converted
Throws:
SQLException – if an error occurs
Returns:
the extracted string value/**
     * 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;
    }

    
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.
Type parameters:
<T> – the type of the results produced by a JDBC operation/**
     * 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 JdbcOperation<T>
    {
        
Stores the connection. /** Stores the connection. */
        private Connection conn;

        
Stores the statement. /** Stores the statement. */
        private PreparedStatement pstmt;

        
Stores the result set. /** Stores the result set. */
        private ResultSet resultSet;

        
The type of the event to send in case of an error. /** 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. /** The type of the operation which caused an error. */
        private final EventType<?> operationEventType;

        
The property configurationName for an error event. /** The property configurationName for an error event. */
        private final String errorPropertyName;

        
The property value for an error event. /** The property value for an error event. */
        private final Object errorPropertyValue;

        
Creates a new instance of JdbcOperation and initializes the properties related to the error event. 
Params:
errEvType – the type of the error event
opType – the operation event type
errPropName – the property configurationName for the error event
errPropVal – the property value for the error event/**
         * 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 JdbcOperation(
                final EventType<? extends ConfigurationErrorEvent> errEvType,
                final EventType<?> opType, final String errPropName, final Object errPropVal)
        {
            errorEventType = errEvType;
            operationEventType = opType;
            errorPropertyName = errPropName;
            errorPropertyValue = errPropVal;
        }

        
Executes this operation. This method obtains a database connection and then delegates to performOperation(). Afterwards it performs the necessary clean up. Exceptions that are thrown during the JDBC operation are caught and transformed into configuration error events. 
Returns:
the result of the operation/**
         * 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;

            try
            {
                conn = getDatasource().getConnection();
                result = performOperation();

                if (isAutoCommit())
                {
                    conn.commit();
                }
            }
            catch (final SQLException e)
            {
                fireError(errorEventType, operationEventType, errorPropertyName,
                        errorPropertyValue, e);
            }
            finally
            {
                close(conn, pstmt, resultSet);
            }

            return result;
        }

        
Returns the current connection. This method can be called while execute() is running. It returns null otherwise.
Returns:
the current connection/**
         * Returns 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 conn;
        }

        
Creates a PreparedStatement object for executing the specified SQL statement. 
Params:
sql – the statement to be executed
nameCol – a flag whether the configurationName column should be taken into
       account
Throws:
SQLException – if an SQL error occurs
Returns:
the prepared statement object/**
         * 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
        {
            String statement;
            if (nameCol && configurationNameColumn != null)
            {
                final StringBuilder buf = new StringBuilder(sql);
                buf.append(" AND ").append(configurationNameColumn).append("=?");
                statement = buf.toString();
            }
            else
            {
                statement = sql;
            }

            pstmt = getConnection().prepareStatement(statement);
            return pstmt;
        }

        
Creates an initializes a PreparedStatement object for executing an SQL statement. This method first calls createStatement() for creating the statement and then initializes the statement's parameters. 
Params:
sql – the statement to be executed
nameCol – a flag whether the configurationName column should be taken into
       account
params – the parameters for the statement
Throws:
SQLException – if an SQL error occurs
Returns:
the initialized statement object/**
         * 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 PreparedStatement for a query, initializes it and executes it. The resulting ResultSet is returned. 
Params:
sql – the statement to be executed
nameCol – a flag whether the configurationName column should be taken into
       account
params – the parameters for the statement
Throws:
SQLException – if an SQL error occurs
Returns:
the ResultSet produced by the query/**
         * 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 execute() after this object has been fully initialized. Here the actual JDBC logic has to be placed. 
Throws:
SQLException – if an SQL error occurs
Returns:
the result of the operation/**
         * 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;
    }
}