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