001/* 002 * Copyright 2002-2019 the original author or authors. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * https://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017package org.springframework.jdbc.core; 018 019import java.lang.reflect.InvocationHandler; 020import java.lang.reflect.InvocationTargetException; 021import java.lang.reflect.Method; 022import java.lang.reflect.Proxy; 023import java.sql.BatchUpdateException; 024import java.sql.CallableStatement; 025import java.sql.Connection; 026import java.sql.PreparedStatement; 027import java.sql.ResultSet; 028import java.sql.SQLException; 029import java.sql.SQLWarning; 030import java.sql.Statement; 031import java.util.ArrayList; 032import java.util.Collection; 033import java.util.Collections; 034import java.util.LinkedHashMap; 035import java.util.List; 036import java.util.Map; 037import javax.sql.DataSource; 038 039import org.springframework.dao.DataAccessException; 040import org.springframework.dao.InvalidDataAccessApiUsageException; 041import org.springframework.dao.support.DataAccessUtils; 042import org.springframework.jdbc.SQLWarningException; 043import org.springframework.jdbc.datasource.ConnectionProxy; 044import org.springframework.jdbc.datasource.DataSourceUtils; 045import org.springframework.jdbc.support.JdbcAccessor; 046import org.springframework.jdbc.support.JdbcUtils; 047import org.springframework.jdbc.support.KeyHolder; 048import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor; 049import org.springframework.jdbc.support.rowset.SqlRowSet; 050import org.springframework.util.Assert; 051import org.springframework.util.LinkedCaseInsensitiveMap; 052import org.springframework.util.StringUtils; 053 054/** 055 * <b>This is the central class in the JDBC core package.</b> 056 * It simplifies the use of JDBC and helps to avoid common errors. 057 * It executes core JDBC workflow, leaving application code to provide SQL 058 * and extract results. This class executes SQL queries or updates, initiating 059 * iteration over ResultSets and catching JDBC exceptions and translating 060 * them to the generic, more informative exception hierarchy defined in the 061 * {@code org.springframework.dao} package. 062 * 063 * <p>Code using this class need only implement callback interfaces, giving 064 * them a clearly defined contract. The {@link PreparedStatementCreator} callback 065 * interface creates a prepared statement given a Connection, providing SQL and 066 * any necessary parameters. The {@link ResultSetExtractor} interface extracts 067 * values from a ResultSet. See also {@link PreparedStatementSetter} and 068 * {@link RowMapper} for two popular alternative callback interfaces. 069 * 070 * <p>Can be used within a service implementation via direct instantiation 071 * with a DataSource reference, or get prepared in an application context 072 * and given to services as bean reference. Note: The DataSource should 073 * always be configured as a bean in the application context, in the first case 074 * given to the service directly, in the second case to the prepared template. 075 * 076 * <p>Because this class is parameterizable by the callback interfaces and 077 * the {@link org.springframework.jdbc.support.SQLExceptionTranslator} 078 * interface, there should be no need to subclass it. 079 * 080 * <p>All SQL operations performed by this class are logged at debug level, 081 * using "org.springframework.jdbc.core.JdbcTemplate" as log category. 082 * 083 * <p><b>NOTE: An instance of this class is thread-safe once configured.</b> 084 * 085 * @author Rod Johnson 086 * @author Juergen Hoeller 087 * @author Thomas Risberg 088 * @since May 3, 2001 089 * @see PreparedStatementCreator 090 * @see PreparedStatementSetter 091 * @see CallableStatementCreator 092 * @see PreparedStatementCallback 093 * @see CallableStatementCallback 094 * @see ResultSetExtractor 095 * @see RowCallbackHandler 096 * @see RowMapper 097 * @see org.springframework.jdbc.support.SQLExceptionTranslator 098 */ 099public class JdbcTemplate extends JdbcAccessor implements JdbcOperations { 100 101 private static final String RETURN_RESULT_SET_PREFIX = "#result-set-"; 102 103 private static final String RETURN_UPDATE_COUNT_PREFIX = "#update-count-"; 104 105 106 /** Custom NativeJdbcExtractor */ 107 private NativeJdbcExtractor nativeJdbcExtractor; 108 109 /** If this variable is false, we will throw exceptions on SQL warnings */ 110 private boolean ignoreWarnings = true; 111 112 /** 113 * If this variable is set to a non-negative value, it will be used for setting the 114 * fetchSize property on statements used for query processing. 115 */ 116 private int fetchSize = -1; 117 118 /** 119 * If this variable is set to a non-negative value, it will be used for setting the 120 * maxRows property on statements used for query processing. 121 */ 122 private int maxRows = -1; 123 124 /** 125 * If this variable is set to a non-negative value, it will be used for setting the 126 * queryTimeout property on statements used for query processing. 127 */ 128 private int queryTimeout = -1; 129 130 /** 131 * If this variable is set to true, then all results checking will be bypassed for any 132 * callable statement processing. This can be used to avoid a bug in some older Oracle 133 * JDBC drivers like 10.1.0.2. 134 */ 135 private boolean skipResultsProcessing = false; 136 137 /** 138 * If this variable is set to true then all results from a stored procedure call 139 * that don't have a corresponding SqlOutParameter declaration will be bypassed. 140 * All other results processing will be take place unless the variable 141 * {@code skipResultsProcessing} is set to {@code true}. 142 */ 143 private boolean skipUndeclaredResults = false; 144 145 /** 146 * If this variable is set to true then execution of a CallableStatement will return 147 * the results in a Map that uses case insensitive names for the parameters. 148 */ 149 private boolean resultsMapCaseInsensitive = false; 150 151 152 /** 153 * Construct a new JdbcTemplate for bean usage. 154 * <p>Note: The DataSource has to be set before using the instance. 155 * @see #setDataSource 156 */ 157 public JdbcTemplate() { 158 } 159 160 /** 161 * Construct a new JdbcTemplate, given a DataSource to obtain connections from. 162 * <p>Note: This will not trigger initialization of the exception translator. 163 * @param dataSource the JDBC DataSource to obtain connections from 164 */ 165 public JdbcTemplate(DataSource dataSource) { 166 setDataSource(dataSource); 167 afterPropertiesSet(); 168 } 169 170 /** 171 * Construct a new JdbcTemplate, given a DataSource to obtain connections from. 172 * <p>Note: Depending on the "lazyInit" flag, initialization of the exception translator 173 * will be triggered. 174 * @param dataSource the JDBC DataSource to obtain connections from 175 * @param lazyInit whether to lazily initialize the SQLExceptionTranslator 176 */ 177 public JdbcTemplate(DataSource dataSource, boolean lazyInit) { 178 setDataSource(dataSource); 179 setLazyInit(lazyInit); 180 afterPropertiesSet(); 181 } 182 183 184 /** 185 * Set a NativeJdbcExtractor to extract native JDBC objects from wrapped handles. 186 * Useful if native Statement and/or ResultSet handles are expected for casting 187 * to database-specific implementation classes, but a connection pool that wraps 188 * JDBC objects is used (note: <i>any</i> pool will return wrapped Connections). 189 */ 190 public void setNativeJdbcExtractor(NativeJdbcExtractor extractor) { 191 this.nativeJdbcExtractor = extractor; 192 } 193 194 /** 195 * Return the current NativeJdbcExtractor implementation. 196 */ 197 public NativeJdbcExtractor getNativeJdbcExtractor() { 198 return this.nativeJdbcExtractor; 199 } 200 201 /** 202 * Set whether or not we want to ignore SQLWarnings. 203 * <p>Default is "true", swallowing and logging all warnings. Switch this flag 204 * to "false" to make the JdbcTemplate throw a SQLWarningException instead. 205 * @see java.sql.SQLWarning 206 * @see org.springframework.jdbc.SQLWarningException 207 * @see #handleWarnings 208 */ 209 public void setIgnoreWarnings(boolean ignoreWarnings) { 210 this.ignoreWarnings = ignoreWarnings; 211 } 212 213 /** 214 * Return whether or not we ignore SQLWarnings. 215 */ 216 public boolean isIgnoreWarnings() { 217 return this.ignoreWarnings; 218 } 219 220 /** 221 * Set the fetch size for this JdbcTemplate. This is important for processing large 222 * result sets: Setting this higher than the default value will increase processing 223 * speed at the cost of memory consumption; setting this lower can avoid transferring 224 * row data that will never be read by the application. 225 * <p>Default is -1, indicating to use the JDBC driver's default configuration 226 * (i.e. to not pass a specific fetch size setting on to the driver). 227 * <p>Note: As of 4.3, negative values other than -1 will get passed on to the 228 * driver, since e.g. MySQL supports special behavior for {@code Integer.MIN_VALUE}. 229 * @see java.sql.Statement#setFetchSize 230 */ 231 public void setFetchSize(int fetchSize) { 232 this.fetchSize = fetchSize; 233 } 234 235 /** 236 * Return the fetch size specified for this JdbcTemplate. 237 */ 238 public int getFetchSize() { 239 return this.fetchSize; 240 } 241 242 /** 243 * Set the maximum number of rows for this JdbcTemplate. This is important for 244 * processing subsets of large result sets, avoiding to read and hold the entire 245 * result set in the database or in the JDBC driver if we're never interested in 246 * the entire result in the first place (for example, when performing searches 247 * that might return a large number of matches). 248 * <p>Default is -1, indicating to use the JDBC driver's default configuration 249 * (i.e. to not pass a specific max rows setting on to the driver). 250 * <p>Note: As of 4.3, negative values other than -1 will get passed on to the 251 * driver, in sync with {@link #setFetchSize}'s support for special MySQL values. 252 * @see java.sql.Statement#setMaxRows 253 */ 254 public void setMaxRows(int maxRows) { 255 this.maxRows = maxRows; 256 } 257 258 /** 259 * Return the maximum number of rows specified for this JdbcTemplate. 260 */ 261 public int getMaxRows() { 262 return this.maxRows; 263 } 264 265 /** 266 * Set the query timeout for statements that this JdbcTemplate executes. 267 * <p>Default is -1, indicating to use the JDBC driver's default 268 * (i.e. to not pass a specific query timeout setting on the driver). 269 * <p>Note: Any timeout specified here will be overridden by the remaining 270 * transaction timeout when executing within a transaction that has a 271 * timeout specified at the transaction level. 272 * @see java.sql.Statement#setQueryTimeout 273 */ 274 public void setQueryTimeout(int queryTimeout) { 275 this.queryTimeout = queryTimeout; 276 } 277 278 /** 279 * Return the query timeout for statements that this JdbcTemplate executes. 280 */ 281 public int getQueryTimeout() { 282 return this.queryTimeout; 283 } 284 285 /** 286 * Set whether results processing should be skipped. Can be used to optimize callable 287 * statement processing when we know that no results are being passed back - the processing 288 * of out parameter will still take place. This can be used to avoid a bug in some older 289 * Oracle JDBC drivers like 10.1.0.2. 290 */ 291 public void setSkipResultsProcessing(boolean skipResultsProcessing) { 292 this.skipResultsProcessing = skipResultsProcessing; 293 } 294 295 /** 296 * Return whether results processing should be skipped. 297 */ 298 public boolean isSkipResultsProcessing() { 299 return this.skipResultsProcessing; 300 } 301 302 /** 303 * Set whether undeclared results should be skipped. 304 */ 305 public void setSkipUndeclaredResults(boolean skipUndeclaredResults) { 306 this.skipUndeclaredResults = skipUndeclaredResults; 307 } 308 309 /** 310 * Return whether undeclared results should be skipped. 311 */ 312 public boolean isSkipUndeclaredResults() { 313 return this.skipUndeclaredResults; 314 } 315 316 /** 317 * Set whether execution of a CallableStatement will return the results in a Map 318 * that uses case insensitive names for the parameters. 319 */ 320 public void setResultsMapCaseInsensitive(boolean resultsMapCaseInsensitive) { 321 this.resultsMapCaseInsensitive = resultsMapCaseInsensitive; 322 } 323 324 /** 325 * Return whether execution of a CallableStatement will return the results in a Map 326 * that uses case insensitive names for the parameters. 327 */ 328 public boolean isResultsMapCaseInsensitive() { 329 return this.resultsMapCaseInsensitive; 330 } 331 332 333 //------------------------------------------------------------------------- 334 // Methods dealing with a plain java.sql.Connection 335 //------------------------------------------------------------------------- 336 337 @Override 338 public <T> T execute(ConnectionCallback<T> action) throws DataAccessException { 339 Assert.notNull(action, "Callback object must not be null"); 340 341 Connection con = DataSourceUtils.getConnection(getDataSource()); 342 try { 343 Connection conToUse = con; 344 if (this.nativeJdbcExtractor != null) { 345 // Extract native JDBC Connection, castable to OracleConnection or the like. 346 conToUse = this.nativeJdbcExtractor.getNativeConnection(con); 347 } 348 else { 349 // Create close-suppressing Connection proxy, also preparing returned Statements. 350 conToUse = createConnectionProxy(con); 351 } 352 return action.doInConnection(conToUse); 353 } 354 catch (SQLException ex) { 355 // Release Connection early, to avoid potential connection pool deadlock 356 // in the case when the exception translator hasn't been initialized yet. 357 DataSourceUtils.releaseConnection(con, getDataSource()); 358 con = null; 359 throw getExceptionTranslator().translate("ConnectionCallback", getSql(action), ex); 360 } 361 finally { 362 DataSourceUtils.releaseConnection(con, getDataSource()); 363 } 364 } 365 366 /** 367 * Create a close-suppressing proxy for the given JDBC Connection. 368 * Called by the {@code execute} method. 369 * <p>The proxy also prepares returned JDBC Statements, applying 370 * statement settings such as fetch size, max rows, and query timeout. 371 * @param con the JDBC Connection to create a proxy for 372 * @return the Connection proxy 373 * @see java.sql.Connection#close() 374 * @see #execute(ConnectionCallback) 375 * @see #applyStatementSettings 376 */ 377 protected Connection createConnectionProxy(Connection con) { 378 return (Connection) Proxy.newProxyInstance( 379 ConnectionProxy.class.getClassLoader(), 380 new Class<?>[] {ConnectionProxy.class}, 381 new CloseSuppressingInvocationHandler(con)); 382 } 383 384 385 //------------------------------------------------------------------------- 386 // Methods dealing with static SQL (java.sql.Statement) 387 //------------------------------------------------------------------------- 388 389 @Override 390 public <T> T execute(StatementCallback<T> action) throws DataAccessException { 391 Assert.notNull(action, "Callback object must not be null"); 392 393 Connection con = DataSourceUtils.getConnection(getDataSource()); 394 Statement stmt = null; 395 try { 396 Connection conToUse = con; 397 if (this.nativeJdbcExtractor != null && 398 this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) { 399 conToUse = this.nativeJdbcExtractor.getNativeConnection(con); 400 } 401 stmt = conToUse.createStatement(); 402 applyStatementSettings(stmt); 403 Statement stmtToUse = stmt; 404 if (this.nativeJdbcExtractor != null) { 405 stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt); 406 } 407 T result = action.doInStatement(stmtToUse); 408 handleWarnings(stmt); 409 return result; 410 } 411 catch (SQLException ex) { 412 // Release Connection early, to avoid potential connection pool deadlock 413 // in the case when the exception translator hasn't been initialized yet. 414 JdbcUtils.closeStatement(stmt); 415 stmt = null; 416 DataSourceUtils.releaseConnection(con, getDataSource()); 417 con = null; 418 throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex); 419 } 420 finally { 421 JdbcUtils.closeStatement(stmt); 422 DataSourceUtils.releaseConnection(con, getDataSource()); 423 } 424 } 425 426 @Override 427 public void execute(final String sql) throws DataAccessException { 428 if (logger.isDebugEnabled()) { 429 logger.debug("Executing SQL statement [" + sql + "]"); 430 } 431 432 class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider { 433 @Override 434 public Object doInStatement(Statement stmt) throws SQLException { 435 stmt.execute(sql); 436 return null; 437 } 438 @Override 439 public String getSql() { 440 return sql; 441 } 442 } 443 444 execute(new ExecuteStatementCallback()); 445 } 446 447 @Override 448 public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException { 449 Assert.notNull(sql, "SQL must not be null"); 450 Assert.notNull(rse, "ResultSetExtractor must not be null"); 451 if (logger.isDebugEnabled()) { 452 logger.debug("Executing SQL query [" + sql + "]"); 453 } 454 455 class QueryStatementCallback implements StatementCallback<T>, SqlProvider { 456 @Override 457 public T doInStatement(Statement stmt) throws SQLException { 458 ResultSet rs = null; 459 try { 460 rs = stmt.executeQuery(sql); 461 ResultSet rsToUse = rs; 462 if (nativeJdbcExtractor != null) { 463 rsToUse = nativeJdbcExtractor.getNativeResultSet(rs); 464 } 465 return rse.extractData(rsToUse); 466 } 467 finally { 468 JdbcUtils.closeResultSet(rs); 469 } 470 } 471 @Override 472 public String getSql() { 473 return sql; 474 } 475 } 476 477 return execute(new QueryStatementCallback()); 478 } 479 480 @Override 481 public void query(String sql, RowCallbackHandler rch) throws DataAccessException { 482 query(sql, new RowCallbackHandlerResultSetExtractor(rch)); 483 } 484 485 @Override 486 public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException { 487 return query(sql, new RowMapperResultSetExtractor<T>(rowMapper)); 488 } 489 490 @Override 491 public Map<String, Object> queryForMap(String sql) throws DataAccessException { 492 return queryForObject(sql, getColumnMapRowMapper()); 493 } 494 495 @Override 496 public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException { 497 List<T> results = query(sql, rowMapper); 498 return DataAccessUtils.requiredSingleResult(results); 499 } 500 501 @Override 502 public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException { 503 return queryForObject(sql, getSingleColumnRowMapper(requiredType)); 504 } 505 506 @Override 507 public <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException { 508 return query(sql, getSingleColumnRowMapper(elementType)); 509 } 510 511 @Override 512 public List<Map<String, Object>> queryForList(String sql) throws DataAccessException { 513 return query(sql, getColumnMapRowMapper()); 514 } 515 516 @Override 517 public SqlRowSet queryForRowSet(String sql) throws DataAccessException { 518 return query(sql, new SqlRowSetResultSetExtractor()); 519 } 520 521 @Override 522 public int update(final String sql) throws DataAccessException { 523 Assert.notNull(sql, "SQL must not be null"); 524 if (logger.isDebugEnabled()) { 525 logger.debug("Executing SQL update [" + sql + "]"); 526 } 527 528 class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider { 529 @Override 530 public Integer doInStatement(Statement stmt) throws SQLException { 531 int rows = stmt.executeUpdate(sql); 532 if (logger.isDebugEnabled()) { 533 logger.debug("SQL update affected " + rows + " rows"); 534 } 535 return rows; 536 } 537 @Override 538 public String getSql() { 539 return sql; 540 } 541 } 542 543 return execute(new UpdateStatementCallback()); 544 } 545 546 @Override 547 public int[] batchUpdate(final String... sql) throws DataAccessException { 548 Assert.notEmpty(sql, "SQL array must not be empty"); 549 if (logger.isDebugEnabled()) { 550 logger.debug("Executing SQL batch update of " + sql.length + " statements"); 551 } 552 553 class BatchUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider { 554 555 private String currSql; 556 557 @Override 558 public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException { 559 int[] rowsAffected = new int[sql.length]; 560 if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) { 561 for (String sqlStmt : sql) { 562 this.currSql = appendSql(this.currSql, sqlStmt); 563 stmt.addBatch(sqlStmt); 564 } 565 try { 566 rowsAffected = stmt.executeBatch(); 567 } 568 catch (BatchUpdateException ex) { 569 String batchExceptionSql = null; 570 for (int i = 0; i < ex.getUpdateCounts().length; i++) { 571 if (ex.getUpdateCounts()[i] == Statement.EXECUTE_FAILED) { 572 batchExceptionSql = appendSql(batchExceptionSql, sql[i]); 573 } 574 } 575 if (StringUtils.hasLength(batchExceptionSql)) { 576 this.currSql = batchExceptionSql; 577 } 578 throw ex; 579 } 580 } 581 else { 582 for (int i = 0; i < sql.length; i++) { 583 this.currSql = sql[i]; 584 if (!stmt.execute(sql[i])) { 585 rowsAffected[i] = stmt.getUpdateCount(); 586 } 587 else { 588 throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]); 589 } 590 } 591 } 592 return rowsAffected; 593 } 594 595 private String appendSql(String sql, String statement) { 596 return (StringUtils.isEmpty(sql) ? statement : sql + "; " + statement); 597 } 598 599 @Override 600 public String getSql() { 601 return this.currSql; 602 } 603 } 604 605 return execute(new BatchUpdateStatementCallback()); 606 } 607 608 609 //------------------------------------------------------------------------- 610 // Methods dealing with prepared statements 611 //------------------------------------------------------------------------- 612 613 @Override 614 public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action) 615 throws DataAccessException { 616 617 Assert.notNull(psc, "PreparedStatementCreator must not be null"); 618 Assert.notNull(action, "Callback object must not be null"); 619 if (logger.isDebugEnabled()) { 620 String sql = getSql(psc); 621 logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : "")); 622 } 623 624 Connection con = DataSourceUtils.getConnection(getDataSource()); 625 PreparedStatement ps = null; 626 try { 627 Connection conToUse = con; 628 if (this.nativeJdbcExtractor != null && 629 this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) { 630 conToUse = this.nativeJdbcExtractor.getNativeConnection(con); 631 } 632 ps = psc.createPreparedStatement(conToUse); 633 applyStatementSettings(ps); 634 PreparedStatement psToUse = ps; 635 if (this.nativeJdbcExtractor != null) { 636 psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps); 637 } 638 T result = action.doInPreparedStatement(psToUse); 639 handleWarnings(ps); 640 return result; 641 } 642 catch (SQLException ex) { 643 // Release Connection early, to avoid potential connection pool deadlock 644 // in the case when the exception translator hasn't been initialized yet. 645 if (psc instanceof ParameterDisposer) { 646 ((ParameterDisposer) psc).cleanupParameters(); 647 } 648 String sql = getSql(psc); 649 psc = null; 650 JdbcUtils.closeStatement(ps); 651 ps = null; 652 DataSourceUtils.releaseConnection(con, getDataSource()); 653 con = null; 654 throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex); 655 } 656 finally { 657 if (psc instanceof ParameterDisposer) { 658 ((ParameterDisposer) psc).cleanupParameters(); 659 } 660 JdbcUtils.closeStatement(ps); 661 DataSourceUtils.releaseConnection(con, getDataSource()); 662 } 663 } 664 665 @Override 666 public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException { 667 return execute(new SimplePreparedStatementCreator(sql), action); 668 } 669 670 /** 671 * Query using a prepared statement, allowing for a PreparedStatementCreator 672 * and a PreparedStatementSetter. Most other query methods use this method, 673 * but application code will always work with either a creator or a setter. 674 * @param psc a callback that creates a PreparedStatement given a Connection 675 * @param pss a callback that knows how to set values on the prepared statement. 676 * If this is {@code null}, the SQL will be assumed to contain no bind parameters. 677 * @param rse a callback that will extract results 678 * @return an arbitrary result object, as returned by the ResultSetExtractor 679 * @throws DataAccessException if there is any problem 680 */ 681 public <T> T query( 682 PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse) 683 throws DataAccessException { 684 685 Assert.notNull(rse, "ResultSetExtractor must not be null"); 686 logger.debug("Executing prepared SQL query"); 687 688 return execute(psc, new PreparedStatementCallback<T>() { 689 @Override 690 public T doInPreparedStatement(PreparedStatement ps) throws SQLException { 691 ResultSet rs = null; 692 try { 693 if (pss != null) { 694 pss.setValues(ps); 695 } 696 rs = ps.executeQuery(); 697 ResultSet rsToUse = rs; 698 if (nativeJdbcExtractor != null) { 699 rsToUse = nativeJdbcExtractor.getNativeResultSet(rs); 700 } 701 return rse.extractData(rsToUse); 702 } 703 finally { 704 JdbcUtils.closeResultSet(rs); 705 if (pss instanceof ParameterDisposer) { 706 ((ParameterDisposer) pss).cleanupParameters(); 707 } 708 } 709 } 710 }); 711 } 712 713 @Override 714 public <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException { 715 return query(psc, null, rse); 716 } 717 718 @Override 719 public <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException { 720 return query(new SimplePreparedStatementCreator(sql), pss, rse); 721 } 722 723 @Override 724 public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException { 725 return query(sql, newArgTypePreparedStatementSetter(args, argTypes), rse); 726 } 727 728 @Override 729 public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException { 730 return query(sql, newArgPreparedStatementSetter(args), rse); 731 } 732 733 @Override 734 public <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) throws DataAccessException { 735 return query(sql, newArgPreparedStatementSetter(args), rse); 736 } 737 738 @Override 739 public void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException { 740 query(psc, new RowCallbackHandlerResultSetExtractor(rch)); 741 } 742 743 @Override 744 public void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException { 745 query(sql, pss, new RowCallbackHandlerResultSetExtractor(rch)); 746 } 747 748 @Override 749 public void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException { 750 query(sql, newArgTypePreparedStatementSetter(args, argTypes), rch); 751 } 752 753 @Override 754 public void query(String sql, Object[] args, RowCallbackHandler rch) throws DataAccessException { 755 query(sql, newArgPreparedStatementSetter(args), rch); 756 } 757 758 @Override 759 public void query(String sql, RowCallbackHandler rch, Object... args) throws DataAccessException { 760 query(sql, newArgPreparedStatementSetter(args), rch); 761 } 762 763 @Override 764 public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException { 765 return query(psc, new RowMapperResultSetExtractor<T>(rowMapper)); 766 } 767 768 @Override 769 public <T> List<T> query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException { 770 return query(sql, pss, new RowMapperResultSetExtractor<T>(rowMapper)); 771 } 772 773 @Override 774 public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException { 775 return query(sql, args, argTypes, new RowMapperResultSetExtractor<T>(rowMapper)); 776 } 777 778 @Override 779 public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException { 780 return query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper)); 781 } 782 783 @Override 784 public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException { 785 return query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper)); 786 } 787 788 @Override 789 public <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) 790 throws DataAccessException { 791 792 List<T> results = query(sql, args, argTypes, new RowMapperResultSetExtractor<T>(rowMapper, 1)); 793 return DataAccessUtils.requiredSingleResult(results); 794 } 795 796 @Override 797 public <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException { 798 List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1)); 799 return DataAccessUtils.requiredSingleResult(results); 800 } 801 802 @Override 803 public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException { 804 List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1)); 805 return DataAccessUtils.requiredSingleResult(results); 806 } 807 808 @Override 809 public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType) 810 throws DataAccessException { 811 812 return queryForObject(sql, args, argTypes, getSingleColumnRowMapper(requiredType)); 813 } 814 815 @Override 816 public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException { 817 return queryForObject(sql, args, getSingleColumnRowMapper(requiredType)); 818 } 819 820 @Override 821 public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException { 822 return queryForObject(sql, args, getSingleColumnRowMapper(requiredType)); 823 } 824 825 @Override 826 public Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException { 827 return queryForObject(sql, args, argTypes, getColumnMapRowMapper()); 828 } 829 830 @Override 831 public Map<String, Object> queryForMap(String sql, Object... args) throws DataAccessException { 832 return queryForObject(sql, args, getColumnMapRowMapper()); 833 } 834 835 @Override 836 public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) throws DataAccessException { 837 return query(sql, args, argTypes, getSingleColumnRowMapper(elementType)); 838 } 839 840 @Override 841 public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType) throws DataAccessException { 842 return query(sql, args, getSingleColumnRowMapper(elementType)); 843 } 844 845 @Override 846 public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args) throws DataAccessException { 847 return query(sql, args, getSingleColumnRowMapper(elementType)); 848 } 849 850 @Override 851 public List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException { 852 return query(sql, args, argTypes, getColumnMapRowMapper()); 853 } 854 855 @Override 856 public List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException { 857 return query(sql, args, getColumnMapRowMapper()); 858 } 859 860 @Override 861 public SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException { 862 return query(sql, args, argTypes, new SqlRowSetResultSetExtractor()); 863 } 864 865 @Override 866 public SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException { 867 return query(sql, args, new SqlRowSetResultSetExtractor()); 868 } 869 870 protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss) 871 throws DataAccessException { 872 873 logger.debug("Executing prepared SQL update"); 874 875 return execute(psc, new PreparedStatementCallback<Integer>() { 876 @Override 877 public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException { 878 try { 879 if (pss != null) { 880 pss.setValues(ps); 881 } 882 int rows = ps.executeUpdate(); 883 if (logger.isDebugEnabled()) { 884 logger.debug("SQL update affected " + rows + " rows"); 885 } 886 return rows; 887 } 888 finally { 889 if (pss instanceof ParameterDisposer) { 890 ((ParameterDisposer) pss).cleanupParameters(); 891 } 892 } 893 } 894 }); 895 } 896 897 @Override 898 public int update(PreparedStatementCreator psc) throws DataAccessException { 899 return update(psc, (PreparedStatementSetter) null); 900 } 901 902 @Override 903 public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder) 904 throws DataAccessException { 905 906 Assert.notNull(generatedKeyHolder, "KeyHolder must not be null"); 907 logger.debug("Executing SQL update and returning generated keys"); 908 909 return execute(psc, new PreparedStatementCallback<Integer>() { 910 @Override 911 public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException { 912 int rows = ps.executeUpdate(); 913 List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList(); 914 generatedKeys.clear(); 915 ResultSet keys = ps.getGeneratedKeys(); 916 if (keys != null) { 917 try { 918 RowMapperResultSetExtractor<Map<String, Object>> rse = 919 new RowMapperResultSetExtractor<Map<String, Object>>(getColumnMapRowMapper(), 1); 920 generatedKeys.addAll(rse.extractData(keys)); 921 } 922 finally { 923 JdbcUtils.closeResultSet(keys); 924 } 925 } 926 if (logger.isDebugEnabled()) { 927 logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys"); 928 } 929 return rows; 930 } 931 }); 932 } 933 934 @Override 935 public int update(String sql, PreparedStatementSetter pss) throws DataAccessException { 936 return update(new SimplePreparedStatementCreator(sql), pss); 937 } 938 939 @Override 940 public int update(String sql, Object[] args, int[] argTypes) throws DataAccessException { 941 return update(sql, newArgTypePreparedStatementSetter(args, argTypes)); 942 } 943 944 @Override 945 public int update(String sql, Object... args) throws DataAccessException { 946 return update(sql, newArgPreparedStatementSetter(args)); 947 } 948 949 @Override 950 public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException { 951 if (logger.isDebugEnabled()) { 952 logger.debug("Executing SQL batch update [" + sql + "]"); 953 } 954 955 return execute(sql, new PreparedStatementCallback<int[]>() { 956 @Override 957 public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException { 958 try { 959 int batchSize = pss.getBatchSize(); 960 InterruptibleBatchPreparedStatementSetter ipss = 961 (pss instanceof InterruptibleBatchPreparedStatementSetter ? 962 (InterruptibleBatchPreparedStatementSetter) pss : null); 963 if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) { 964 for (int i = 0; i < batchSize; i++) { 965 pss.setValues(ps, i); 966 if (ipss != null && ipss.isBatchExhausted(i)) { 967 break; 968 } 969 ps.addBatch(); 970 } 971 return ps.executeBatch(); 972 } 973 else { 974 List<Integer> rowsAffected = new ArrayList<Integer>(); 975 for (int i = 0; i < batchSize; i++) { 976 pss.setValues(ps, i); 977 if (ipss != null && ipss.isBatchExhausted(i)) { 978 break; 979 } 980 rowsAffected.add(ps.executeUpdate()); 981 } 982 int[] rowsAffectedArray = new int[rowsAffected.size()]; 983 for (int i = 0; i < rowsAffectedArray.length; i++) { 984 rowsAffectedArray[i] = rowsAffected.get(i); 985 } 986 return rowsAffectedArray; 987 } 988 } 989 finally { 990 if (pss instanceof ParameterDisposer) { 991 ((ParameterDisposer) pss).cleanupParameters(); 992 } 993 } 994 } 995 }); 996 } 997 998 @Override 999 public int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException { 1000 return batchUpdate(sql, batchArgs, new int[0]); 1001 } 1002 1003 @Override 1004 public int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException { 1005 return BatchUpdateUtils.executeBatchUpdate(sql, batchArgs, argTypes, this); 1006 } 1007 1008 @Override 1009 public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, 1010 final ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException { 1011 1012 if (logger.isDebugEnabled()) { 1013 logger.debug("Executing SQL batch update [" + sql + "] with a batch size of " + batchSize); 1014 } 1015 return execute(sql, new PreparedStatementCallback<int[][]>() { 1016 @Override 1017 public int[][] doInPreparedStatement(PreparedStatement ps) throws SQLException { 1018 List<int[]> rowsAffected = new ArrayList<int[]>(); 1019 try { 1020 boolean batchSupported = JdbcUtils.supportsBatchUpdates(ps.getConnection()); 1021 int n = 0; 1022 for (T obj : batchArgs) { 1023 pss.setValues(ps, obj); 1024 n++; 1025 if (batchSupported) { 1026 ps.addBatch(); 1027 if (n % batchSize == 0 || n == batchArgs.size()) { 1028 if (logger.isDebugEnabled()) { 1029 int batchIdx = (n % batchSize == 0) ? n / batchSize : (n / batchSize) + 1; 1030 int items = n - ((n % batchSize == 0) ? n / batchSize - 1 : (n / batchSize)) * batchSize; 1031 logger.debug("Sending SQL batch update #" + batchIdx + " with " + items + " items"); 1032 } 1033 rowsAffected.add(ps.executeBatch()); 1034 } 1035 } 1036 else { 1037 int i = ps.executeUpdate(); 1038 rowsAffected.add(new int[] {i}); 1039 } 1040 } 1041 int[][] result = new int[rowsAffected.size()][]; 1042 for (int i = 0; i < result.length; i++) { 1043 result[i] = rowsAffected.get(i); 1044 } 1045 return result; 1046 } 1047 finally { 1048 if (pss instanceof ParameterDisposer) { 1049 ((ParameterDisposer) pss).cleanupParameters(); 1050 } 1051 } 1052 } 1053 }); 1054 } 1055 1056 1057 //------------------------------------------------------------------------- 1058 // Methods dealing with callable statements 1059 //------------------------------------------------------------------------- 1060 1061 @Override 1062 public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) 1063 throws DataAccessException { 1064 1065 Assert.notNull(csc, "CallableStatementCreator must not be null"); 1066 Assert.notNull(action, "Callback object must not be null"); 1067 if (logger.isDebugEnabled()) { 1068 String sql = getSql(csc); 1069 logger.debug("Calling stored procedure" + (sql != null ? " [" + sql + "]" : "")); 1070 } 1071 1072 Connection con = DataSourceUtils.getConnection(getDataSource()); 1073 CallableStatement cs = null; 1074 try { 1075 Connection conToUse = con; 1076 if (this.nativeJdbcExtractor != null) { 1077 conToUse = this.nativeJdbcExtractor.getNativeConnection(con); 1078 } 1079 cs = csc.createCallableStatement(conToUse); 1080 applyStatementSettings(cs); 1081 CallableStatement csToUse = cs; 1082 if (this.nativeJdbcExtractor != null) { 1083 csToUse = this.nativeJdbcExtractor.getNativeCallableStatement(cs); 1084 } 1085 T result = action.doInCallableStatement(csToUse); 1086 handleWarnings(cs); 1087 return result; 1088 } 1089 catch (SQLException ex) { 1090 // Release Connection early, to avoid potential connection pool deadlock 1091 // in the case when the exception translator hasn't been initialized yet. 1092 if (csc instanceof ParameterDisposer) { 1093 ((ParameterDisposer) csc).cleanupParameters(); 1094 } 1095 String sql = getSql(csc); 1096 csc = null; 1097 JdbcUtils.closeStatement(cs); 1098 cs = null; 1099 DataSourceUtils.releaseConnection(con, getDataSource()); 1100 con = null; 1101 throw getExceptionTranslator().translate("CallableStatementCallback", sql, ex); 1102 } 1103 finally { 1104 if (csc instanceof ParameterDisposer) { 1105 ((ParameterDisposer) csc).cleanupParameters(); 1106 } 1107 JdbcUtils.closeStatement(cs); 1108 DataSourceUtils.releaseConnection(con, getDataSource()); 1109 } 1110 } 1111 1112 @Override 1113 public <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException { 1114 return execute(new SimpleCallableStatementCreator(callString), action); 1115 } 1116 1117 @Override 1118 public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters) 1119 throws DataAccessException { 1120 1121 final List<SqlParameter> updateCountParameters = new ArrayList<SqlParameter>(); 1122 final List<SqlParameter> resultSetParameters = new ArrayList<SqlParameter>(); 1123 final List<SqlParameter> callParameters = new ArrayList<SqlParameter>(); 1124 1125 for (SqlParameter parameter : declaredParameters) { 1126 if (parameter.isResultsParameter()) { 1127 if (parameter instanceof SqlReturnResultSet) { 1128 resultSetParameters.add(parameter); 1129 } 1130 else { 1131 updateCountParameters.add(parameter); 1132 } 1133 } 1134 else { 1135 callParameters.add(parameter); 1136 } 1137 } 1138 1139 return execute(csc, new CallableStatementCallback<Map<String, Object>>() { 1140 @Override 1141 public Map<String, Object> doInCallableStatement(CallableStatement cs) throws SQLException { 1142 boolean retVal = cs.execute(); 1143 int updateCount = cs.getUpdateCount(); 1144 if (logger.isDebugEnabled()) { 1145 logger.debug("CallableStatement.execute() returned '" + retVal + "'"); 1146 logger.debug("CallableStatement.getUpdateCount() returned " + updateCount); 1147 } 1148 Map<String, Object> resultsMap = createResultsMap(); 1149 if (retVal || updateCount != -1) { 1150 resultsMap.putAll(extractReturnedResults(cs, updateCountParameters, resultSetParameters, updateCount)); 1151 } 1152 resultsMap.putAll(extractOutputParameters(cs, callParameters)); 1153 return resultsMap; 1154 } 1155 }); 1156 } 1157 1158 /** 1159 * Extract returned ResultSets from the completed stored procedure. 1160 * @param cs a JDBC wrapper for the stored procedure 1161 * @param updateCountParameters the parameter list of declared update count parameters for the stored procedure 1162 * @param resultSetParameters the parameter list of declared resultSet parameters for the stored procedure 1163 * @return a Map that contains returned results 1164 */ 1165 protected Map<String, Object> extractReturnedResults(CallableStatement cs, 1166 List<SqlParameter> updateCountParameters, List<SqlParameter> resultSetParameters, int updateCount) 1167 throws SQLException { 1168 1169 Map<String, Object> results = new LinkedHashMap<String, Object>(4); 1170 int rsIndex = 0; 1171 int updateIndex = 0; 1172 boolean moreResults; 1173 if (!this.skipResultsProcessing) { 1174 do { 1175 if (updateCount == -1) { 1176 if (resultSetParameters != null && resultSetParameters.size() > rsIndex) { 1177 SqlReturnResultSet declaredRsParam = (SqlReturnResultSet) resultSetParameters.get(rsIndex); 1178 results.putAll(processResultSet(cs.getResultSet(), declaredRsParam)); 1179 rsIndex++; 1180 } 1181 else { 1182 if (!this.skipUndeclaredResults) { 1183 String rsName = RETURN_RESULT_SET_PREFIX + (rsIndex + 1); 1184 SqlReturnResultSet undeclaredRsParam = new SqlReturnResultSet(rsName, getColumnMapRowMapper()); 1185 if (logger.isDebugEnabled()) { 1186 logger.debug("Added default SqlReturnResultSet parameter named '" + rsName + "'"); 1187 } 1188 results.putAll(processResultSet(cs.getResultSet(), undeclaredRsParam)); 1189 rsIndex++; 1190 } 1191 } 1192 } 1193 else { 1194 if (updateCountParameters != null && updateCountParameters.size() > updateIndex) { 1195 SqlReturnUpdateCount ucParam = (SqlReturnUpdateCount) updateCountParameters.get(updateIndex); 1196 String declaredUcName = ucParam.getName(); 1197 results.put(declaredUcName, updateCount); 1198 updateIndex++; 1199 } 1200 else { 1201 if (!this.skipUndeclaredResults) { 1202 String undeclaredName = RETURN_UPDATE_COUNT_PREFIX + (updateIndex + 1); 1203 if (logger.isDebugEnabled()) { 1204 logger.debug("Added default SqlReturnUpdateCount parameter named '" + undeclaredName + "'"); 1205 } 1206 results.put(undeclaredName, updateCount); 1207 updateIndex++; 1208 } 1209 } 1210 } 1211 moreResults = cs.getMoreResults(); 1212 updateCount = cs.getUpdateCount(); 1213 if (logger.isDebugEnabled()) { 1214 logger.debug("CallableStatement.getUpdateCount() returned " + updateCount); 1215 } 1216 } 1217 while (moreResults || updateCount != -1); 1218 } 1219 return results; 1220 } 1221 1222 /** 1223 * Extract output parameters from the completed stored procedure. 1224 * @param cs the JDBC wrapper for the stored procedure 1225 * @param parameters parameter list for the stored procedure 1226 * @return a Map that contains returned results 1227 */ 1228 protected Map<String, Object> extractOutputParameters(CallableStatement cs, List<SqlParameter> parameters) 1229 throws SQLException { 1230 1231 Map<String, Object> results = new LinkedHashMap<String, Object>(parameters.size()); 1232 int sqlColIndex = 1; 1233 for (SqlParameter param : parameters) { 1234 if (param instanceof SqlOutParameter) { 1235 SqlOutParameter outParam = (SqlOutParameter) param; 1236 if (outParam.isReturnTypeSupported()) { 1237 Object out = outParam.getSqlReturnType().getTypeValue( 1238 cs, sqlColIndex, outParam.getSqlType(), outParam.getTypeName()); 1239 results.put(outParam.getName(), out); 1240 } 1241 else { 1242 Object out = cs.getObject(sqlColIndex); 1243 if (out instanceof ResultSet) { 1244 if (outParam.isResultSetSupported()) { 1245 results.putAll(processResultSet((ResultSet) out, outParam)); 1246 } 1247 else { 1248 String rsName = outParam.getName(); 1249 SqlReturnResultSet rsParam = new SqlReturnResultSet(rsName, getColumnMapRowMapper()); 1250 results.putAll(processResultSet((ResultSet) out, rsParam)); 1251 if (logger.isDebugEnabled()) { 1252 logger.debug("Added default SqlReturnResultSet parameter named '" + rsName + "'"); 1253 } 1254 } 1255 } 1256 else { 1257 results.put(outParam.getName(), out); 1258 } 1259 } 1260 } 1261 if (!(param.isResultsParameter())) { 1262 sqlColIndex++; 1263 } 1264 } 1265 return results; 1266 } 1267 1268 /** 1269 * Process the given ResultSet from a stored procedure. 1270 * @param rs the ResultSet to process 1271 * @param param the corresponding stored procedure parameter 1272 * @return a Map that contains returned results 1273 */ 1274 @SuppressWarnings({"unchecked", "rawtypes"}) 1275 protected Map<String, Object> processResultSet(ResultSet rs, ResultSetSupportingSqlParameter param) 1276 throws SQLException { 1277 1278 if (rs != null) { 1279 try { 1280 ResultSet rsToUse = rs; 1281 if (this.nativeJdbcExtractor != null) { 1282 rsToUse = this.nativeJdbcExtractor.getNativeResultSet(rs); 1283 } 1284 if (param.getRowMapper() != null) { 1285 RowMapper rowMapper = param.getRowMapper(); 1286 Object data = (new RowMapperResultSetExtractor(rowMapper)).extractData(rsToUse); 1287 return Collections.singletonMap(param.getName(), data); 1288 } 1289 else if (param.getRowCallbackHandler() != null) { 1290 RowCallbackHandler rch = param.getRowCallbackHandler(); 1291 (new RowCallbackHandlerResultSetExtractor(rch)).extractData(rsToUse); 1292 return Collections.singletonMap(param.getName(), 1293 (Object) "ResultSet returned from stored procedure was processed"); 1294 } 1295 else if (param.getResultSetExtractor() != null) { 1296 Object data = param.getResultSetExtractor().extractData(rsToUse); 1297 return Collections.singletonMap(param.getName(), data); 1298 } 1299 } 1300 finally { 1301 JdbcUtils.closeResultSet(rs); 1302 } 1303 } 1304 return Collections.emptyMap(); 1305 } 1306 1307 1308 //------------------------------------------------------------------------- 1309 // Implementation hooks and helper methods 1310 //------------------------------------------------------------------------- 1311 1312 /** 1313 * Create a new RowMapper for reading columns as key-value pairs. 1314 * @return the RowMapper to use 1315 * @see ColumnMapRowMapper 1316 */ 1317 protected RowMapper<Map<String, Object>> getColumnMapRowMapper() { 1318 return new ColumnMapRowMapper(); 1319 } 1320 1321 /** 1322 * Create a new RowMapper for reading result objects from a single column. 1323 * @param requiredType the type that each result object is expected to match 1324 * @return the RowMapper to use 1325 * @see SingleColumnRowMapper 1326 */ 1327 protected <T> RowMapper<T> getSingleColumnRowMapper(Class<T> requiredType) { 1328 return new SingleColumnRowMapper<T>(requiredType); 1329 } 1330 1331 /** 1332 * Create a Map instance to be used as the results map. 1333 * <p>If {@link #resultsMapCaseInsensitive} has been set to true, 1334 * a {@link LinkedCaseInsensitiveMap} will be created; otherwise, a 1335 * {@link LinkedHashMap} will be created. 1336 * @return the results Map instance 1337 * @see #setResultsMapCaseInsensitive 1338 * @see #isResultsMapCaseInsensitive 1339 */ 1340 protected Map<String, Object> createResultsMap() { 1341 if (isResultsMapCaseInsensitive()) { 1342 return new LinkedCaseInsensitiveMap<Object>(); 1343 } 1344 else { 1345 return new LinkedHashMap<String, Object>(); 1346 } 1347 } 1348 1349 /** 1350 * Prepare the given JDBC Statement (or PreparedStatement or CallableStatement), 1351 * applying statement settings such as fetch size, max rows, and query timeout. 1352 * @param stmt the JDBC Statement to prepare 1353 * @throws SQLException if thrown by JDBC API 1354 * @see #setFetchSize 1355 * @see #setMaxRows 1356 * @see #setQueryTimeout 1357 * @see org.springframework.jdbc.datasource.DataSourceUtils#applyTransactionTimeout 1358 */ 1359 protected void applyStatementSettings(Statement stmt) throws SQLException { 1360 int fetchSize = getFetchSize(); 1361 if (fetchSize != -1) { 1362 stmt.setFetchSize(fetchSize); 1363 } 1364 int maxRows = getMaxRows(); 1365 if (maxRows != -1) { 1366 stmt.setMaxRows(maxRows); 1367 } 1368 DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout()); 1369 } 1370 1371 /** 1372 * Create a new arg-based PreparedStatementSetter using the args passed in. 1373 * <p>By default, we'll create an {@link ArgumentPreparedStatementSetter}. 1374 * This method allows for the creation to be overridden by subclasses. 1375 * @param args object array with arguments 1376 * @return the new PreparedStatementSetter to use 1377 */ 1378 protected PreparedStatementSetter newArgPreparedStatementSetter(Object[] args) { 1379 return new ArgumentPreparedStatementSetter(args); 1380 } 1381 1382 /** 1383 * Create a new arg-type-based PreparedStatementSetter using the args and types passed in. 1384 * <p>By default, we'll create an {@link ArgumentTypePreparedStatementSetter}. 1385 * This method allows for the creation to be overridden by subclasses. 1386 * @param args object array with arguments 1387 * @param argTypes int array of SQLTypes for the associated arguments 1388 * @return the new PreparedStatementSetter to use 1389 */ 1390 protected PreparedStatementSetter newArgTypePreparedStatementSetter(Object[] args, int[] argTypes) { 1391 return new ArgumentTypePreparedStatementSetter(args, argTypes); 1392 } 1393 1394 /** 1395 * Throw a SQLWarningException if we're not ignoring warnings, 1396 * otherwise log the warnings at debug level. 1397 * @param stmt the current JDBC statement 1398 * @throws SQLWarningException if not ignoring warnings 1399 * @see org.springframework.jdbc.SQLWarningException 1400 */ 1401 protected void handleWarnings(Statement stmt) throws SQLException { 1402 if (isIgnoreWarnings()) { 1403 if (logger.isDebugEnabled()) { 1404 SQLWarning warningToLog = stmt.getWarnings(); 1405 while (warningToLog != null) { 1406 logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + "', error code '" + 1407 warningToLog.getErrorCode() + "', message [" + warningToLog.getMessage() + "]"); 1408 warningToLog = warningToLog.getNextWarning(); 1409 } 1410 } 1411 } 1412 else { 1413 handleWarnings(stmt.getWarnings()); 1414 } 1415 } 1416 1417 /** 1418 * Throw a SQLWarningException if encountering an actual warning. 1419 * @param warning the warnings object from the current statement. 1420 * May be {@code null}, in which case this method does nothing. 1421 * @throws SQLWarningException in case of an actual warning to be raised 1422 */ 1423 protected void handleWarnings(SQLWarning warning) throws SQLWarningException { 1424 if (warning != null) { 1425 throw new SQLWarningException("Warning not ignored", warning); 1426 } 1427 } 1428 1429 /** 1430 * Determine SQL from potential provider object. 1431 * @param sqlProvider object which is potentially a SqlProvider 1432 * @return the SQL string, or {@code null} if not known 1433 * @see SqlProvider 1434 */ 1435 private static String getSql(Object sqlProvider) { 1436 if (sqlProvider instanceof SqlProvider) { 1437 return ((SqlProvider) sqlProvider).getSql(); 1438 } 1439 else { 1440 return null; 1441 } 1442 } 1443 1444 1445 /** 1446 * Invocation handler that suppresses close calls on JDBC Connections. 1447 * Also prepares returned Statement (Prepared/CallbackStatement) objects. 1448 * @see java.sql.Connection#close() 1449 */ 1450 private class CloseSuppressingInvocationHandler implements InvocationHandler { 1451 1452 private final Connection target; 1453 1454 public CloseSuppressingInvocationHandler(Connection target) { 1455 this.target = target; 1456 } 1457 1458 @Override 1459 public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { 1460 // Invocation on ConnectionProxy interface coming in... 1461 1462 if (method.getName().equals("equals")) { 1463 // Only consider equal when proxies are identical. 1464 return (proxy == args[0]); 1465 } 1466 else if (method.getName().equals("hashCode")) { 1467 // Use hashCode of PersistenceManager proxy. 1468 return System.identityHashCode(proxy); 1469 } 1470 else if (method.getName().equals("unwrap")) { 1471 if (((Class<?>) args[0]).isInstance(proxy)) { 1472 return proxy; 1473 } 1474 } 1475 else if (method.getName().equals("isWrapperFor")) { 1476 if (((Class<?>) args[0]).isInstance(proxy)) { 1477 return true; 1478 } 1479 } 1480 else if (method.getName().equals("close")) { 1481 // Handle close method: suppress, not valid. 1482 return null; 1483 } 1484 else if (method.getName().equals("isClosed")) { 1485 return false; 1486 } 1487 else if (method.getName().equals("getTargetConnection")) { 1488 // Handle getTargetConnection method: return underlying Connection. 1489 return this.target; 1490 } 1491 1492 // Invoke method on target Connection. 1493 try { 1494 Object retVal = method.invoke(this.target, args); 1495 1496 // If return value is a JDBC Statement, apply statement settings 1497 // (fetch size, max rows, transaction timeout). 1498 if (retVal instanceof Statement) { 1499 applyStatementSettings(((Statement) retVal)); 1500 } 1501 1502 return retVal; 1503 } 1504 catch (InvocationTargetException ex) { 1505 throw ex.getTargetException(); 1506 } 1507 } 1508 } 1509 1510 1511 /** 1512 * Simple adapter for PreparedStatementCreator, allowing to use a plain SQL statement. 1513 */ 1514 private static class SimplePreparedStatementCreator implements PreparedStatementCreator, SqlProvider { 1515 1516 private final String sql; 1517 1518 public SimplePreparedStatementCreator(String sql) { 1519 Assert.notNull(sql, "SQL must not be null"); 1520 this.sql = sql; 1521 } 1522 1523 @Override 1524 public PreparedStatement createPreparedStatement(Connection con) throws SQLException { 1525 return con.prepareStatement(this.sql); 1526 } 1527 1528 @Override 1529 public String getSql() { 1530 return this.sql; 1531 } 1532 } 1533 1534 1535 /** 1536 * Simple adapter for CallableStatementCreator, allowing to use a plain SQL statement. 1537 */ 1538 private static class SimpleCallableStatementCreator implements CallableStatementCreator, SqlProvider { 1539 1540 private final String callString; 1541 1542 public SimpleCallableStatementCreator(String callString) { 1543 Assert.notNull(callString, "Call string must not be null"); 1544 this.callString = callString; 1545 } 1546 1547 @Override 1548 public CallableStatement createCallableStatement(Connection con) throws SQLException { 1549 return con.prepareCall(this.callString); 1550 } 1551 1552 @Override 1553 public String getSql() { 1554 return this.callString; 1555 } 1556 } 1557 1558 1559 /** 1560 * Adapter to enable use of a RowCallbackHandler inside a ResultSetExtractor. 1561 * <p>Uses a regular ResultSet, so we have to be careful when using it: 1562 * We don't use it for navigating since this could lead to unpredictable consequences. 1563 */ 1564 private static class RowCallbackHandlerResultSetExtractor implements ResultSetExtractor<Object> { 1565 1566 private final RowCallbackHandler rch; 1567 1568 public RowCallbackHandlerResultSetExtractor(RowCallbackHandler rch) { 1569 this.rch = rch; 1570 } 1571 1572 @Override 1573 public Object extractData(ResultSet rs) throws SQLException { 1574 while (rs.next()) { 1575 this.rch.processRow(rs); 1576 } 1577 return null; 1578 } 1579 } 1580 1581}