GrepCode: org.springframework.jdbc.core.JdbcTemplate (.java) - Class

Maven-Central
/
org.springframework / spring-jdbc
/
3.0.5.RELEASE

3.2.1.RELEASE
3.1.4.RELEASE
3.1.3.RELEASE
3.1.2.RELEASE
3.1.1.RELEASE
3.1.0.RELEASE
3.0.7.RELEASE
3.0.6.RELEASE
3.0.5.RELEASE
3.0.4.RELEASE
3.0.3.RELEASE
3.0.2.RELEASE
3.0.1.RELEASE
3.0.0.RELEASE
2.5.6
2.5.5
1.2.1
/
org.springframework.jdbc.core.JdbcTemplate
Find Usages
Diff
Raw
Download
HTML Widget
Stack Overflow Questions
1
   /*
2
    * Copyright 2002-2010 the original author or authors.
3
    *
4
    * Licensed under the Apache License, Version 2.0 (the "License");
5
    * you may not use this file except in compliance with the License.
6
    * You may obtain a copy of the License at
7
    *
8
    *      http://www.apache.org/licenses/LICENSE-2.0
9
    *
10
   * Unless required by applicable law or agreed to in writing, software
11
   * distributed under the License is distributed on an "AS IS" BASIS,
12
   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
   * See the License for the specific language governing permissions and
14
   * limitations under the License.
15
   */
16
  
17
  package org.springframework.jdbc.core;
18
  
19
  import java.lang.reflect.InvocationHandler;
20
  import java.lang.reflect.InvocationTargetException;
21
  import java.lang.reflect.Method;
22
  import java.lang.reflect.Proxy;
23
  import java.sql.CallableStatement;
24
  import java.sql.Connection;
25
  import java.sql.PreparedStatement;
26
  import java.sql.ResultSet;
27
  import java.sql.SQLException;
28
  import java.sql.SQLWarning;
29
  import java.sql.Statement;
30
  import java.util.ArrayList;
31
  import java.util.Collections;
32
  import java.util.HashMap;
33
  import java.util.LinkedHashMap;
34
  import java.util.List;
35
  import java.util.Map;
36
  import javax.sql.DataSource;
37
  
38
  import org.springframework.dao.DataAccessException;
39
  import org.springframework.dao.InvalidDataAccessApiUsageException;
40
  import org.springframework.dao.support.DataAccessUtils;
41
  import org.springframework.jdbc.SQLWarningException;
42
  import org.springframework.jdbc.datasource.ConnectionProxy;
43
  import org.springframework.jdbc.datasource.DataSourceUtils;
44
  import org.springframework.jdbc.support.JdbcAccessor;
45
  import org.springframework.jdbc.support.JdbcUtils;
46
  import org.springframework.jdbc.support.KeyHolder;
47
  import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
48
  import org.springframework.jdbc.support.rowset.SqlRowSet;
49
  import org.springframework.util.Assert;
50
  import org.springframework.util.LinkedCaseInsensitiveMap;

This is the central class in the JDBC core package.
 It simplifies the use of JDBC and helps to avoid common errors.
 It executes core JDBC workflow, leaving application code to provide SQL
 and extract results. This class executes SQL queries or updates, initiating
 iteration over ResultSets and catching JDBC exceptions and translating
 them to the generic, more informative exception hierarchy defined in the
 org.springframework.dao package.

 Code using this class need only implement callback interfaces, giving
 them a clearly defined contract. The PreparedStatementCreator callback
 interface creates a prepared statement given a Connection, providing SQL and
 any necessary parameters. The ResultSetExtractor interface extracts
 values from a ResultSet. See also PreparedStatementSetter and
 RowMapper for two popular alternative callback interfaces.

 
Can be used within a service implementation via direct instantiation
 with a DataSource reference, or get prepared in an application context
 and given to services as bean reference. Note: The DataSource should
 always be configured as a bean in the application context, in the first case
 given to the service directly, in the second case to the prepared template.

 
Because this class is parameterizable by the callback interfaces and
 the org.springframework.jdbc.support.SQLExceptionTranslator
 interface, there should be no need to subclass it.

 
All SQL operations performed by this class are logged at debug level,
 using "org.springframework.jdbc.core.JdbcTemplate" as log category.

 
 Author(s):Rod JohnsonJuergen HoellerThomas Risberg Since:May 3, 2001 See also:PreparedStatementCreatorPreparedStatementSetterCallableStatementCreatorPreparedStatementCallbackCallableStatementCallbackResultSetExtractorRowCallbackHandlerRowMapperorg.springframework.jdbc.support.SQLExceptionTranslator
94
  
95
  public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {
96
  
97
  	private static final String RETURN_RESULT_SET_PREFIX = "#result-set-";
98
  
99
  	private static final String RETURN_UPDATE_COUNT_PREFIX = "#update-count-";


	
Custom NativeJdbcExtractor
102
 
103
 	private NativeJdbcExtractor nativeJdbcExtractor;

	
If this variable is false, we will throw exceptions on SQL warnings
105
 
106
 	private boolean ignoreWarnings = true;

	
 If this variable is set to a non-zero value, it will be used for setting the
 fetchSize property on statements used for query processing.
 
111
 
112
 	private int fetchSize = 0;

	
 If this variable is set to a non-zero value, it will be used for setting the
 maxRows property on statements used for query processing.
 
117
 
118
 	private int maxRows = 0;

	
 If this variable is set to a non-zero value, it will be used for setting the
 queryTimeout property on statements used for query processing.
 
123
 
124
 	private int queryTimeout = 0;

	
 If this variable is set to true then all results checking will be bypassed for any
 callable statement processing.  This can be used to avoid a bug in some older Oracle
 JDBC drivers like 10.1.0.2.
 
130
 
131
 	private boolean skipResultsProcessing = false;

	
 If this variable is set to true then all results from a stored procedure call
 that don't have a corresponding SqlOutParameter declaration will be bypassed.
 All other results processng will be take place unless the variable 
 skipResultsProcessing is set to true 
 
138
 
139
 	private boolean skipUndeclaredResults = false;

	
 If this variable is set to true then execution of a CallableStatement will return
 the results in a Map that uses case insensitive names for the parameters if
 Commons Collections is available on the classpath.
 
145
 
146
 	private boolean resultsMapCaseInsensitive = false;


	
 Construct a new JdbcTemplate for bean usage.
 Note: The DataSource has to be set before using the instance.
 
 See also:org.springframework.jdbc.support.JdbcAccessor.setDataSource(javax.sql.DataSource)
153
 
154
 	public JdbcTemplate() {
155
 	}

	
 Construct a new JdbcTemplate, given a DataSource to obtain connections from.
 Note: This will not trigger initialization of the exception translator.
 
 Parameters:dataSource the JDBC DataSource to obtain connections from
161
 
162
 	public JdbcTemplate(DataSource dataSource) {
163
 		setDataSource(dataSource);
164
 		afterPropertiesSet();
165
 	}

	
 Construct a new JdbcTemplate, given a DataSource to obtain connections from.
 Note: Depending on the "lazyInit" flag, initialization of the exception translator
 will be triggered.
 
 Parameters:dataSource the JDBC DataSource to obtain connections fromlazyInit whether to lazily initialize the SQLExceptionTranslator
173
 
174
 	public JdbcTemplate(DataSource dataSource, boolean lazyInit) {
175
 		setDataSource(dataSource);
176
 		setLazyInit(lazyInit);
177
 		afterPropertiesSet();
178
 	}


	
 Set a NativeJdbcExtractor to extract native JDBC objects from wrapped handles.
 Useful if native Statement and/or ResultSet handles are expected for casting
 to database-specific implementation classes, but a connection pool that wraps
 JDBC objects is used (note: any pool will return wrapped Connections).
 
186
 
187
 	public void setNativeJdbcExtractor(NativeJdbcExtractor extractor) {
188
 		this.nativeJdbcExtractor = extractor;
189
 	}

	
 Return the current NativeJdbcExtractor implementation.
 
193
 
194
 	public NativeJdbcExtractor getNativeJdbcExtractor() {
195
 		return this.nativeJdbcExtractor;
196
 	}

	
 Set whether or not we want to ignore SQLWarnings.
 Default is "true", swallowing and logging all warnings. Switch this flag
 to "false" to make the JdbcTemplate throw a SQLWarningException instead.
 
 See also:java.sql.SQLWarningorg.springframework.jdbc.SQLWarningExceptionhandleWarnings(java.sql.Statement)
205
 
206
 	public void setIgnoreWarnings(boolean ignoreWarnings) {
207
 		this.ignoreWarnings = ignoreWarnings;
208
 	}

	
 Return whether or not we ignore SQLWarnings.
 
212
 
213
 	public boolean isIgnoreWarnings() {
214
 		return this.ignoreWarnings;
215
 	}

	
 Set the fetch size for this JdbcTemplate. This is important for processing
 large result sets: Setting this higher than the default value will increase
 processing speed at the cost of memory consumption; setting this lower can
 avoid transferring row data that will never be read by the application.
 Default is 0, indicating to use the JDBC driver's default.
 
 See also:java.sql.Statement.setFetchSize(int)
224
 
225
 	public void setFetchSize(int fetchSize) {
226
 		this.fetchSize = fetchSize;
227
 	}

	
 Return the fetch size specified for this JdbcTemplate.
 
231
 
232
 	public int getFetchSize() {
233
 		return this.fetchSize;
234
 	}

	
 Set the maximum number of rows for this JdbcTemplate. This is important
 for processing subsets of large result sets, avoiding to read and hold
 the entire result set in the database or in the JDBC driver if we're
 never interested in the entire result in the first place (for example,
 when performing searches that might return a large number of matches).
 Default is 0, indicating to use the JDBC driver's default.
 
 See also:java.sql.Statement.setMaxRows(int)
244
 
245
 	public void setMaxRows(int maxRows) {
246
 		this.maxRows = maxRows;
247
 	}

	
 Return the maximum number of rows specified for this JdbcTemplate.
 
251
 
252
 	public int getMaxRows() {
253
 		return this.maxRows;
254
 	}

	
 Set the query timeout for statements that this JdbcTemplate executes.
 Default is 0, indicating to use the JDBC driver's default.
 
Note: Any timeout specified here will be overridden by the remaining
 transaction timeout when executing within a transaction that has a
 timeout specified at the transaction level.
 
 See also:java.sql.Statement.setQueryTimeout(int)
263
 
264
 	public void setQueryTimeout(int queryTimeout) {
265
 		this.queryTimeout = queryTimeout;
266
 	}

	
 Return the query timeout for statements that this JdbcTemplate executes.
 
270
 
271
 	public int getQueryTimeout() {
272
 		return this.queryTimeout;
273
 	}

	
 Set whether results processing should be skipped.  Can be used to optimize callable
 statement processing when we know that no results are being passed back - the processing
 of out parameter will still take place.  This can be used to avoid a bug in some older
 Oracle JDBC drivers like 10.1.0.2.
 
280
 
281
 	public void setSkipResultsProcessing(boolean skipResultsProcessing) {
282
 		this.skipResultsProcessing = skipResultsProcessing;
283
 	}

	
 Return whether results processing should be skipped.
 
287
 
288
 	public boolean isSkipResultsProcessing() {
289
 		return this.skipResultsProcessing;
290
 	}

	
 Set whether undelared results should be skipped.
 
294
 
295
 	public void setSkipUndeclaredResults(boolean skipUndeclaredResults) {
296
 		this.skipUndeclaredResults = skipUndeclaredResults;
297
 	}

	
 Return whether undeclared results should be skipped.
 
301
 
302
 	public boolean isSkipUndeclaredResults() {
303
 		return this.skipUndeclaredResults;
304
 	}

	
 Set whether execution of a CallableStatement will return the results in a Map
 that uses case insensitive names for the parameters.
 
309
 
310
 	public void setResultsMapCaseInsensitive(boolean resultsMapCaseInsensitive) {
311
 		this.resultsMapCaseInsensitive = resultsMapCaseInsensitive;
312
 	}

	
 Return whether execution of a CallableStatement will return the results in a Map
 that uses case insensitive names for the parameters.
 
317
 
318
 	public boolean isResultsMapCaseInsensitive() {
319
 		return this.resultsMapCaseInsensitive;
320
 	}
321
 
322
 
323
 	//-------------------------------------------------------------------------
324
 	// Methods dealing with a plain java.sql.Connection
325
 	//-------------------------------------------------------------------------
326
 
327
 	public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {
328
 		Assert.notNull(action, "Callback object must not be null");
329
 
330
 		Connection con = DataSourceUtils.getConnection(getDataSource());
331
 		try {
332
 			Connection conToUse = con;
333
 			if (this.nativeJdbcExtractor != null) {
334
 				// Extract native JDBC Connection, castable to OracleConnection or the like.
335
 				conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
336
 			}
337
 			else {
338
 				// Create close-suppressing Connection proxy, also preparing returned Statements.
339
 				conToUse = createConnectionProxy(con);
340
 			}
341
 			return action.doInConnection(conToUse);
342
 		}
343
 		catch (SQLException ex) {
344
 			// Release Connection early, to avoid potential connection pool deadlock
345
 			// in the case when the exception translator hasn't been initialized yet.
346
 			DataSourceUtils.releaseConnection(con, getDataSource());
347
 			con = null;
348
 			throw getExceptionTranslator().translate("ConnectionCallback", getSql(action), ex);
349
 		}
350
 		finally {
351
 			DataSourceUtils.releaseConnection(con, getDataSource());
352
 		}
353
 	}

	
 Create a close-suppressing proxy for the given JDBC Connection.
 Called by the execute method.
 The proxy also prepares returned JDBC Statements, applying
 statement settings such as fetch size, max rows, and query timeout.
 
 Parameters:con the JDBC Connection to create a proxy for Returns:the Connection proxy See also:java.sql.Connection.close()execute(org.springframework.jdbc.core.ConnectionCallback)applyStatementSettings(java.sql.Statement)
365
 
366
 	protected Connection createConnectionProxy(Connection con) {
367
 		return (Connection) Proxy.newProxyInstance(
368
 				ConnectionProxy.class.getClassLoader(),
369
 				new Class[] {ConnectionProxy.class},
370
 				new CloseSuppressingInvocationHandler(con));
371
 	}
372
 
373
 
374
 	//-------------------------------------------------------------------------
375
 	// Methods dealing with static SQL (java.sql.Statement)
376
 	//-------------------------------------------------------------------------
377
 
378
 	public <T> T execute(StatementCallback<T> action) throws DataAccessException {
379
 		Assert.notNull(action, "Callback object must not be null");
380
 
381
 		Connection con = DataSourceUtils.getConnection(getDataSource());
382
 		Statement stmt = null;
383
 		try {
384
 			Connection conToUse = con;
385
 			if (this.nativeJdbcExtractor != null &&
386
 					this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
387
 				conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
388
 			}
389
 			stmt = conToUse.createStatement();
390
 			applyStatementSettings(stmt);
391
 			Statement stmtToUse = stmt;
392
 			if (this.nativeJdbcExtractor != null) {
393
 				stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
394
 			}
395
 			T result = action.doInStatement(stmtToUse);
396
 			handleWarnings(stmt);
397
 			return result;
398
 		}
399
 		catch (SQLException ex) {
400
 			// Release Connection early, to avoid potential connection pool deadlock
401
 			// in the case when the exception translator hasn't been initialized yet.
402
 			JdbcUtils.closeStatement(stmt);
403
 			stmt = null;
404
 			DataSourceUtils.releaseConnection(con, getDataSource());
405
 			con = null;
406
 			throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
407
 		}
408
 		finally {
409
 			JdbcUtils.closeStatement(stmt);
410
 			DataSourceUtils.releaseConnection(con, getDataSource());
411
 		}
412
 	}
413
 
414
 	public void execute(final String sql) throws DataAccessException {
415
 		if (logger.isDebugEnabled()) {
416
 			logger.debug("Executing SQL statement [" + sql + "]");
417
 		}
418
 		class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
419
 			public Object doInStatement(Statement stmt) throws SQLException {
420
 				stmt.execute(sql);
421
 				return null;
422
 			}
423
 			public String getSql() {
424
 				return sql;
425
 			}
426
 		}
427
 		execute(new ExecuteStatementCallback());
428
 	}
429
 
430
 	public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
431
 		Assert.notNull(sql, "SQL must not be null");
432
 		Assert.notNull(rse, "ResultSetExtractor must not be null");
433
 		if (logger.isDebugEnabled()) {
434
 			logger.debug("Executing SQL query [" + sql + "]");
435
 		}
436
 		class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
437
 			public T doInStatement(Statement stmt) throws SQLException {
438
 				ResultSet rs = null;
439
 				try {
440
 					rs = stmt.executeQuery(sql);
441
 					ResultSet rsToUse = rs;
442
 					if (nativeJdbcExtractor != null) {
443
 						rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
444
 					}
445
 					return rse.extractData(rsToUse);
446
 				}
447
 				finally {
448
 					JdbcUtils.closeResultSet(rs);
449
 				}
450
 			}
451
 			public String getSql() {
452
 				return sql;
453
 			}
454
 		}
455
 		return execute(new QueryStatementCallback());
456
 	}
457
 
458
 	public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
459
 		query(sql, new RowCallbackHandlerResultSetExtractor(rch));
460
 	}
461
 
462
 	public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
463
 		return query(sql, new RowMapperResultSetExtractor<T>(rowMapper));
464
 	}
465
 
466
 	public Map<String, Object> queryForMap(String sql) throws DataAccessException {
467
 		return queryForObject(sql, getColumnMapRowMapper());
468
 	}
469
 
470
 	public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException {
471
 		List<T> results = query(sql, rowMapper);
472
 		return DataAccessUtils.requiredSingleResult(results);
473
 	}
474
 
475
 	public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException {
476
 		return queryForObject(sql, getSingleColumnRowMapper(requiredType));
477
 	}
478
 
479
 	public long queryForLong(String sql) throws DataAccessException {
480
 		Number number = queryForObject(sql, Long.class);
481
 		return (number != null ? number.longValue() : 0);
482
 	}
483
 
484
 	public int queryForInt(String sql) throws DataAccessException {
485
 		Number number = queryForObject(sql, Integer.class);
486
 		return (number != null ? number.intValue() : 0);
487
 	}
488
 
489
 	public <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException {
490
 		return query(sql, getSingleColumnRowMapper(elementType));
491
 	}
492
 
493
 	public List<Map<String, Object>> queryForList(String sql) throws DataAccessException {
494
 		return query(sql, getColumnMapRowMapper());
495
 	}
496
 
497
 	public SqlRowSet queryForRowSet(String sql) throws DataAccessException {
498
 		return query(sql, new SqlRowSetResultSetExtractor());
499
 	}
500
 
501
 	public int update(final String sql) throws DataAccessException {
502
 		Assert.notNull(sql, "SQL must not be null");
503
 		if (logger.isDebugEnabled()) {
504
 			logger.debug("Executing SQL update [" + sql + "]");
505
 		}
506
 		class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider {
507
 			public Integer doInStatement(Statement stmt) throws SQLException {
508
 				int rows = stmt.executeUpdate(sql);
509
 				if (logger.isDebugEnabled()) {
510
 					logger.debug("SQL update affected " + rows + " rows");
511
 				}
512
 				return rows;
513
 			}
514
 			public String getSql() {
515
 				return sql;
516
 			}
517
 		}
518
 		return execute(new UpdateStatementCallback());
519
 	}
520
 
521
 	public int[] batchUpdate(final String[] sql) throws DataAccessException {
522
 		Assert.notEmpty(sql, "SQL array must not be empty");
523
 		if (logger.isDebugEnabled()) {
524
 			logger.debug("Executing SQL batch update of " + sql.length + " statements");
525
 		}
526
 		class BatchUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider {
527
 			private String currSql;
528
 			public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException {
529
 				int[] rowsAffected = new int[sql.length];
530
 				if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
531
 					for (String sqlStmt : sql) {
532
 						this.currSql = sqlStmt;
533
 						stmt.addBatch(sqlStmt);
534
 					}
535
 					rowsAffected = stmt.executeBatch();
536
 				}
537
 				else {
538
 					for (int i = 0; i < sql.length; i++) {
539
 						this.currSql = sql[i];
540
 						if (!stmt.execute(sql[i])) {
541
 							rowsAffected[i] = stmt.getUpdateCount();
542
 						}
543
 						else {
544
 							throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]);
545
 						}
546
 					}
547
 				}
548
 				return rowsAffected;
549
 			}
550
 			public String getSql() {
551
 				return this.currSql;
552
 			}
553
 		}
554
 		return execute(new BatchUpdateStatementCallback());
555
 	}
556
 
557
 
558
 	//-------------------------------------------------------------------------
559
 	// Methods dealing with prepared statements
560
 	//-------------------------------------------------------------------------
561
 
562
 	public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action)
563
 			throws DataAccessException {
564
 
565
 		Assert.notNull(psc, "PreparedStatementCreator must not be null");
566
 		Assert.notNull(action, "Callback object must not be null");
567
 		if (logger.isDebugEnabled()) {
568
 			String sql = getSql(psc);
569
 			logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));
570
 		}
571
 
572
 		Connection con = DataSourceUtils.getConnection(getDataSource());
573
 		PreparedStatement ps = null;
574
 		try {
575
 			Connection conToUse = con;
576
 			if (this.nativeJdbcExtractor != null &&
577
 					this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) {
578
 				conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
579
 			}
580
 			ps = psc.createPreparedStatement(conToUse);
581
 			applyStatementSettings(ps);
582
 			PreparedStatement psToUse = ps;
583
 			if (this.nativeJdbcExtractor != null) {
584
 				psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps);
585
 			}
586
 			T result = action.doInPreparedStatement(psToUse);
587
 			handleWarnings(ps);
588
 			return result;
589
 		}
590
 		catch (SQLException ex) {
591
 			// Release Connection early, to avoid potential connection pool deadlock
592
 			// in the case when the exception translator hasn't been initialized yet.
593
 			if (psc instanceof ParameterDisposer) {
594
 				((ParameterDisposer) psc).cleanupParameters();
595
 			}
596
 			String sql = getSql(psc);
597
 			psc = null;
598
 			JdbcUtils.closeStatement(ps);
599
 			ps = null;
600
 			DataSourceUtils.releaseConnection(con, getDataSource());
601
 			con = null;
602
 			throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex);
603
 		}
604
 		finally {
605
 			if (psc instanceof ParameterDisposer) {
606
 				((ParameterDisposer) psc).cleanupParameters();
607
 			}
608
 			JdbcUtils.closeStatement(ps);
609
 			DataSourceUtils.releaseConnection(con, getDataSource());
610
 		}
611
 	}
612
 
613
 	public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException {
614
 		return execute(new SimplePreparedStatementCreator(sql), action);
615
 	}

	
 Query using a prepared statement, allowing for a PreparedStatementCreator
 and a PreparedStatementSetter. Most other query methods use this method,
 but application code will always work with either a creator or a setter.
 
 Parameters:psc Callback handler that can create a PreparedStatement given a
 Connectionpss object that knows how to set values on the prepared statement.
 If this is null, the SQL will be assumed to contain no bind parameters.rse object that will extract results. Returns:an arbitrary result object, as returned by the ResultSetExtractor Throws:org.springframework.dao.DataAccessException if there is any problem
628
 
629
 	public <T> T query(
630
 			PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
631
 			throws DataAccessException {
632
 
633
 		Assert.notNull(rse, "ResultSetExtractor must not be null");
634
 		logger.debug("Executing prepared SQL query");
635
 
636
 		return execute(psc, new PreparedStatementCallback<T>() {
637
 			public T doInPreparedStatement(PreparedStatement ps) throws SQLException {
638
 				ResultSet rs = null;
639
 				try {
640
 					if (pss != null) {
641
 						pss.setValues(ps);
642
 					}
643
 					rs = ps.executeQuery();
644
 					ResultSet rsToUse = rs;
645
 					if (nativeJdbcExtractor != null) {
646
 						rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
647
 					}
648
 					return rse.extractData(rsToUse);
649
 				}
650
 				finally {
651
 					JdbcUtils.closeResultSet(rs);
652
 					if (pss instanceof ParameterDisposer) {
653
 						((ParameterDisposer) pss).cleanupParameters();
654
 					}
655
 				}
656
 			}
657
 		});
658
 	}
659
 
660
 	public <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException {
661
 		return query(psc, null, rse);
662
 	}
663
 
664
 	public <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException {
665
 		return query(new SimplePreparedStatementCreator(sql), pss, rse);
666
 	}
667
 
668
 	public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException {
669
 		return query(sql, newArgTypePreparedStatementSetter(args, argTypes), rse);
670
 	}
671
 
672
 	public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException {
673
 		return query(sql, newArgPreparedStatementSetter(args), rse);
674
 	}
675
 
676
 	public <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) throws DataAccessException {
677
 		return query(sql, newArgPreparedStatementSetter(args), rse);
678
 	}
679
 
680
 	public void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException {
681
 		query(psc, new RowCallbackHandlerResultSetExtractor(rch));
682
 	}
683
 
684
 	public void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException {
685
 		query(sql, pss, new RowCallbackHandlerResultSetExtractor(rch));
686
 	}
687
 
688
 	public void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException {
689
 		query(sql, newArgTypePreparedStatementSetter(args, argTypes), rch);
690
 	}
691
 
692
 	public void query(String sql, Object[] args, RowCallbackHandler rch) throws DataAccessException {
693
 		query(sql, newArgPreparedStatementSetter(args), rch);
694
 	}
695
 
696
 	public void query(String sql, RowCallbackHandler rch, Object... args) throws DataAccessException {
697
 		query(sql, newArgPreparedStatementSetter(args), rch);
698
 	}
699
 
700
 	public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException {
701
 		return query(psc, new RowMapperResultSetExtractor<T>(rowMapper));
702
 	}
703
 
704
 	public <T> List<T> query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException {
705
 		return query(sql, pss, new RowMapperResultSetExtractor<T>(rowMapper));
706
 	}
707
 
708
 	public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException {
709
 		return query(sql, args, argTypes, new RowMapperResultSetExtractor<T>(rowMapper));
710
 	}
711
 
712
 	public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException {
713
 		return query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper));
714
 	}
715
 
716
 	public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
717
 		return query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper));
718
 	}
719
 
720
 	public <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper)
721
 			throws DataAccessException {
722
 
723
 		List<T> results = query(sql, args, argTypes, new RowMapperResultSetExtractor<T>(rowMapper, 1));
724
 		return DataAccessUtils.requiredSingleResult(results);
725
 	}
726
 
727
 	public <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException {
728
 		List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
729
 		return DataAccessUtils.requiredSingleResult(results);
730
 	}
731
 
732
 	public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
733
 		List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
734
 		return DataAccessUtils.requiredSingleResult(results);
735
 	}
736
 
737
 	public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType)
738
 			throws DataAccessException {
739
 
740
 		return queryForObject(sql, args, argTypes, getSingleColumnRowMapper(requiredType));
741
 	}
742
 
743
 	public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException {
744
 		return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
745
 	}
746
 
747
 	public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException {
748
 		return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
749
 	}
750
 
751
 	public Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException {
752
 		return queryForObject(sql, args, argTypes, getColumnMapRowMapper());
753
 	}
754
 
755
 	public Map<String, Object> queryForMap(String sql, Object... args) throws DataAccessException {
756
 		return queryForObject(sql, args, getColumnMapRowMapper());
757
 	}
758
 
759
 	public long queryForLong(String sql, Object[] args, int[] argTypes) throws DataAccessException {
760
 		Number number = queryForObject(sql, args, argTypes, Long.class);
761
 		return (number != null ? number.longValue() : 0);
762
 	}
763
 
764
 	public long queryForLong(String sql, Object... args) throws DataAccessException {
765
 		Number number = queryForObject(sql, args, Long.class);
766
 		return (number != null ? number.longValue() : 0);
767
 	}
768
 
769
 	public int queryForInt(String sql, Object[] args, int[] argTypes) throws DataAccessException {
770
 		Number number = queryForObject(sql, args, argTypes, Integer.class);
771
 		return (number != null ? number.intValue() : 0);
772
 	}
773
 
774
 	public int queryForInt(String sql, Object... args) throws DataAccessException {
775
 		Number number = queryForObject(sql, args, Integer.class);
776
 		return (number != null ? number.intValue() : 0);
777
 	}
778
 
779
 	public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) throws DataAccessException {
780
 		return query(sql, args, argTypes, getSingleColumnRowMapper(elementType));
781
 	}
782
 
783
 	public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType) throws DataAccessException {
784
 		return query(sql, args, getSingleColumnRowMapper(elementType));
785
 	}
786
 
787
 	public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args) throws DataAccessException {
788
 		return query(sql, args, getSingleColumnRowMapper(elementType));
789
 	}
790
 
791
 	public List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException {
792
 		return query(sql, args, argTypes, getColumnMapRowMapper());
793
 	}
794
 
795
 	public List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException {
796
 		return query(sql, args, getColumnMapRowMapper());
797
 	}
798
 
799
 	public SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException {
800
 		return query(sql, args, argTypes, new SqlRowSetResultSetExtractor());
801
 	}
802
 
803
 	public SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException {
804
 		return query(sql, args, new SqlRowSetResultSetExtractor());
805
 	}
806
 
807
 	protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
808
 			throws DataAccessException {
809
 
810
 		logger.debug("Executing prepared SQL update");
811
 		return execute(psc, new PreparedStatementCallback<Integer>() {
812
 			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
813
 				try {
814
 					if (pss != null) {
815
 						pss.setValues(ps);
816
 					}
817
 					int rows = ps.executeUpdate();
818
 					if (logger.isDebugEnabled()) {
819
 						logger.debug("SQL update affected " + rows + " rows");
820
 					}
821
 					return rows;
822
 				}
823
 				finally {
824
 					if (pss instanceof ParameterDisposer) {
825
 						((ParameterDisposer) pss).cleanupParameters();
826
 					}
827
 				}
828
 			}
829
 		});
830
 	}
831
 
832
 	public int update(PreparedStatementCreator psc) throws DataAccessException {
833
 		return update(psc, (PreparedStatementSetter) null);
834
 	}
835
 
836
 	public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
837
 			throws DataAccessException {
838
 
839
 		Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
840
 		logger.debug("Executing SQL update and returning generated keys");
841
 
842
 		return execute(psc, new PreparedStatementCallback<Integer>() {
843
 			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
844
 				int rows = ps.executeUpdate();
845
 				List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
846
 				generatedKeys.clear();
847
 				ResultSet keys = ps.getGeneratedKeys();
848
 				if (keys != null) {
849
 					try {
850
 						RowMapperResultSetExtractor<Map<String, Object>> rse =
851
 								new RowMapperResultSetExtractor<Map<String, Object>>(getColumnMapRowMapper(), 1);
852
 						generatedKeys.addAll(rse.extractData(keys));
853
 					}
854
 					finally {
855
 						JdbcUtils.closeResultSet(keys);
856
 					}
857
 				}
858
 				if (logger.isDebugEnabled()) {
859
 					logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
860
 				}
861
 				return rows;
862
 			}
863
 		});
864
 	}
865
 
866
 	public int update(String sql, PreparedStatementSetter pss) throws DataAccessException {
867
 		return update(new SimplePreparedStatementCreator(sql), pss);
868
 	}
869
 
870
 	public int update(String sql, Object[] args, int[] argTypes) throws DataAccessException {
871
 		return update(sql, newArgTypePreparedStatementSetter(args, argTypes));
872
 	}
873
 
874
 	public int update(String sql, Object... args) throws DataAccessException {
875
 		return update(sql, newArgPreparedStatementSetter(args));
876
 	}
877
 
878
 	public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
879
 		if (logger.isDebugEnabled()) {
880
 			logger.debug("Executing SQL batch update [" + sql + "]");
881
 		}
882
 
883
 		return execute(sql, new PreparedStatementCallback<int[]>() {
884
 			public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException {
885
 				try {
886
 					int batchSize = pss.getBatchSize();
887
 					InterruptibleBatchPreparedStatementSetter ipss =
888
 							(pss instanceof InterruptibleBatchPreparedStatementSetter ?
889
 							(InterruptibleBatchPreparedStatementSetter) pss : null);
890
 					if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
891
 						for (int i = 0; i < batchSize; i++) {
892
 							pss.setValues(ps, i);
893
 							if (ipss != null && ipss.isBatchExhausted(i)) {
894
 								break;
895
 							}
896
 							ps.addBatch();
897
 						}
898
 						return ps.executeBatch();
899
 					}
900
 					else {
901
 						List<Integer> rowsAffected = new ArrayList<Integer>();
902
 						for (int i = 0; i < batchSize; i++) {
903
 							pss.setValues(ps, i);
904
 							if (ipss != null && ipss.isBatchExhausted(i)) {
905
 								break;
906
 							}
907
 							rowsAffected.add(ps.executeUpdate());
908
 						}
909
 						int[] rowsAffectedArray = new int[rowsAffected.size()];
910
 						for (int i = 0; i < rowsAffectedArray.length; i++) {
911
 							rowsAffectedArray[i] = rowsAffected.get(i);
912
 						}
913
 						return rowsAffectedArray;
914
 					}
915
 				}
916
 				finally {
917
 					if (pss instanceof ParameterDisposer) {
918
 						((ParameterDisposer) pss).cleanupParameters();
919
 					}
920
 				}
921
 			}
922
 		});
923
 	}
924
 
925
 
926
 	//-------------------------------------------------------------------------
927
 	// Methods dealing with callable statements
928
 	//-------------------------------------------------------------------------
929
 
930
 	public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action)
931
 			throws DataAccessException {
932
 
933
 		Assert.notNull(csc, "CallableStatementCreator must not be null");
934
 		Assert.notNull(action, "Callback object must not be null");
935
 		if (logger.isDebugEnabled()) {
936
 			String sql = getSql(csc);
937
 			logger.debug("Calling stored procedure" + (sql != null ? " [" + sql  + "]" : ""));
938
 		}
939
 
940
 		Connection con = DataSourceUtils.getConnection(getDataSource());
941
 		CallableStatement cs = null;
942
 		try {
943
 			Connection conToUse = con;
944
 			if (this.nativeJdbcExtractor != null) {
945
 				conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
946
 			}
947
 			cs = csc.createCallableStatement(conToUse);
948
 			applyStatementSettings(cs);
949
 			CallableStatement csToUse = cs;
950
 			if (this.nativeJdbcExtractor != null) {
951
 				csToUse = this.nativeJdbcExtractor.getNativeCallableStatement(cs);
952
 			}
953
 			T result = action.doInCallableStatement(csToUse);
954
 			handleWarnings(cs);
955
 			return result;
956
 		}
957
 		catch (SQLException ex) {
958
 			// Release Connection early, to avoid potential connection pool deadlock
959
 			// in the case when the exception translator hasn't been initialized yet.
960
 			if (csc instanceof ParameterDisposer) {
961
 				((ParameterDisposer) csc).cleanupParameters();
962
 			}
963
 			String sql = getSql(csc);
964
 			csc = null;
965
 			JdbcUtils.closeStatement(cs);
966
 			cs = null;
967
 			DataSourceUtils.releaseConnection(con, getDataSource());
968
 			con = null;
969
 			throw getExceptionTranslator().translate("CallableStatementCallback", sql, ex);
970
 		}
971
 		finally {
972
 			if (csc instanceof ParameterDisposer) {
973
 				((ParameterDisposer) csc).cleanupParameters();
974
 			}
975
 			JdbcUtils.closeStatement(cs);
976
 			DataSourceUtils.releaseConnection(con, getDataSource());
977
 		}
978
 	}
979
 
980
 	public <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException {
981
 		return execute(new SimpleCallableStatementCreator(callString), action);
982
 	}
983
 
984
 	public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)
985
 			throws DataAccessException {
986
 
987
 		final List<SqlParameter> updateCountParameters = new ArrayList<SqlParameter>();
988
 		final List<SqlParameter> resultSetParameters = new ArrayList<SqlParameter>();
989
 		final List<SqlParameter> callParameters = new ArrayList<SqlParameter>();
990
 		for (SqlParameter parameter : declaredParameters) {
991
 			if (parameter.isResultsParameter()) {
992
 				if (parameter instanceof SqlReturnResultSet) {
993
 					resultSetParameters.add(parameter);
994
 				}
995
 				else {
996
 					updateCountParameters.add(parameter);					
997
 				}
998
 			}
999
 			else {
1000
				callParameters.add(parameter);
1001
			}
1002
		}
1003
		return execute(csc, new CallableStatementCallback<Map<String, Object>>() {
1004
			public Map<String, Object> doInCallableStatement(CallableStatement cs) throws SQLException {
1005
				boolean retVal = cs.execute();
1006
				int updateCount = cs.getUpdateCount();
1007
				if (logger.isDebugEnabled()) {
1008
					logger.debug("CallableStatement.execute() returned '" + retVal + "'");
1009
					logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
1010
				}
1011
				Map<String, Object> returnedResults = createResultsMap();
1012
				if (retVal || updateCount != -1) {
1013
					returnedResults.putAll(extractReturnedResults(cs, updateCountParameters, resultSetParameters, updateCount));
1014
				}
1015
				returnedResults.putAll(extractOutputParameters(cs, callParameters));
1016
				return returnedResults;
1017
			}
1018
		});
1019
	}

	
 Extract returned ResultSets from the completed stored procedure.
 
 Parameters:cs JDBC wrapper for the stored procedureupdateCountParameters Parameter list of declared update count parameters for the stored procedureresultSetParameters Parameter list of declared resturn resultSet parameters for the stored procedure Returns:Map that contains returned results
1027
1028
	protected Map<String, Object> extractReturnedResults(
1029
			CallableStatement cs, List updateCountParameters, List resultSetParameters, int updateCount)
1030
			throws SQLException {
1031
1032
		Map<String, Object> returnedResults = new HashMap<String, Object>();
1033
		int rsIndex = 0;
1034
		int updateIndex = 0;
1035
		boolean moreResults;
1036
		if (!skipResultsProcessing) {
1037
			do {
1038
				if (updateCount == -1) {
1039
					if (resultSetParameters != null && resultSetParameters.size() > rsIndex) {
1040
						SqlReturnResultSet declaredRsParam = (SqlReturnResultSet)resultSetParameters.get(rsIndex);
1041
						returnedResults.putAll(processResultSet(cs.getResultSet(), declaredRsParam));
1042
						rsIndex++;
1043
					}
1044
					else {
1045
						if (!skipUndeclaredResults) {
1046
							String rsName = RETURN_RESULT_SET_PREFIX + (rsIndex + 1);
1047
							SqlReturnResultSet undeclaredRsParam = new SqlReturnResultSet(rsName, new ColumnMapRowMapper());
1048
							logger.info("Added default SqlReturnResultSet parameter named " + rsName);
1049
							returnedResults.putAll(processResultSet(cs.getResultSet(), undeclaredRsParam));
1050
							rsIndex++;
1051
						}
1052
					}
1053
				}
1054
				else {
1055
					if (updateCountParameters != null && updateCountParameters.size() > updateIndex) {
1056
						SqlReturnUpdateCount ucParam = (SqlReturnUpdateCount)updateCountParameters.get(updateIndex);
1057
						String declaredUcName = ucParam.getName();
1058
						returnedResults.put(declaredUcName, updateCount);
1059
						updateIndex++;
1060
					}
1061
					else {
1062
						if (!skipUndeclaredResults) {
1063
							String undeclaredUcName = RETURN_UPDATE_COUNT_PREFIX + (updateIndex + 1);
1064
							logger.info("Added default SqlReturnUpdateCount parameter named " + undeclaredUcName);
1065
							returnedResults.put(undeclaredUcName, updateCount);
1066
							updateIndex++;
1067
						}
1068
					}
1069
				}
1070
				moreResults = cs.getMoreResults();
1071
				updateCount = cs.getUpdateCount();
1072
				if (logger.isDebugEnabled()) {
1073
					logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
1074
				}
1075
			}
1076
			while (moreResults || updateCount != -1);
1077
		}
1078
		return returnedResults;
1079
	}

	
 Extract output parameters from the completed stored procedure.
 
 Parameters:cs JDBC wrapper for the stored procedureparameters parameter list for the stored procedure Returns:Map that contains returned results
1086
1087
	protected Map<String, Object> extractOutputParameters(CallableStatement cs, List<SqlParameter> parameters)
1088
			throws SQLException {
1089
1090
		Map<String, Object> returnedResults = new HashMap<String, Object>();
1091
		int sqlColIndex = 1;
1092
		for (SqlParameter param : parameters) {
1093
			if (param instanceof SqlOutParameter) {
1094
				SqlOutParameter outParam = (SqlOutParameter) param;
1095
				if (outParam.isReturnTypeSupported()) {
1096
					Object out = outParam.getSqlReturnType().getTypeValue(
1097
							cs, sqlColIndex, outParam.getSqlType(), outParam.getTypeName());
1098
					returnedResults.put(outParam.getName(), out);
1099
				}
1100
				else {
1101
					Object out = cs.getObject(sqlColIndex);
1102
					if (out instanceof ResultSet) {
1103
						if (outParam.isResultSetSupported()) {
1104
							returnedResults.putAll(processResultSet((ResultSet) out, outParam));
1105
						}
1106
						else {
1107
							String rsName = outParam.getName();
1108
							SqlReturnResultSet rsParam = new SqlReturnResultSet(rsName, new ColumnMapRowMapper());
1109
							returnedResults.putAll(processResultSet(cs.getResultSet(), rsParam));
1110
							logger.info("Added default SqlReturnResultSet parameter named " + rsName);
1111
						}
1112
					}
1113
					else {
1114
						returnedResults.put(outParam.getName(), out);
1115
					}
1116
				}
1117
			}
1118
			if (!(param.isResultsParameter())) {
1119
				sqlColIndex++;
1120
			}
1121
		}
1122
		return returnedResults;
1123
	}

	
 Process the given ResultSet from a stored procedure.
 
 Parameters:rs the ResultSet to processparam the corresponding stored procedure parameter Returns:Map that contains returned results
1130
1131
	@SuppressWarnings("unchecked")
1132
	protected Map<String, Object> processResultSet(ResultSet rs, ResultSetSupportingSqlParameter param) throws SQLException {
1133
		if (rs == null) {
1134
			return Collections.emptyMap();
1135
		}
1136
		Map<String, Object> returnedResults = new HashMap<String, Object>();
1137
		try {
1138
			ResultSet rsToUse = rs;
1139
			if (this.nativeJdbcExtractor != null) {
1140
				rsToUse = this.nativeJdbcExtractor.getNativeResultSet(rs);
1141
			}
1142
			if (param.getRowMapper() != null) {
1143
				RowMapper rowMapper = param.getRowMapper();
1144
				Object result = (new RowMapperResultSetExtractor(rowMapper)).extractData(rsToUse);
1145