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