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