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