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