001/* 002 * Licensed to the Apache Software Foundation (ASF) under one or more 003 * contributor license agreements. See the NOTICE file distributed with 004 * this work for additional information regarding copyright ownership. 005 * The ASF licenses this file to You under the Apache License, Version 2.0 006 * (the "License"); you may not use this file except in compliance with 007 * the License. You may obtain a copy of the License at 008 * 009 * http://www.apache.org/licenses/LICENSE-2.0 010 * 011 * Unless required by applicable law or agreed to in writing, software 012 * distributed under the License is distributed on an "AS IS" BASIS, 013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 014 * See the License for the specific language governing permissions and 015 * limitations under the License. 016 */ 017 018package org.apache.commons.dbcp2; 019 020import java.sql.CallableStatement; 021import java.sql.Connection; 022import java.sql.PreparedStatement; 023import java.sql.SQLException; 024import java.util.NoSuchElementException; 025 026import org.apache.commons.pool2.KeyedObjectPool; 027import org.apache.commons.pool2.KeyedPooledObjectFactory; 028import org.apache.commons.pool2.PooledObject; 029import org.apache.commons.pool2.impl.DefaultPooledObject; 030 031/** 032 * A {@link DelegatingConnection} that pools {@link PreparedStatement}s. 033 * <p> 034 * The {@link #prepareStatement} and {@link #prepareCall} methods, rather than creating a new PreparedStatement each 035 * time, may actually pull the statement from a pool of unused statements. The {@link PreparedStatement#close} method of 036 * the returned statement doesn't actually close the statement, but rather returns it to the pool. (See 037 * {@link PoolablePreparedStatement}, {@link PoolableCallableStatement}.) 038 * </p> 039 * 040 * @see PoolablePreparedStatement 041 * @since 2.0 042 */ 043public class PoolingConnection extends DelegatingConnection<Connection> 044 implements KeyedPooledObjectFactory<PStmtKey, DelegatingPreparedStatement> { 045 046 /** 047 * Statement types. 048 * 049 * @since 2.0 protected enum. 050 * @since 2.4.0 public enum. 051 */ 052 public enum StatementType { 053 054 /** 055 * Callable statement. 056 */ 057 CALLABLE_STATEMENT, 058 059 /** 060 * Prepared statement. 061 */ 062 PREPARED_STATEMENT 063 } 064 065 /** Pool of {@link PreparedStatement}s. and {@link CallableStatement}s */ 066 private KeyedObjectPool<PStmtKey, DelegatingPreparedStatement> pstmtPool; 067 068 private boolean clearStatementPoolOnReturn; 069 070 /** 071 * Constructor. 072 * 073 * @param connection 074 * the underlying {@link Connection}. 075 */ 076 public PoolingConnection(final Connection connection) { 077 super(connection); 078 } 079 080 /** 081 * {@link KeyedPooledObjectFactory} method for activating pooled statements. 082 * 083 * @param key 084 * ignored 085 * @param pooledObject 086 * wrapped pooled statement to be activated 087 */ 088 @Override 089 public void activateObject(final PStmtKey key, final PooledObject<DelegatingPreparedStatement> pooledObject) 090 throws Exception { 091 pooledObject.getObject().activate(); 092 } 093 094 /** 095 * Closes and frees all {@link PreparedStatement}s or {@link CallableStatement}s from the pool, and close the 096 * underlying connection. 097 */ 098 @Override 099 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 PoolablePreparedStatement pps = new PoolablePreparedStatement(statement, key, pstmtPool, this); 349 return new DefaultPooledObject<>(pps); 350 } 351 final CallableStatement statement = (CallableStatement) key.createStatement(getDelegate()); 352 final 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}