    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.dbcp2;
    019
    020import java.sql.CallableStatement;
    021import java.sql.Connection;
    022import java.sql.PreparedStatement;
    023import java.sql.SQLException;
    024import java.util.NoSuchElementException;
    025
    026import org.apache.commons.pool2.KeyedObjectPool;
    027import org.apache.commons.pool2.KeyedPooledObjectFactory;
    028import org.apache.commons.pool2.PooledObject;
    029import org.apache.commons.pool2.impl.DefaultPooledObject;
    030
    031/**
    032 * A {@link DelegatingConnection} that pools {@link PreparedStatement}s.
    033 * <p>
    034 * The {@link #prepareStatement} and {@link #prepareCall} methods, rather than creating a new PreparedStatement each
    035 * time, may actually pull the statement from a pool of unused statements. The {@link PreparedStatement#close} method of
    036 * the returned statement doesn't actually close the statement, but rather returns it to the pool. (See
    037 * {@link PoolablePreparedStatement}, {@link PoolableCallableStatement}.)
    038 * </p>
    039 *
    040 * @see PoolablePreparedStatement
    041 * @since 2.0
    042 */
    043public class PoolingConnection extends DelegatingConnection<Connection>
    044        implements KeyedPooledObjectFactory<PStmtKey, DelegatingPreparedStatement> {
    045
    046    /**
    047     * Statement types.
    048     *
    049     * @since 2.0 protected enum.
    050     * @since 2.4.0 public enum.
    051     */
    052    public enum StatementType {
    053
    054        /**
    055         * Callable statement.
    056         */
    057        CALLABLE_STATEMENT,
    058
    059        /**
    060         * Prepared statement.
    061         */
    062        PREPARED_STATEMENT
    063    }
    064
    065    /** Pool of {@link PreparedStatement}s. and {@link CallableStatement}s */
    066    private KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> pstmtPool;
    067
    068    private boolean clearStatementPoolOnReturn = false;
    069
    070    /**
    071     * Constructor.
    072     *
    073     * @param connection
    074     *            the underlying {@link Connection}.
    075     */
    076    public PoolingConnection(final Connection connection) {
    077        super(connection);
    078    }
    079
    080    /**
    081     * {@link KeyedPooledObjectFactory} method for activating pooled statements.
    082     *
    083     * @param key
    084     *            ignored
    085     * @param pooledObject
    086     *            wrapped pooled statement to be activated
    087     */
    088    @Override
    089    public void activateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject)
    090            throws Exception {
    091        pooledObject.getObject().activate();
    092    }
    093
    094    /**
    095     * Closes and frees all {@link PreparedStatement}s or {@link CallableStatement}s from the pool, and close the
    096     * underlying connection.
    097     */
    098    @Override
    099    public synchronized void close() throws SQLException {
    100        try {
    101            if (null != pstmtPool) {
    102                final KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> oldpool = pstmtPool;
    103                pstmtPool = null;
    104                try {
    105                    oldpool.close();
    106                } catch (final RuntimeException e) {
    107                    throw e;
    108                } catch (final Exception e) {
    109                    throw new SQLException("Cannot close connection", e);
    110                }
    111            }
    112        } finally {
    113            try {
    114                getDelegateInternal().close();
    115            } finally {
    116                setClosedInternal(true);
    117            }
    118        }
    119    }
    120
    121    /**
    122     * Notification from {@link PoolableConnection} that we returned to the pool.
    123     *
    124     * @throws SQLException when <code>clearStatementPoolOnReturn</code> is true and the statement pool could not be
    125     *                      cleared
    126     * @since 2.8.0
    127     */
    128    public void connectionReturnedToPool() throws SQLException {
    129        if (pstmtPool != null && clearStatementPoolOnReturn) {
    130            try {
    131                pstmtPool.clear();
    132            } catch (Exception e) {
    133                throw new SQLException("Error clearing statement pool", e);
    134            }
    135        }
    136    }
    137
    138    /**
    139     * Creates a PStmtKey for the given arguments.
    140     *
    141     * @param sql
    142     *            the SQL string used to define the statement
    143     *
    144     * @return the PStmtKey created for the given arguments.
    145     */
    146    protected PStmtKey createKey(final String sql) {
    147        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull());
    148    }
    149
    150    /**
    151     * Creates a PStmtKey for the given arguments.
    152     *
    153     * @param sql
    154     *            the SQL string used to define the statement
    155     * @param columnIndexes
    156     *            An array of column indexes indicating the columns that should be returned from the inserted row or
    157     *            rows.
    158     *
    159     * @return the PStmtKey created for the given arguments.
    160     */
    161    protected PStmtKey createKey(final String sql, final int columnIndexes[]) {
    162        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), columnIndexes);
    163    }
    164
    165    /**
    166     * Creates a PStmtKey for the given arguments.
    167     *
    168     * @param sql
    169     *            the SQL string used to define the statement
    170     * @param autoGeneratedKeys
    171     *            A flag indicating whether auto-generated keys should be returned; one of
    172     *            <code>Statement.RETURN_GENERATED_KEYS</code> or <code>Statement.NO_GENERATED_KEYS</code>.
    173     *
    174     * @return the PStmtKey created for the given arguments.
    175     */
    176    protected PStmtKey createKey(final String sql, final int autoGeneratedKeys) {
    177        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), autoGeneratedKeys);
    178    }
    179
    180    /**
    181     * Creates a PStmtKey for the given arguments.
    182     *
    183     * @param sql
    184     *            the SQL string used to define the statement
    185     * @param resultSetType
    186     *            result set type
    187     * @param resultSetConcurrency
    188     *            result set concurrency
    189     *
    190     * @return the PStmtKey created for the given arguments.
    191     */
    192    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency) {
    193        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency);
    194    }
    195
    196    /**
    197     * Creates a PStmtKey for the given arguments.
    198     *
    199     * @param sql
    200     *            the SQL string used to define the statement
    201     * @param resultSetType
    202     *            result set type
    203     * @param resultSetConcurrency
    204     *            result set concurrency
    205     * @param resultSetHoldability
    206     *            result set holdability
    207     *
    208     * @return the PStmtKey created for the given arguments.
    209     */
    210    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency,
    211            final int resultSetHoldability) {
    212        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency,
    213                resultSetHoldability);
    214    }
    215
    216    /**
    217     * Creates a PStmtKey for the given arguments.
    218     *
    219     * @param sql
    220     *            the SQL string used to define the statement
    221     * @param resultSetType
    222     *            result set type
    223     * @param resultSetConcurrency
    224     *            result set concurrency
    225     * @param resultSetHoldability
    226     *            result set holdability
    227     * @param statementType
    228     *            statement type
    229     *
    230     * @return the PStmtKey created for the given arguments.
    231     */
    232    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency,
    233            final int resultSetHoldability, final StatementType statementType) {
    234        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency,
    235                resultSetHoldability, statementType);
    236    }
    237
    238    /**
    239     * Creates a PStmtKey for the given arguments.
    240     *
    241     * @param sql
    242     *            the SQL string used to define the statement
    243     * @param resultSetType
    244     *            result set type
    245     * @param resultSetConcurrency
    246     *            result set concurrency
    247     * @param statementType
    248     *            statement type
    249     *
    250     * @return the PStmtKey created for the given arguments.
    251     */
    252    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency,
    253            final StatementType statementType) {
    254        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency, statementType);
    255    }
    256
    257    /**
    258     * Creates a PStmtKey for the given arguments.
    259     *
    260     * @param sql
    261     *            the SQL string used to define the statement
    262     * @param statementType
    263     *            statement type
    264     *
    265     * @return the PStmtKey created for the given arguments.
    266     */
    267    protected PStmtKey createKey(final String sql, final StatementType statementType) {
    268        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), statementType, null);
    269    }
    270
    271    /**
    272     * Creates a PStmtKey for the given arguments.
    273     *
    274     * @param sql
    275     *            the SQL string used to define the statement
    276     * @param columnNames
    277     *            column names
    278     *
    279     * @return the PStmtKey created for the given arguments.
    280     */
    281    protected PStmtKey createKey(final String sql, final String columnNames[]) {
    282        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), columnNames);
    283    }
    284
    285    /**
    286     * {@link KeyedPooledObjectFactory} method for destroying PoolablePreparedStatements and PoolableCallableStatements.
    287     * Closes the underlying statement.
    288     *
    289     * @param key
    290     *            ignored
    291     * @param pooledObject
    292     *            the wrapped pooled statement to be destroyed.
    293     */
    294    @Override
    295    public void destroyObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject)
    296            throws Exception {
    297        pooledObject.getObject().getInnermostDelegate().close();
    298    }
    299
    300    private String getCatalogOrNull() {
    301        String catalog = null;
    302        try {
    303            catalog = getCatalog();
    304        } catch (final SQLException e) {
    305            // Ignored
    306        }
    307        return catalog;
    308    }
    309
    310    private String getSchemaOrNull() {
    311        String schema = null;
    312        try {
    313            schema = getSchema();
    314        } catch (final SQLException e) {
    315            // Ignored
    316        }
    317        return schema;
    318    }
    319
    320    /**
    321     * Returns the prepared statement pool we're using.
    322     *
    323     * @return statement pool
    324     * @since 2.8.0
    325     */
    326    public KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> getStatementPool() {
    327        return pstmtPool;
    328    }
    329
    330    /**
    331     * {@link KeyedPooledObjectFactory} method for creating {@link PoolablePreparedStatement}s or
    332     * {@link PoolableCallableStatement}s. The <code>stmtType</code> field in the key determines whether a
    333     * PoolablePreparedStatement or PoolableCallableStatement is created.
    334     *
    335     * @param key
    336     *            the key for the {@link PreparedStatement} to be created
    337     * @see #createKey(String, int, int, StatementType)
    338     */
    339    @SuppressWarnings("resource")
    340    @Override
    341    public PooledObject<DelegatingPreparedStatement> makeObject(final PStmtKey key) throws Exception {
    342        if (null == key) {
    343            throw new IllegalArgumentException("Prepared statement key is null or invalid.");
    344        }
    345        if (key.getStmtType() == StatementType.PREPARED_STATEMENT) {
    346            final PreparedStatement statement = (PreparedStatement) key.createStatement(getDelegate());
    347            @SuppressWarnings({"rawtypes", "unchecked" }) // Unable to find way to avoid this
    348            final PoolablePreparedStatement pps = new PoolablePreparedStatement(statement, key, pstmtPool, this);
    349            return new DefaultPooledObject<>(pps);
    350        }
    351        final CallableStatement statement = (CallableStatement) key.createStatement(getDelegate());
    352        final PoolableCallableStatement pcs = new PoolableCallableStatement(statement, key, pstmtPool, this);
    353        return new DefaultPooledObject<>(pcs);
    354    }
    355
    356    /**
    357     * Normalizes the given SQL statement, producing a canonical form that is semantically equivalent to the original.
    358     *
    359     * @param sql The statement to be normalized.
    360     *
    361     * @return The canonical form of the supplied SQL statement.
    362     */
    363    protected String normalizeSQL(final String sql) {
    364        return sql.trim();
    365    }
    366
    367    /**
    368     * {@link KeyedPooledObjectFactory} method for passivating {@link PreparedStatement}s or {@link CallableStatement}s.
    369     * Invokes {@link PreparedStatement#clearParameters}.
    370     *
    371     * @param key
    372     *            ignored
    373     * @param pooledObject
    374     *            a wrapped {@link PreparedStatement}
    375     */
    376    @Override
    377    public void passivateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject)
    378            throws Exception {
    379        @SuppressWarnings("resource")
    380        final DelegatingPreparedStatement dps = pooledObject.getObject();
    381        dps.clearParameters();
    382        dps.passivate();
    383    }
    384
    385    /**
    386     * Creates or obtains a {@link CallableStatement} from the pool.
    387     *
    388     * @param key
    389     *            a {@link PStmtKey} for the given arguments
    390     * @return a {@link PoolableCallableStatement}
    391     * @throws SQLException
    392     *             Wraps an underlying exception.
    393     */
    394    private CallableStatement prepareCall(final PStmtKey key) throws SQLException {
    395        return (CallableStatement) prepareStatement(key);
    396    }
    397
    398    /**
    399     * Creates or obtains a {@link CallableStatement} from the pool.
    400     *
    401     * @param sql
    402     *            the SQL string used to define the CallableStatement
    403     * @return a {@link PoolableCallableStatement}
    404     * @throws SQLException
    405     *             Wraps an underlying exception.
    406     */
    407    @Override
    408    public CallableStatement prepareCall(final String sql) throws SQLException {
    409        return prepareCall(createKey(sql, StatementType.CALLABLE_STATEMENT));
    410    }
    411
    412    /**
    413     * Creates or obtains a {@link CallableStatement} from the pool.
    414     *
    415     * @param sql
    416     *            the SQL string used to define the CallableStatement
    417     * @param resultSetType
    418     *            result set type
    419     * @param resultSetConcurrency
    420     *            result set concurrency
    421     * @return a {@link PoolableCallableStatement}
    422     * @throws SQLException
    423     *             Wraps an underlying exception.
    424     */
    425    @Override
    426    public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency)
    427            throws SQLException {
    428        return prepareCall(createKey(sql, resultSetType, resultSetConcurrency, StatementType.CALLABLE_STATEMENT));
    429    }
    430
    431    /**
    432     * Creates or obtains a {@link CallableStatement} from the pool.
    433     *
    434     * @param sql
    435     *            the SQL string used to define the CallableStatement
    436     * @param resultSetType
    437     *            result set type
    438     * @param resultSetConcurrency
    439     *            result set concurrency
    440     * @param resultSetHoldability
    441     *            result set holdability
    442     * @return a {@link PoolableCallableStatement}
    443     * @throws SQLException
    444     *             Wraps an underlying exception.
    445     */
    446    @Override
    447    public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency,
    448            final int resultSetHoldability) throws SQLException {
    449        return prepareCall(createKey(sql, resultSetType, resultSetConcurrency,
    450                resultSetHoldability, StatementType.CALLABLE_STATEMENT));
    451    }
    452
    453    /**
    454     * Creates or obtains a {@link PreparedStatement} from the pool.
    455     *
    456     * @param key
    457     *            a {@link PStmtKey} for the given arguments
    458     * @return a {@link PoolablePreparedStatement}
    459     * @throws SQLException
    460     *             Wraps an underlying exception.
    461     */
    462    private PreparedStatement prepareStatement(final PStmtKey key) throws SQLException {
    463        if (null == pstmtPool) {
    464            throw new SQLException("Statement pool is null - closed or invalid PoolingConnection.");
    465        }
    466        try {
    467            return pstmtPool.borrowObject(key);
    468        } catch (final NoSuchElementException e) {
    469            throw new SQLException("MaxOpenPreparedStatements limit reached", e);
    470        } catch (final RuntimeException e) {
    471            throw e;
    472        } catch (final Exception e) {
    473            throw new SQLException("Borrow prepareStatement from pool failed", e);
    474        }
    475    }
    476
    477    /**
    478     * Creates or obtains a {@link PreparedStatement} from the pool.
    479     *
    480     * @param sql
    481     *            the SQL string used to define the PreparedStatement
    482     * @return a {@link PoolablePreparedStatement}
    483     * @throws SQLException
    484     *             Wraps an underlying exception.
    485     */
    486    @Override
    487    public PreparedStatement prepareStatement(final String sql) throws SQLException {
    488        return prepareStatement(createKey(sql));
    489    }
    490
    491    /*
    492     * Creates or obtains a {@link PreparedStatement} from the pool.
    493     *
    494     * @param sql
    495     *            the SQL string used to define the PreparedStatement
    496     * @param autoGeneratedKeys
    497     *            A flag indicating whether auto-generated keys should be returned; one of
    498     *            <code>Statement.RETURN_GENERATED_KEYS</code> or <code>Statement.NO_GENERATED_KEYS</code>.
    499     * @return a {@link PoolablePreparedStatement}
    500     * @throws SQLException
    501     *             Wraps an underlying exception.
    502     */
    503    @Override
    504    public PreparedStatement prepareStatement(final String sql, final int autoGeneratedKeys) throws SQLException {
    505        return prepareStatement(createKey(sql, autoGeneratedKeys));
    506    }
    507
    508    /**
    509     * Creates or obtains a {@link PreparedStatement} from the pool.
    510     *
    511     * @param sql
    512     *            the SQL string used to define the PreparedStatement
    513     * @param columnIndexes
    514     *            An array of column indexes indicating the columns that should be returned from the inserted row or
    515     *            rows.
    516     * @return a {@link PoolablePreparedStatement}
    517     * @throws SQLException
    518     *             Wraps an underlying exception.
    519     *
    520     */
    521    @Override
    522    public PreparedStatement prepareStatement(final String sql, final int columnIndexes[]) throws SQLException {
    523        return prepareStatement(createKey(sql, columnIndexes));
    524    }
    525
    526    /**
    527     * Creates or obtains a {@link PreparedStatement} from the pool.
    528     *
    529     * @param sql
    530     *            the SQL string used to define the PreparedStatement
    531     * @param resultSetType
    532     *            result set type
    533     * @param resultSetConcurrency
    534     *            result set concurrency
    535     * @return a {@link PoolablePreparedStatement}
    536     * @throws SQLException
    537     *             Wraps an underlying exception.
    538     */
    539    @Override
    540    public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency)
    541            throws SQLException {
    542        return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency));
    543    }
    544
    545    /**
    546     * Creates or obtains a {@link PreparedStatement} from the pool.
    547     *
    548     * @param sql
    549     *            the SQL string used to define the PreparedStatement
    550     * @param resultSetType
    551     *            result set type
    552     * @param resultSetConcurrency
    553     *            result set concurrency
    554     * @param resultSetHoldability
    555     *            result set holdability
    556     * @return a {@link PoolablePreparedStatement}
    557     * @throws SQLException
    558     *             Wraps an underlying exception.
    559     */
    560    @Override
    561    public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency,
    562            final int resultSetHoldability) throws SQLException {
    563        return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency, resultSetHoldability));
    564    }
    565
    566    /**
    567     * Creates or obtains a {@link PreparedStatement} from the pool.
    568     *
    569     * @param sql
    570     *            the SQL string used to define the PreparedStatement
    571     * @param columnNames
    572     *            column names
    573     * @return a {@link PoolablePreparedStatement}
    574     * @throws SQLException
    575     *             Wraps an underlying exception.
    576     */
    577    @Override
    578    public PreparedStatement prepareStatement(final String sql, final String columnNames[]) throws SQLException {
    579        return prepareStatement(createKey(sql, columnNames));
    580    }
    581
    582    /**
    583     * Sets whether the pool of statements should be cleared when the connection is returned to its pool.
    584     * Default is false.
    585     *
    586     * @param clearStatementPoolOnReturn clear or not
    587     * @since 2.8.0
    588     */
    589    public void setClearStatementPoolOnReturn(final boolean clearStatementPoolOnReturn) {
    590        this.clearStatementPoolOnReturn = clearStatementPoolOnReturn;
    591    }
    592
    593    /**
    594     * Sets the prepared statement pool.
    595     *
    596     * @param pool
    597     *            the prepared statement pool.
    598     */
    599    public void setStatementPool(final KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> pool) {
    600        pstmtPool = pool;
    601    }
    602
    603    @Override
    604    public synchronized String toString() {
    605        if (pstmtPool != null) {
    606            return "PoolingConnection: " + pstmtPool.toString();
    607        }
    608        return "PoolingConnection: null";
    609    }
    610
    611    /**
    612     * {@link KeyedPooledObjectFactory} method for validating pooled statements. Currently always returns true.
    613     *
    614     * @param key
    615     *            ignored
    616     * @param pooledObject
    617     *            ignored
    618     * @return {@code true}
    619     */
    620    @Override
    621    public boolean validateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject) {
    622        return true;
    623    }
    624}