    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 */
    017package org.apache.commons.dbcp2;
    018
    019import java.sql.CallableStatement;
    020import java.sql.Connection;
    021import java.sql.PreparedStatement;
    022import java.sql.SQLException;
    023import java.util.NoSuchElementException;
    024import java.util.Objects;
    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;
    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 SQLException {
    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} 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 (final 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 autoGeneratedKeys
    156     *            A flag indicating whether auto-generated keys should be returned; one of
    157     *            {@code Statement.RETURN_GENERATED_KEYS} or {@code Statement.NO_GENERATED_KEYS}.
    158     *
    159     * @return the PStmtKey created for the given arguments.
    160     */
    161    protected PStmtKey createKey(final String sql, final int autoGeneratedKeys) {
    162        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), autoGeneratedKeys);
    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 resultSetType
    171     *            result set type
    172     * @param resultSetConcurrency
    173     *            result set concurrency
    174     *
    175     * @return the PStmtKey created for the given arguments.
    176     */
    177    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency) {
    178        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency);
    179    }
    180
    181    /**
    182     * Creates a PStmtKey for the given arguments.
    183     *
    184     * @param sql
    185     *            the SQL string used to define the statement
    186     * @param resultSetType
    187     *            result set type
    188     * @param resultSetConcurrency
    189     *            result set concurrency
    190     * @param resultSetHoldability
    191     *            result set holdability
    192     *
    193     * @return the PStmtKey created for the given arguments.
    194     */
    195    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency,
    196            final int resultSetHoldability) {
    197        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency,
    198                resultSetHoldability);
    199    }
    200
    201    /**
    202     * Creates a PStmtKey for the given arguments.
    203     *
    204     * @param sql
    205     *            the SQL string used to define the statement
    206     * @param resultSetType
    207     *            result set type
    208     * @param resultSetConcurrency
    209     *            result set concurrency
    210     * @param resultSetHoldability
    211     *            result set holdability
    212     * @param statementType
    213     *            statement type
    214     *
    215     * @return the PStmtKey created for the given arguments.
    216     */
    217    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency,
    218            final int resultSetHoldability, final StatementType statementType) {
    219        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency,
    220                resultSetHoldability, statementType);
    221    }
    222
    223    /**
    224     * Creates a PStmtKey for the given arguments.
    225     *
    226     * @param sql
    227     *            the SQL string used to define the statement
    228     * @param resultSetType
    229     *            result set type
    230     * @param resultSetConcurrency
    231     *            result set concurrency
    232     * @param statementType
    233     *            statement type
    234     *
    235     * @return the PStmtKey created for the given arguments.
    236     */
    237    protected PStmtKey createKey(final String sql, final int resultSetType, final int resultSetConcurrency,
    238            final StatementType statementType) {
    239        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), resultSetType, resultSetConcurrency, statementType);
    240    }
    241
    242    /**
    243     * Creates a PStmtKey for the given arguments.
    244     *
    245     * @param sql
    246     *            the SQL string used to define the statement
    247     * @param columnIndexes
    248     *            An array of column indexes indicating the columns that should be returned from the inserted row or
    249     *            rows.
    250     *
    251     * @return the PStmtKey created for the given arguments.
    252     */
    253    protected PStmtKey createKey(final String sql, final int[] columnIndexes) {
    254        return new PStmtKey(normalizeSQL(sql), getCatalogOrNull(), getSchemaOrNull(), columnIndexes);
    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 SQLException {
    297        pooledObject.getObject().getInnermostDelegate().close();
    298    }
    299
    300    private String getCatalogOrNull() {
    301        try {
    302            return getCatalog();
    303        } catch (final SQLException ignored) {
    304            return null;
    305        }
    306    }
    307
    308    private String getSchemaOrNull() {
    309        try {
    310            return getSchema();
    311        } catch (final SQLException ignored) {
    312            return null;
    313        }
    314    }
    315
    316    /**
    317     * Gets the prepared statement pool.
    318     *
    319     * @return statement pool
    320     * @since 2.8.0
    321     */
    322    public KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> getStatementPool() {
    323        return pStmtPool;
    324    }
    325
    326    /**
    327     * {@link KeyedPooledObjectFactory} method for creating {@link PoolablePreparedStatement}s or
    328     * {@link PoolableCallableStatement}s. The {@code stmtType} field in the key determines whether a
    329     * PoolablePreparedStatement or PoolableCallableStatement is created.
    330     *
    331     * @param key
    332     *            the key for the {@link PreparedStatement} to be created
    333     * @see #createKey(String, int, int, StatementType)
    334     */
    335    @SuppressWarnings("resource")
    336    @Override
    337    public PooledObject<DelegatingPreparedStatement> makeObject(final PStmtKey key) throws SQLException {
    338        if (null == key) {
    339            throw new IllegalArgumentException("Prepared statement key is null or invalid.");
    340        }
    341        if (key.getStmtType() == StatementType.PREPARED_STATEMENT) {
    342            final PreparedStatement statement = (PreparedStatement) key.createStatement(getDelegate());
    343            @SuppressWarnings({"rawtypes", "unchecked" }) // Unable to find way to avoid this
    344            final PoolablePreparedStatement pps = new PoolablePreparedStatement(statement, key, pStmtPool, this);
    345            return new DefaultPooledObject<>(pps);
    346        }
    347        final CallableStatement statement = (CallableStatement) key.createStatement(getDelegate());
    348        final PoolableCallableStatement pcs = new PoolableCallableStatement(statement, key, pStmtPool, this);
    349        return new DefaultPooledObject<>(pcs);
    350    }
    351
    352    /**
    353     * Normalizes the given SQL statement, producing a canonical form that is semantically equivalent to the original.
    354     *
    355     * @param sql The statement to be normalized.
    356     *
    357     * @return The canonical form of the supplied SQL statement.
    358     */
    359    protected String normalizeSQL(final String sql) {
    360        return sql.trim();
    361    }
    362
    363    /**
    364     * {@link KeyedPooledObjectFactory} method for passivating {@link PreparedStatement}s or {@link CallableStatement}s.
    365     * Invokes {@link PreparedStatement#clearParameters}.
    366     *
    367     * @param key
    368     *            ignored
    369     * @param pooledObject
    370     *            a wrapped {@link PreparedStatement}
    371     */
    372    @Override
    373    public void passivateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject)
    374            throws SQLException {
    375        @SuppressWarnings("resource")
    376        final DelegatingPreparedStatement dps = pooledObject.getObject();
    377        dps.clearParameters();
    378        dps.passivate();
    379    }
    380
    381    /**
    382     * Creates or obtains a {@link CallableStatement} from the pool.
    383     *
    384     * @param key
    385     *            a {@link PStmtKey} for the given arguments
    386     * @return a {@link PoolableCallableStatement}
    387     * @throws SQLException
    388     *             Wraps an underlying exception.
    389     */
    390    private CallableStatement prepareCall(final PStmtKey key) throws SQLException {
    391        return (CallableStatement) prepareStatement(key);
    392    }
    393
    394    /**
    395     * Creates or obtains a {@link CallableStatement} from the pool.
    396     *
    397     * @param sql
    398     *            the SQL string used to define the CallableStatement
    399     * @return a {@link PoolableCallableStatement}
    400     * @throws SQLException
    401     *             Wraps an underlying exception.
    402     */
    403    @Override
    404    public CallableStatement prepareCall(final String sql) throws SQLException {
    405        return prepareCall(createKey(sql, StatementType.CALLABLE_STATEMENT));
    406    }
    407
    408    /**
    409     * Creates or obtains a {@link CallableStatement} from the pool.
    410     *
    411     * @param sql
    412     *            the SQL string used to define the CallableStatement
    413     * @param resultSetType
    414     *            result set type
    415     * @param resultSetConcurrency
    416     *            result set concurrency
    417     * @return a {@link PoolableCallableStatement}
    418     * @throws SQLException
    419     *             Wraps an underlying exception.
    420     */
    421    @Override
    422    public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency)
    423            throws SQLException {
    424        return prepareCall(createKey(sql, resultSetType, resultSetConcurrency, StatementType.CALLABLE_STATEMENT));
    425    }
    426
    427    /**
    428     * Creates or obtains a {@link CallableStatement} from the pool.
    429     *
    430     * @param sql
    431     *            the SQL string used to define the CallableStatement
    432     * @param resultSetType
    433     *            result set type
    434     * @param resultSetConcurrency
    435     *            result set concurrency
    436     * @param resultSetHoldability
    437     *            result set holdability
    438     * @return a {@link PoolableCallableStatement}
    439     * @throws SQLException
    440     *             Wraps an underlying exception.
    441     */
    442    @Override
    443    public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency,
    444            final int resultSetHoldability) throws SQLException {
    445        return prepareCall(createKey(sql, resultSetType, resultSetConcurrency,
    446                resultSetHoldability, StatementType.CALLABLE_STATEMENT));
    447    }
    448
    449    /**
    450     * Creates or obtains a {@link PreparedStatement} from the pool.
    451     *
    452     * @param key
    453     *            a {@link PStmtKey} for the given arguments
    454     * @return a {@link PoolablePreparedStatement}
    455     * @throws SQLException
    456     *             Wraps an underlying exception.
    457     */
    458    private PreparedStatement prepareStatement(final PStmtKey key) throws SQLException {
    459        if (null == pStmtPool) {
    460            throw new SQLException("Statement pool is null - closed or invalid PoolingConnection.");
    461        }
    462        try {
    463            return pStmtPool.borrowObject(key);
    464        } catch (final NoSuchElementException e) {
    465            throw new SQLException("MaxOpenPreparedStatements limit reached", e);
    466        } catch (final RuntimeException e) {
    467            throw e;
    468        } catch (final Exception e) {
    469            throw new SQLException("Borrow prepareStatement from pool failed", e);
    470        }
    471    }
    472
    473    /**
    474     * Creates or obtains a {@link PreparedStatement} from the pool.
    475     *
    476     * @param sql
    477     *            the SQL string used to define the PreparedStatement
    478     * @return a {@link PoolablePreparedStatement}
    479     * @throws SQLException
    480     *             Wraps an underlying exception.
    481     */
    482    @Override
    483    public PreparedStatement prepareStatement(final String sql) throws SQLException {
    484        return prepareStatement(createKey(sql));
    485    }
    486
    487    /*
    488     * Creates or obtains a {@link PreparedStatement} from the pool.
    489     *
    490     * @param sql
    491     *            the SQL string used to define the PreparedStatement
    492     * @param autoGeneratedKeys
    493     *            A flag indicating whether auto-generated keys should be returned; one of
    494     *            {@code Statement.RETURN_GENERATED_KEYS} or {@code Statement.NO_GENERATED_KEYS}.
    495     * @return a {@link PoolablePreparedStatement}
    496     * @throws SQLException
    497     *             Wraps an underlying exception.
    498     */
    499    @Override
    500    public PreparedStatement prepareStatement(final String sql, final int autoGeneratedKeys) throws SQLException {
    501        return prepareStatement(createKey(sql, autoGeneratedKeys));
    502    }
    503
    504    /**
    505     * Creates or obtains a {@link PreparedStatement} from the pool.
    506     *
    507     * @param sql
    508     *            the SQL string used to define the PreparedStatement
    509     * @param resultSetType
    510     *            result set type
    511     * @param resultSetConcurrency
    512     *            result set concurrency
    513     * @return a {@link PoolablePreparedStatement}
    514     * @throws SQLException
    515     *             Wraps an underlying exception.
    516     */
    517    @Override
    518    public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency)
    519            throws SQLException {
    520        return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency));
    521    }
    522
    523    /**
    524     * Creates or obtains a {@link PreparedStatement} from the pool.
    525     *
    526     * @param sql
    527     *            the SQL string used to define the PreparedStatement
    528     * @param resultSetType
    529     *            result set type
    530     * @param resultSetConcurrency
    531     *            result set concurrency
    532     * @param resultSetHoldability
    533     *            result set holdability
    534     * @return a {@link PoolablePreparedStatement}
    535     * @throws SQLException
    536     *             Wraps an underlying exception.
    537     */
    538    @Override
    539    public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency,
    540            final int resultSetHoldability) throws SQLException {
    541        return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency, resultSetHoldability));
    542    }
    543
    544    /**
    545     * Creates or obtains a {@link PreparedStatement} from the pool.
    546     *
    547     * @param sql
    548     *            the SQL string used to define the PreparedStatement
    549     * @param columnIndexes
    550     *            An array of column indexes indicating the columns that should be returned from the inserted row or
    551     *            rows.
    552     * @return a {@link PoolablePreparedStatement}
    553     * @throws SQLException
    554     *             Wraps an underlying exception.
    555     *
    556     */
    557    @Override
    558    public PreparedStatement prepareStatement(final String sql, final int[] columnIndexes) throws SQLException {
    559        return prepareStatement(createKey(sql, columnIndexes));
    560    }
    561
    562    /**
    563     * Creates or obtains a {@link PreparedStatement} from the pool.
    564     *
    565     * @param sql
    566     *            the SQL string used to define the PreparedStatement
    567     * @param columnNames
    568     *            column names
    569     * @return a {@link PoolablePreparedStatement}
    570     * @throws SQLException
    571     *             Wraps an underlying exception.
    572     */
    573    @Override
    574    public PreparedStatement prepareStatement(final String sql, final String[] columnNames) throws SQLException {
    575        return prepareStatement(createKey(sql, columnNames));
    576    }
    577
    578    /**
    579     * Sets whether the pool of statements should be cleared when the connection is returned to its pool.
    580     * Default is false.
    581     *
    582     * @param clearStatementPoolOnReturn clear or not
    583     * @since 2.8.0
    584     */
    585    public void setClearStatementPoolOnReturn(final boolean clearStatementPoolOnReturn) {
    586        this.clearStatementPoolOnReturn = clearStatementPoolOnReturn;
    587    }
    588
    589    /**
    590     * Sets the prepared statement pool.
    591     *
    592     * @param pool
    593     *            the prepared statement pool.
    594     */
    595    public void setStatementPool(final KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> pool) {
    596        pStmtPool = pool;
    597    }
    598
    599    @Override
    600    public synchronized String toString() {
    601        return "PoolingConnection: " + Objects.toString(pStmtPool);
    602    }
    603
    604    /**
    605     * {@link KeyedPooledObjectFactory} method for validating pooled statements. Currently, always returns true.
    606     *
    607     * @param key
    608     *            ignored
    609     * @param pooledObject
    610     *            ignored
    611     * @return {@code true}
    612     */
    613    @Override
    614    public boolean validateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject) {
    615        return true;
    616    }
    617}