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