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