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