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