View Javadoc
1   /*
2    * Licensed to the Apache Software Foundation (ASF) under one or more
3    * contributor license agreements.  See the NOTICE file distributed with
4    * this work for additional information regarding copyright ownership.
5    * The ASF licenses this file to You under the Apache License, Version 2.0
6    * (the "License"); you may not use this file except in compliance with
7    * the License.  You may obtain a copy of the License at
8    *
9    *      http://www.apache.org/licenses/LICENSE-2.0
10   *
11   * Unless required by applicable law or agreed to in writing, software
12   * distributed under the License is distributed on an "AS IS" BASIS,
13   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14   * See the License for the specific language governing permissions and
15   * limitations under the License.
16   */
17  
18  package org.apache.commons.dbcp2;
19  
20  import java.sql.CallableStatement;
21  import java.sql.Connection;
22  import java.sql.PreparedStatement;
23  import java.sql.SQLException;
24  import java.util.NoSuchElementException;
25  
26  import org.apache.commons.pool2.KeyedObjectPool;
27  import org.apache.commons.pool2.KeyedPooledObjectFactory;
28  import org.apache.commons.pool2.PooledObject;
29  import org.apache.commons.pool2.impl.DefaultPooledObject;
30  
31  /**
32   * A {@link DelegatingConnection} that pools {@link PreparedStatement}s.
33   * <p>
34   * The {@link #prepareStatement} and {@link #prepareCall} methods, rather than creating a new PreparedStatement each
35   * time, may actually pull the statement from a pool of unused statements. The {@link PreparedStatement#close} method of
36   * the returned statement doesn't actually close the statement, but rather returns it to the pool. (See
37   * {@link PoolablePreparedStatement}, {@link PoolableCallableStatement}.)
38   * </p>
39   *
40   * @see PoolablePreparedStatement
41   * @since 2.0
42   */
43  public class PoolingConnection extends DelegatingConnection<Connection>
44          implements KeyedPooledObjectFactory<PStmtKey, DelegatingPreparedStatement> {
45  
46      /**
47       * Statement types.
48       *
49       * @since 2.0 protected enum.
50       * @since 2.4.0 public enum.
51       */
52      public enum StatementType {
53  
54          /**
55           * Callable statement.
56           */
57          CALLABLE_STATEMENT,
58  
59          /**
60           * Prepared statement.
61           */
62          PREPARED_STATEMENT
63      }
64  
65      /** Pool of {@link PreparedStatement}s. and {@link CallableStatement}s */
66      private KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> pstmtPool;
67  
68      private boolean clearStatementPoolOnReturn;
69  
70      /**
71       * Constructor.
72       *
73       * @param connection
74       *            the underlying {@link Connection}.
75       */
76      public PoolingConnection(final Connection connection) {
77          super(connection);
78      }
79  
80      /**
81       * {@link KeyedPooledObjectFactory} method for activating pooled statements.
82       *
83       * @param key
84       *            ignored
85       * @param pooledObject
86       *            wrapped pooled statement to be activated
87       */
88      @Override
89      public void activateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject)
90              throws Exception {
91          pooledObject.getObject().activate();
92      }
93  
94      /**
95       * Closes and frees all {@link PreparedStatement}s or {@link CallableStatement}s from the pool, and close the
96       * underlying connection.
97       */
98      @Override
99      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 PoolablePreparedStatementtement.html#PoolablePreparedStatement">PoolablePreparedStatement pps = new PoolablePreparedStatement(statement, key, pstmtPool, this);
349             return new DefaultPooledObject<>(pps);
350         }
351         final CallableStatement statement = (CallableStatement) key.createStatement(getDelegate());
352         final PoolableCallableStatementtement.html#PoolableCallableStatement">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 }