    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 *      https://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> stmtPool;
    078
    079    private volatile 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 != stmtPool) {
    113                final KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> oldPool = stmtPool;
    114                stmtPool = 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 (stmtPool != null && clearStatementPoolOnReturn) {
    145            try {
    146                stmtPool.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     *            {@link Statement#RETURN_GENERATED_KEYS} or {@link 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 stmtPool;
    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, stmtPool, this);
    369            return new DefaultPooledObject<>(pps);
    370        }
    371        final CallableStatement statement = (CallableStatement) key.createStatement(getDelegate());
    372        final PoolableCallableStatement pcs = new PoolableCallableStatement(statement, key, stmtPool, 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     * @return The canonical form of the supplied SQL statement.
    381     */
    382    protected String normalizeSQL(final String sql) {
    383        return sql.trim();
    384    }
    385
    386    /**
    387     * {@link KeyedPooledObjectFactory} method for passivating {@link PreparedStatement}s or {@link CallableStatement}s.
    388     * Invokes {@link PreparedStatement#clearParameters}.
    389     *
    390     * @param key
    391     *            ignored
    392     * @param pooledObject
    393     *            a wrapped {@link PreparedStatement}
    394     */
    395    @Override
    396    public void passivateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject)
    397            throws SQLException {
    398        @SuppressWarnings("resource")
    399        final DelegatingPreparedStatement dps = pooledObject.getObject();
    400        dps.clearParameters();
    401        dps.passivate();
    402    }
    403
    404    /**
    405     * Creates or obtains a {@link CallableStatement} from the pool.
    406     *
    407     * @param key
    408     *            a {@link PStmtKey} for the given arguments
    409     * @return a {@link PoolableCallableStatement}
    410     * @throws SQLException
    411     *             Wraps an underlying exception.
    412     */
    413    private CallableStatement prepareCall(final PStmtKey key) throws SQLException {
    414        return (CallableStatement) prepareStatement(key);
    415    }
    416
    417    /**
    418     * Creates or obtains a {@link CallableStatement} from the pool.
    419     *
    420     * @param sql
    421     *            the SQL string used to define the CallableStatement
    422     * @return a {@link PoolableCallableStatement}
    423     * @throws SQLException
    424     *             Wraps an underlying exception.
    425     */
    426    @Override
    427    public CallableStatement prepareCall(final String sql) throws SQLException {
    428        return prepareCall(createKey(sql, StatementType.CALLABLE_STATEMENT));
    429    }
    430
    431    /**
    432     * Creates or obtains a {@link CallableStatement} from the pool.
    433     *
    434     * @param sql
    435     *            the SQL string used to define the CallableStatement
    436     * @param resultSetType
    437     *            result set type
    438     * @param resultSetConcurrency
    439     *            result set concurrency
    440     * @return a {@link PoolableCallableStatement}
    441     * @throws SQLException
    442     *             Wraps an underlying exception.
    443     */
    444    @Override
    445    public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency)
    446            throws SQLException {
    447        return prepareCall(createKey(sql, resultSetType, resultSetConcurrency, StatementType.CALLABLE_STATEMENT));
    448    }
    449
    450    /**
    451     * Creates or obtains a {@link CallableStatement} from the pool.
    452     *
    453     * @param sql
    454     *            the SQL string used to define the CallableStatement
    455     * @param resultSetType
    456     *            result set type
    457     * @param resultSetConcurrency
    458     *            result set concurrency
    459     * @param resultSetHoldability
    460     *            result set holdability
    461     * @return a {@link PoolableCallableStatement}
    462     * @throws SQLException
    463     *             Wraps an underlying exception.
    464     */
    465    @Override
    466    public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency,
    467            final int resultSetHoldability) throws SQLException {
    468        return prepareCall(createKey(sql, resultSetType, resultSetConcurrency,
    469                resultSetHoldability, StatementType.CALLABLE_STATEMENT));
    470    }
    471
    472    /**
    473     * Creates or obtains a {@link PreparedStatement} from the pool.
    474     *
    475     * @param key
    476     *            a {@link PStmtKey} for the given arguments
    477     * @return a {@link PoolablePreparedStatement}
    478     * @throws SQLException
    479     *             Wraps an underlying exception.
    480     */
    481    private PreparedStatement prepareStatement(final PStmtKey key) throws SQLException {
    482        if (null == stmtPool) {
    483            throw new SQLException("Statement pool is null - closed or invalid PoolingConnection.");
    484        }
    485        try {
    486            return stmtPool.borrowObject(key);
    487        } catch (final NoSuchElementException e) {
    488            throw new SQLException("MaxOpenPreparedStatements limit reached", e);
    489        } catch (final RuntimeException e) {
    490            throw e;
    491        } catch (final Exception e) {
    492            throw new SQLException("Borrow prepareStatement from pool failed", e);
    493        }
    494    }
    495
    496    /**
    497     * Creates or obtains a {@link PreparedStatement} from the pool.
    498     *
    499     * @param sql
    500     *            the SQL string used to define the PreparedStatement
    501     * @return a {@link PoolablePreparedStatement}
    502     * @throws SQLException
    503     *             Wraps an underlying exception.
    504     */
    505    @Override
    506    public PreparedStatement prepareStatement(final String sql) throws SQLException {
    507        return prepareStatement(createKey(sql));
    508    }
    509
    510    /*
    511     * Creates or obtains a {@link PreparedStatement} from the pool.
    512     *
    513     * @param sql
    514     *            the SQL string used to define the PreparedStatement
    515     * @param autoGeneratedKeys
    516     *            A flag indicating whether auto-generated keys should be returned; one of
    517     *            {@link Statement#RETURN_GENERATED_KEYS} or {@link Statement#NO_GENERATED_KEYS}.
    518     * @return a {@link PoolablePreparedStatement}
    519     * @throws SQLException
    520     *             Wraps an underlying exception.
    521     */
    522    @Override
    523    public PreparedStatement prepareStatement(final String sql, final int autoGeneratedKeys) throws SQLException {
    524        return prepareStatement(createKey(sql, autoGeneratedKeys));
    525    }
    526
    527    /**
    528     * Creates or obtains a {@link PreparedStatement} from the pool.
    529     *
    530     * @param sql
    531     *            the SQL string used to define the PreparedStatement
    532     * @param resultSetType
    533     *            result set type
    534     * @param resultSetConcurrency
    535     *            result set concurrency
    536     * @return a {@link PoolablePreparedStatement}
    537     * @throws SQLException
    538     *             Wraps an underlying exception.
    539     */
    540    @Override
    541    public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency)
    542            throws SQLException {
    543        return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency));
    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 resultSetType
    552     *            result set type
    553     * @param resultSetConcurrency
    554     *            result set concurrency
    555     * @param resultSetHoldability
    556     *            result set holdability
    557     * @return a {@link PoolablePreparedStatement}
    558     * @throws SQLException
    559     *             Wraps an underlying exception.
    560     */
    561    @Override
    562    public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency,
    563            final int resultSetHoldability) throws SQLException {
    564        return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency, resultSetHoldability));
    565    }
    566
    567    /**
    568     * Creates or obtains a {@link PreparedStatement} from the pool.
    569     *
    570     * @param sql
    571     *            the SQL string used to define the PreparedStatement
    572     * @param columnIndexes
    573     *            An array of column indexes indicating the columns that should be returned from the inserted row or
    574     *            rows.
    575     * @return a {@link PoolablePreparedStatement}
    576     * @throws SQLException
    577     *             Wraps an underlying exception.
    578     */
    579    @Override
    580    public PreparedStatement prepareStatement(final String sql, final int[] columnIndexes) throws SQLException {
    581        return prepareStatement(createKey(sql, columnIndexes));
    582    }
    583
    584    /**
    585     * Creates or obtains a {@link PreparedStatement} from the pool.
    586     *
    587     * @param sql
    588     *            the SQL string used to define the PreparedStatement
    589     * @param columnNames
    590     *            column names
    591     * @return a {@link PoolablePreparedStatement}
    592     * @throws SQLException
    593     *             Wraps an underlying exception.
    594     */
    595    @Override
    596    public PreparedStatement prepareStatement(final String sql, final String[] columnNames) throws SQLException {
    597        return prepareStatement(createKey(sql, columnNames));
    598    }
    599
    600    /**
    601     * Sets whether the pool of statements should be cleared when the connection is returned to its pool.
    602     * Default is false.
    603     *
    604     * @param clearStatementPoolOnReturn clear or not
    605     * @since 2.8.0
    606     */
    607    public void setClearStatementPoolOnReturn(final boolean clearStatementPoolOnReturn) {
    608        this.clearStatementPoolOnReturn = clearStatementPoolOnReturn;
    609    }
    610
    611    /**
    612     * Sets the prepared statement pool.
    613     *
    614     * @param pool
    615     *            the prepared statement pool.
    616     */
    617    public void setStatementPool(final KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> pool) {
    618        stmtPool = pool;
    619    }
    620
    621    @Override
    622    public synchronized String toString() {
    623        if (stmtPool instanceof GenericKeyedObjectPool) {
    624            // DBCP-596 PoolingConnection.toString() causes StackOverflowError
    625            final GenericKeyedObjectPool<?, ?> gkop = (GenericKeyedObjectPool<?, ?>) stmtPool;
    626            if (gkop.getFactory() == this) {
    627                return "PoolingConnection: " + stmtPool.getClass() + "@" + System.identityHashCode(stmtPool);
    628            }
    629        }
    630        return "PoolingConnection: " + Objects.toString(stmtPool);
    631    }
    632
    633    /**
    634     * {@link KeyedPooledObjectFactory} method for validating pooled statements. Currently, always returns true.
    635     *
    636     * @param key
    637     *            ignored
    638     * @param pooledObject
    639     *            ignored
    640     * @return {@code true}
    641     */
    642    @Override
    643    public boolean validateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject) {
    644        return true;
    645    }
    646}