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 * https://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> stmtPool;
78
79 private volatile 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 != stmtPool) {
113 final KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> oldPool = stmtPool;
114 stmtPool = 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 (stmtPool != null && clearStatementPoolOnReturn) {
145 try {
146 stmtPool.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 * {@link Statement#RETURN_GENERATED_KEYS} or {@link 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 stmtPool;
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, stmtPool, this);
369 return new DefaultPooledObject<>(pps);
370 }
371 final CallableStatement statement = (CallableStatement) key.createStatement(getDelegate());
372 final PoolableCallableStatement pcs = new PoolableCallableStatement(statement, key, stmtPool, 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 * @return The canonical form of the supplied SQL statement.
381 */
382 protected String normalizeSQL(final String sql) {
383 return sql.trim();
384 }
385
386 /**
387 * {@link KeyedPooledObjectFactory} method for passivating {@link PreparedStatement}s or {@link CallableStatement}s.
388 * Invokes {@link PreparedStatement#clearParameters}.
389 *
390 * @param key
391 * ignored
392 * @param pooledObject
393 * a wrapped {@link PreparedStatement}
394 */
395 @Override
396 public void passivateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject)
397 throws SQLException {
398 @SuppressWarnings("resource")
399 final DelegatingPreparedStatement dps = pooledObject.getObject();
400 dps.clearParameters();
401 dps.passivate();
402 }
403
404 /**
405 * Creates or obtains a {@link CallableStatement} from the pool.
406 *
407 * @param key
408 * a {@link PStmtKey} for the given arguments
409 * @return a {@link PoolableCallableStatement}
410 * @throws SQLException
411 * Wraps an underlying exception.
412 */
413 private CallableStatement prepareCall(final PStmtKey key) throws SQLException {
414 return (CallableStatement) prepareStatement(key);
415 }
416
417 /**
418 * Creates or obtains a {@link CallableStatement} from the pool.
419 *
420 * @param sql
421 * the SQL string used to define the CallableStatement
422 * @return a {@link PoolableCallableStatement}
423 * @throws SQLException
424 * Wraps an underlying exception.
425 */
426 @Override
427 public CallableStatement prepareCall(final String sql) throws SQLException {
428 return prepareCall(createKey(sql, 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 * @return a {@link PoolableCallableStatement}
441 * @throws SQLException
442 * Wraps an underlying exception.
443 */
444 @Override
445 public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency)
446 throws SQLException {
447 return prepareCall(createKey(sql, resultSetType, resultSetConcurrency, StatementType.CALLABLE_STATEMENT));
448 }
449
450 /**
451 * Creates or obtains a {@link CallableStatement} from the pool.
452 *
453 * @param sql
454 * the SQL string used to define the CallableStatement
455 * @param resultSetType
456 * result set type
457 * @param resultSetConcurrency
458 * result set concurrency
459 * @param resultSetHoldability
460 * result set holdability
461 * @return a {@link PoolableCallableStatement}
462 * @throws SQLException
463 * Wraps an underlying exception.
464 */
465 @Override
466 public CallableStatement prepareCall(final String sql, final int resultSetType, final int resultSetConcurrency,
467 final int resultSetHoldability) throws SQLException {
468 return prepareCall(createKey(sql, resultSetType, resultSetConcurrency,
469 resultSetHoldability, StatementType.CALLABLE_STATEMENT));
470 }
471
472 /**
473 * Creates or obtains a {@link PreparedStatement} from the pool.
474 *
475 * @param key
476 * a {@link PStmtKey} for the given arguments
477 * @return a {@link PoolablePreparedStatement}
478 * @throws SQLException
479 * Wraps an underlying exception.
480 */
481 private PreparedStatement prepareStatement(final PStmtKey key) throws SQLException {
482 if (null == stmtPool) {
483 throw new SQLException("Statement pool is null - closed or invalid PoolingConnection.");
484 }
485 try {
486 return stmtPool.borrowObject(key);
487 } catch (final NoSuchElementException e) {
488 throw new SQLException("MaxOpenPreparedStatements limit reached", e);
489 } catch (final RuntimeException e) {
490 throw e;
491 } catch (final Exception e) {
492 throw new SQLException("Borrow prepareStatement from pool failed", e);
493 }
494 }
495
496 /**
497 * Creates or obtains a {@link PreparedStatement} from the pool.
498 *
499 * @param sql
500 * the SQL string used to define the PreparedStatement
501 * @return a {@link PoolablePreparedStatement}
502 * @throws SQLException
503 * Wraps an underlying exception.
504 */
505 @Override
506 public PreparedStatement prepareStatement(final String sql) throws SQLException {
507 return prepareStatement(createKey(sql));
508 }
509
510 /*
511 * Creates or obtains a {@link PreparedStatement} from the pool.
512 *
513 * @param sql
514 * the SQL string used to define the PreparedStatement
515 * @param autoGeneratedKeys
516 * A flag indicating whether auto-generated keys should be returned; one of
517 * {@link Statement#RETURN_GENERATED_KEYS} or {@link Statement#NO_GENERATED_KEYS}.
518 * @return a {@link PoolablePreparedStatement}
519 * @throws SQLException
520 * Wraps an underlying exception.
521 */
522 @Override
523 public PreparedStatement prepareStatement(final String sql, final int autoGeneratedKeys) throws SQLException {
524 return prepareStatement(createKey(sql, autoGeneratedKeys));
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 * @return a {@link PoolablePreparedStatement}
537 * @throws SQLException
538 * Wraps an underlying exception.
539 */
540 @Override
541 public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency)
542 throws SQLException {
543 return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency));
544 }
545
546 /**
547 * Creates or obtains a {@link PreparedStatement} from the pool.
548 *
549 * @param sql
550 * the SQL string used to define the PreparedStatement
551 * @param resultSetType
552 * result set type
553 * @param resultSetConcurrency
554 * result set concurrency
555 * @param resultSetHoldability
556 * result set holdability
557 * @return a {@link PoolablePreparedStatement}
558 * @throws SQLException
559 * Wraps an underlying exception.
560 */
561 @Override
562 public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency,
563 final int resultSetHoldability) throws SQLException {
564 return prepareStatement(createKey(sql, resultSetType, resultSetConcurrency, resultSetHoldability));
565 }
566
567 /**
568 * Creates or obtains a {@link PreparedStatement} from the pool.
569 *
570 * @param sql
571 * the SQL string used to define the PreparedStatement
572 * @param columnIndexes
573 * An array of column indexes indicating the columns that should be returned from the inserted row or
574 * rows.
575 * @return a {@link PoolablePreparedStatement}
576 * @throws SQLException
577 * Wraps an underlying exception.
578 */
579 @Override
580 public PreparedStatement prepareStatement(final String sql, final int[] columnIndexes) throws SQLException {
581 return prepareStatement(createKey(sql, columnIndexes));
582 }
583
584 /**
585 * Creates or obtains a {@link PreparedStatement} from the pool.
586 *
587 * @param sql
588 * the SQL string used to define the PreparedStatement
589 * @param columnNames
590 * column names
591 * @return a {@link PoolablePreparedStatement}
592 * @throws SQLException
593 * Wraps an underlying exception.
594 */
595 @Override
596 public PreparedStatement prepareStatement(final String sql, final String[] columnNames) throws SQLException {
597 return prepareStatement(createKey(sql, columnNames));
598 }
599
600 /**
601 * Sets whether the pool of statements should be cleared when the connection is returned to its pool.
602 * Default is false.
603 *
604 * @param clearStatementPoolOnReturn clear or not
605 * @since 2.8.0
606 */
607 public void setClearStatementPoolOnReturn(final boolean clearStatementPoolOnReturn) {
608 this.clearStatementPoolOnReturn = clearStatementPoolOnReturn;
609 }
610
611 /**
612 * Sets the prepared statement pool.
613 *
614 * @param pool
615 * the prepared statement pool.
616 */
617 public void setStatementPool(final KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> pool) {
618 stmtPool = pool;
619 }
620
621 @Override
622 public synchronized String toString() {
623 if (stmtPool instanceof GenericKeyedObjectPool) {
624 // DBCP-596 PoolingConnection.toString() causes StackOverflowError
625 final GenericKeyedObjectPool<?, ?> gkop = (GenericKeyedObjectPool<?, ?>) stmtPool;
626 if (gkop.getFactory() == this) {
627 return "PoolingConnection: " + stmtPool.getClass() + "@" + System.identityHashCode(stmtPool);
628 }
629 }
630 return "PoolingConnection: " + Objects.toString(stmtPool);
631 }
632
633 /**
634 * {@link KeyedPooledObjectFactory} method for validating pooled statements. Currently, always returns true.
635 *
636 * @param key
637 * ignored
638 * @param pooledObject
639 * ignored
640 * @return {@code true}
641 */
642 @Override
643 public boolean validateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject) {
644 return true;
645 }
646 }