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 }