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