Java > Open Source Codes > org > springframework > jdbc > core > JdbcTemplate


1 /*
2  * Copyright 2002-2007 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.HashMap ;
31 import java.util.List ;
32 import java.util.Map ;
33
34 import javax.sql.DataSource ;
35
36 import org.springframework.dao.DataAccessException;
37 import org.springframework.dao.InvalidDataAccessApiUsageException;
38 import org.springframework.dao.support.DataAccessUtils;
39 import org.springframework.jdbc.SQLWarningException;
40 import org.springframework.jdbc.datasource.ConnectionProxy;
41 import org.springframework.jdbc.datasource.DataSourceUtils;
42 import org.springframework.jdbc.support.JdbcAccessor;
43 import org.springframework.jdbc.support.JdbcUtils;
44 import org.springframework.jdbc.support.KeyHolder;
45 import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
46 import org.springframework.jdbc.support.rowset.SqlRowSet;
47 import org.springframework.util.Assert;
48
49 /**
50  * <b>This is the central class in the JDBC core package.</b>
51  * It simplifies the use of JDBC and helps to avoid common errors.
52  * It executes core JDBC workflow, leaving application code to provide SQL
53  * and extract results. This class executes SQL queries or updates, initiating
54  * iteration over ResultSets and catching JDBC exceptions and translating
55  * them to the generic, more informative exception hierarchy defined in the
56  * <code>org.springframework.dao</code> package.
57  *
58  * <p>Code using this class need only implement callback interfaces, giving
59  * them a clearly defined contract. The {@link PreparedStatementCreator} callback
60  * interface creates a prepared statement given a Connection, providing SQL and
61  * any necessary parameters. The {@link ResultSetExtractor} interface extracts
62  * values from a ResultSet. See also {@link PreparedStatementSetter} and
63  * {@link RowMapper} for two popular alternative callback interfaces.
64  *
65  * <p>Can be used within a service implementation via direct instantiation
66  * with a DataSource reference, or get prepared in an application context
67  * and given to services as bean reference. Note: The DataSource should
68  * always be configured as a bean in the application context, in the first case
69  * given to the service directly, in the second case to the prepared template.
70  *
71  * <p>Because this class is parameterizable by the callback interfaces and
72  * the {@link org.springframework.jdbc.support.SQLExceptionTranslator}
73  * interface, there should be no need to subclass it.
74  *
75  * <p>All operations performed by this class are logged at debug level.
76  *
77  * @author Rod Johnson
78  * @author Juergen Hoeller
79  * @author Thomas Risberg
80  * @since May 3, 2001
81  * @see PreparedStatementCreator
82  * @see PreparedStatementSetter
83  * @see CallableStatementCreator
84  * @see PreparedStatementCallback
85  * @see CallableStatementCallback
86  * @see ResultSetExtractor
87  * @see RowCallbackHandler
88  * @see RowMapper
89  * @see org.springframework.jdbc.support.SQLExceptionTranslator
90  */
91 public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {
92
93     /** Custom NativeJdbcExtractor */
94     private NativeJdbcExtractor nativeJdbcExtractor;
95
96     /** If this variable is false, we will throw exceptions on SQL warnings */
97     private boolean ignoreWarnings = true;
98
99     /**
100      * If this variable is set to a non-zero value, it will be used for setting the
101      * fetchSize property on statements used for query processing.
102      */
103     private int fetchSize = 0;
104
105     /**
106      * If this variable is set to a non-zero value, it will be used for setting the
107      * maxRows property on statements used for query processing.
108      */
109     private int maxRows = 0;
110
111     /**
112      * If this variable is set to a non-zero value, it will be used for setting the
113      * queryTimeout property on statements used for query processing.
114      */
115     private int queryTimeout = 0;
116
117     /**
118      * If this variable is set to true then all results checking will be bypassed for any
119      * callable statement processing. This can be used to avoid a bug in some older Oracle
120      * JDBC drivers like 10.1.0.2.
121      */
122     private boolean skipResultsProcessing = false;
123
124
125     /**
126      * Construct a new JdbcTemplate for bean usage.
127      * <p>Note: The DataSource has to be set before using the instance.
128      * @see #setDataSource
129      */
130     public JdbcTemplate() {
131     }
132
133     /**
134      * Construct a new JdbcTemplate, given a DataSource to obtain connections from.
135      * Note: This will not trigger initialization of the exception translator.
136      * @param dataSource JDBC DataSource to obtain connections from
137      */
138     public JdbcTemplate(DataSource dataSource) {
139         setDataSource(dataSource);
140         afterPropertiesSet();
141     }
142
143     /**
144      * Construct a new JdbcTemplate, given a DataSource to obtain connections from.
145      * Note: Depending on the "lazyInit" flag, initialization of the exception translator
146      * will be triggered.
147      * @param dataSource JDBC DataSource to obtain connections from
148      * @param lazyInit whether to lazily initialize the SQLExceptionTranslator
149      */
150     public JdbcTemplate(DataSource dataSource, boolean lazyInit) {
151         setDataSource(dataSource);
152         setLazyInit(lazyInit);
153         afterPropertiesSet();
154     }
155
156
157     /**
158      * Set a NativeJdbcExtractor to extract native JDBC objects from wrapped handles.
159      * Useful if native Statement and/or ResultSet handles are expected for casting
160      * to database-specific implementation classes, but a connection pool that wraps
161      * JDBC objects is used (note: <i>any</i> pool will return wrapped Connections).
162      */
163     public void setNativeJdbcExtractor(NativeJdbcExtractor extractor) {
164         this.nativeJdbcExtractor = extractor;
165     }
166
167     /**
168      * Return the current NativeJdbcExtractor implementation.
169      */
170     public NativeJdbcExtractor getNativeJdbcExtractor() {
171         return this.nativeJdbcExtractor;
172     }
173
174     /**
175      * Set whether or not we want to ignore SQLWarnings.
176      * <p>Default is "true", swallowing and logging all warnings. Switch this flag
177      * to "false" to make the JdbcTemplate throw a SQLWarningException instead.
178      * @see java.sql.SQLWarning
179      * @see org.springframework.jdbc.SQLWarningException
180      * @see #handleWarnings
181      */
182     public void setIgnoreWarnings(boolean ignoreWarnings) {
183         this.ignoreWarnings = ignoreWarnings;
184     }
185
186     /**
187      * Return whether or not we ignore SQLWarnings.
188      */
189     public boolean isIgnoreWarnings() {
190         return this.ignoreWarnings;
191     }
192
193     /**
194      * Set the fetch size for this JdbcTemplate. This is important for processing
195      * large result sets: Setting this higher than the default value will increase
196      * processing speed at the cost of memory consumption; setting this lower can
197      * avoid transferring row data that will never be read by the application.
198      * <p>Default is 0, indicating to use the JDBC driver's default.
199      * @see java.sql.Statement#setFetchSize
200      */
201     public void setFetchSize(int fetchSize) {
202         this.fetchSize = fetchSize;
203     }
204
205     /**
206      * Return the fetch size specified for this JdbcTemplate.
207      */
208     public int getFetchSize() {
209         return this.fetchSize;
210     }
211
212     /**
213      * Set the maximum number of rows for this JdbcTemplate. This is important
214      * for processing subsets of large result sets, avoiding to read and hold
215      * the entire result set in the database or in the JDBC driver if we're
216      * never interested in the entire result in the first place (for example,
217      * when performing searches that might return a large number of matches).
218      * <p>Default is 0, indicating to use the JDBC driver's default.
219      * @see java.sql.Statement#setMaxRows
220      */
221     public void setMaxRows(int maxRows) {
222         this.maxRows = maxRows;
223     }
224
225     /**
226      * Return the maximum number of rows specified for this JdbcTemplate.
227      */
228     public int getMaxRows() {
229         return this.maxRows;
230     }
231
232     /**
233      * Set the query timeout for statements that this JdbcTemplate executes.
234      * <p>Default is 0, indicating to use the JDBC driver's default.
235      * <p>Note: Any timeout specified here will be overridden by the remaining
236      * transaction timeout when executing within a transaction that has a
237      * timeout specified at the transaction level.
238      * @see java.sql.Statement#setQueryTimeout
239      */
240     public void setQueryTimeout(int queryTimeout) {
241         this.queryTimeout = queryTimeout;
242     }
243
244     /**
245      * Return the query timeout for statements that this JdbcTemplate executes.
246      */
247     public int getQueryTimeout() {
248         return this.queryTimeout;
249     }
250
251     /**
252      * Set whether results processing should be skipped. Can be used to optimize callable
253      * statement processing when we know that no results are being passed back - the processing
254      * of out parameter will still take place. This can be used to avoid a bug in some older
255      * Oracle JDBC drivers like 10.1.0.2.
256      */
257     public void setSkipResultsProcessing(boolean skipResultsProcessing) {
258         this.skipResultsProcessing = skipResultsProcessing;
259     }
260
261     /**
262      * Return whether results processing should be skipped.
263      */
264     public boolean isSkipResultsProcessing() {
265         return this.skipResultsProcessing;
266     }
267
268
269     //-------------------------------------------------------------------------
270 // Methods dealing with a plain java.sql.Connection
271 //-------------------------------------------------------------------------
272
273     public Object execute(ConnectionCallback action) throws DataAccessException {
274         Assert.notNull(action, "Callback object must not be null");
275
276         Connection con = DataSourceUtils.getConnection(getDataSource());
277         try {
278             Connection conToUse = con;
279             if (this.nativeJdbcExtractor != null) {
280                 // Extract native JDBC Connection, castable to OracleConnection or the like.
281 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
282             }
283             else {
284                 // Create close-suppressing Connection proxy, also preparing returned Statements.
285 conToUse = createConnectionProxy(con);
286             }
287             return action.doInConnection(conToUse);
288         }
289         catch (SQLException ex) {
290             // Release Connection early, to avoid potential connection pool deadlock
291 // in the case when the exception translator hasn't been initialized yet.
292 DataSourceUtils.releaseConnection(con, getDataSource());
293             con = null;
294             throw getExceptionTranslator().translate("ConnectionCallback", getSql(action), ex);
295         }
296         finally {
297             DataSourceUtils.releaseConnection(con, getDataSource());
298         }
299     }
300
301     /**
302      * Create a close-suppressing proxy for the given JDBC Connection.
303      * Called by the <code>execute</code> method.
304      * <p>The proxy also prepares returned JDBC Statements, applying
305      * statement settings such as fetch size, max rows, and query timeout.
306      * @param con the JDBC Connection to create a proxy for
307      * @return the Connection proxy
308      * @see java.sql.Connection#close()
309      * @see #execute(ConnectionCallback)
310      * @see #applyStatementSettings
311      */
312     protected Connection createConnectionProxy(Connection con) {
313         return (Connection ) Proxy.newProxyInstance(
314                 ConnectionProxy.class.getClassLoader(),
315                 new Class [] {ConnectionProxy.class},
316                 new CloseSuppressingInvocationHandler(con));
317     }
318
319
320     //-------------------------------------------------------------------------
321 // Methods dealing with static SQL (java.sql.Statement)
322 //-------------------------------------------------------------------------
323
324     public Object execute(StatementCallback action) throws DataAccessException {
325         Assert.notNull(action, "Callback object must not be null");
326
327         Connection con = DataSourceUtils.getConnection(getDataSource());
328         Statement stmt = null;
329         try {
330             Connection conToUse = con;
331             if (this.nativeJdbcExtractor != null &&
332                     this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
333                 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
334             }
335             stmt = conToUse.createStatement();
336             applyStatementSettings(stmt);
337             Statement stmtToUse = stmt;
338             if (this.nativeJdbcExtractor != null) {
339                 stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
340             }
341             Object result = action.doInStatement(stmtToUse);
342             handleWarnings(stmt.getWarnings());
343             return result;
344         }
345         catch (SQLException ex) {
346             // Release Connection early, to avoid potential connection pool deadlock
347 // in the case when the exception translator hasn't been initialized yet.
348 JdbcUtils.closeStatement(stmt);
349             stmt = null;
350             DataSourceUtils.releaseConnection(con, getDataSource());
351             con = null;
352             throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
353         }
354         finally {
355             JdbcUtils.closeStatement(stmt);
356             DataSourceUtils.releaseConnection(con, getDataSource());
357         }
358     }
359
360     public void execute(final String sql) throws DataAccessException {
361         if (logger.isDebugEnabled()) {
362             logger.debug("Executing SQL statement [" + sql + "]");
363         }
364
365         class ExecuteStatementCallback implements StatementCallback, SqlProvider {
366             public Object doInStatement(Statement stmt) throws SQLException {
367                 stmt.execute(sql);
368                 return null;
369             }
370             public String getSql() {
371                 return sql;
372             }
373         }
374         execute(new ExecuteStatementCallback());
375     }
376
377     public Object query(final String sql, final ResultSetExtractor rse) throws DataAccessException {
378         Assert.notNull(sql, "SQL must not be null");
379         Assert.notNull(rse, "ResultSetExtractor must not be null");
380         if (logger.isDebugEnabled()) {
381             logger.debug("Executing SQL query [" + sql + "]");
382         }
383
384         class QueryStatementCallback implements StatementCallback, SqlProvider {
385             public Object doInStatement(Statement stmt) throws SQLException {
386                 ResultSet rs = null;
387                 try {
388                     rs = stmt.executeQuery(sql);
389                     ResultSet rsToUse = rs;
390                     if (nativeJdbcExtractor != null) {
391                         rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
392                     }
393                     return rse.extractData(rsToUse);
394                 }
395                 finally {
396                     JdbcUtils.closeResultSet(rs);
397                 }
398             }
399             public String getSql() {
400                 return sql;
401             }
402         }
403         return execute(new QueryStatementCallback());
404     }
405
406     public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
407         query(sql, new RowCallbackHandlerResultSetExtractor(rch));
408     }
409
410     public List query(String sql, RowMapper rowMapper) throws DataAccessException {
411         return (List ) query(sql, new RowMapperResultSetExtractor(rowMapper));
412     }
413
414     public Map queryForMap(String sql) throws DataAccessException {
415         return (Map ) queryForObject(sql, getColumnMapRowMapper());
416     }
417
418     public Object queryForObject(String sql, RowMapper rowMapper) throws DataAccessException {
419         List results = query(sql, rowMapper);
420         return DataAccessUtils.requiredSingleResult(results);
421     }
422
423     public Object queryForObject(String sql, Class requiredType) throws DataAccessException {
424         return queryForObject(sql, getSingleColumnRowMapper(requiredType));
425     }
426
427     public long queryForLong(String sql) throws DataAccessException {
428         Number number = (Number ) queryForObject(sql, Long .class);
429         return (number != null ? number.longValue() : 0);
430     }
431
432     public int queryForInt(String sql) throws DataAccessException {
433         Number number = (Number ) queryForObject(sql, Integer .class);
434         return (number != null ? number.intValue() : 0);
435     }
436
437     public List queryForList(String sql, Class elementType) throws DataAccessException {
438         return query(sql, getSingleColumnRowMapper(elementType));
439     }
440
441     public List queryForList(String sql) throws DataAccessException {
442         return query(sql, getColumnMapRowMapper());
443     }
444
445     public SqlRowSet queryForRowSet(String sql) throws DataAccessException {
446         return (SqlRowSet) query(sql, new SqlRowSetResultSetExtractor());
447     }
448
449     public int update(final String sql) throws DataAccessException {
450         Assert.notNull(sql, "SQL must not be null");
451         if (logger.isDebugEnabled()) {
452             logger.debug("Executing SQL update [" + sql + "]");
453         }
454
455         class UpdateStatementCallback implements StatementCallback, SqlProvider {
456             public Object doInStatement(Statement stmt) throws SQLException {
457                 int rows = stmt.executeUpdate(sql);
458                 if (logger.isDebugEnabled()) {
459                     logger.debug("SQL update affected " + rows + " rows");
460                 }
461                 return new Integer (rows);
462             }
463             public String getSql() {
464                 return sql;
465             }
466         }
467         return ((Integer ) execute(new UpdateStatementCallback())).intValue();
468     }
469
470     public int[] batchUpdate(final String [] sql) throws DataAccessException {
471         Assert.notEmpty(sql, "SQL array must not be empty");
472         if (logger.isDebugEnabled()) {
473             logger.debug("Executing SQL batch update of " + sql.length + " statements");
474         }
475
476         class BatchUpdateStatementCallback implements StatementCallback, SqlProvider {
477             private String currSql;
478             public Object doInStatement(Statement stmt) throws SQLException , DataAccessException {
479                 int[] rowsAffected = new int[sql.length];
480                 if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
481                     for (int i = 0; i < sql.length; i++) {
482                         this.currSql = sql[i];
483                         stmt.addBatch(sql[i]);
484                     }
485                     rowsAffected = stmt.executeBatch();
486                 }
487                 else {
488                     for (int i = 0; i < sql.length; i++) {
489                         this.currSql = sql[i];
490                         if (!stmt.execute(sql[i])) {
491                             rowsAffected[i] = stmt.getUpdateCount();
492                         }
493                         else {
494                             throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]);
495                         }
496                     }
497                 }
498                 return rowsAffected;
499             }
500             public String getSql() {
501                 return currSql;
502             }
503         }
504         return (int[]) execute(new BatchUpdateStatementCallback());
505     }
506
507
508     //-------------------------------------------------------------------------
509 // Methods dealing with prepared statements
510 //-------------------------------------------------------------------------
511
512     public Object execute(PreparedStatementCreator psc, PreparedStatementCallback action)
513             throws DataAccessException {
514
515         Assert.notNull(psc, "PreparedStatementCreator must not be null");
516         Assert.notNull(action, "Callback object must not be null");
517         if (logger.isDebugEnabled()) {
518             String sql = getSql(psc);
519             logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));
520         }
521
522         Connection con = DataSourceUtils.getConnection(getDataSource());
523         PreparedStatement ps = null;
524         try {
525             Connection conToUse = con;
526             if (this.nativeJdbcExtractor != null &&
527                     this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) {
528                 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
529             }
530             ps = psc.createPreparedStatement(conToUse);
531             applyStatementSettings(ps);
532             PreparedStatement psToUse = ps;
533             if (this.nativeJdbcExtractor != null) {
534                 psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps);
535             }
536             Object result = action.doInPreparedStatement(psToUse);
537             handleWarnings(ps.getWarnings());
538             return result;
539         }
540         catch (SQLException ex) {
541             // Release Connection early, to avoid potential connection pool deadlock
542 // in the case when the exception translator hasn't been initialized yet.
543 if (psc instanceof ParameterDisposer) {
544                 ((ParameterDisposer) psc).cleanupParameters();
545             }
546             String sql = getSql(psc);
547             psc = null;
548             JdbcUtils.closeStatement(ps);
549             ps = null;
550             DataSourceUtils.releaseConnection(con, getDataSource());
551             con = null;
552             throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex);
553         }
554         finally {
555             if (psc instanceof ParameterDisposer) {
556                 ((ParameterDisposer) psc).cleanupParameters();
557             }
558             JdbcUtils.closeStatement(ps);
559             DataSourceUtils.releaseConnection(con, getDataSource());
560         }
561     }
562
563     public Object execute(String sql, PreparedStatementCallback action) throws DataAccessException {
564         return execute(new SimplePreparedStatementCreator(sql), action);
565     }
566
567     /**
568      * Query using a prepared statement, allowing for a PreparedStatementCreator
569      * and a PreparedStatementSetter. Most other query methods use this method,
570      * but application code will always work with either a creator or a setter.
571      * @param psc Callback handler that can create a PreparedStatement given a
572      * Connection
573      * @param pss object that knows how to set values on the prepared statement.
574      * If this is null, the SQL will be assumed to contain no bind parameters.
575      * @param rse object that will extract results.
576      * @return an arbitrary result object, as returned by the ResultSetExtractor
577      * @throws DataAccessException if there is any problem
578      */
579     public Object query(
580             PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor rse)
581             throws DataAccessException {
582
583         Assert.notNull(rse, "ResultSetExtractor must not be null");
584         logger.debug("Executing prepared SQL query");
585
586         return execute(psc, new PreparedStatementCallback() {
587             public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
588                 ResultSet rs = null;
589                 try {
590                     if (pss != null) {
591                         pss.setValues(ps);
592                     }
593                     rs = ps.executeQuery();
594                     ResultSet rsToUse = rs;
595                     if (nativeJdbcExtractor != null) {
596                         rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
597                     }
598                     return rse.extractData(rsToUse);
599                 }
600                 finally {
601                     JdbcUtils.closeResultSet(rs);
602                     if (pss instanceof ParameterDisposer) {
603                         ((ParameterDisposer) pss).cleanupParameters();
604                     }
605                 }
606             }
607         });
608     }
609
610     public Object query(PreparedStatementCreator psc, ResultSetExtractor rse) throws DataAccessException {
611         return query(psc, null, rse);
612     }
613
614     public Object query(String sql, PreparedStatementSetter pss, ResultSetExtractor rse) throws DataAccessException {
615         return query(new SimplePreparedStatementCreator(sql), pss, rse);
616     }
617
618     public Object query(String sql, Object [] args, int[] argTypes, ResultSetExtractor rse) throws DataAccessException {
619         return query(sql, new ArgTypePreparedStatementSetter(args, argTypes), rse);
620     }
621
622     public Object query(String sql, Object [] args, ResultSetExtractor rse) throws DataAccessException {
623         return query(sql, new ArgPreparedStatementSetter(args), rse);
624     }
625
626     public void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException {
627         query(psc, new RowCallbackHandlerResultSetExtractor(rch));
628     }
629
630     public void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException {
631         query(sql, pss, new RowCallbackHandlerResultSetExtractor(rch));
632     }
633
634     public void query(String sql, Object [] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException {
635         query(sql, new ArgTypePreparedStatementSetter(args, argTypes), rch);
636     }
637
638     public void query(String sql, Object [] args, RowCallbackHandler rch) throws DataAccessException {
639         query(sql, new ArgPreparedStatementSetter(args), rch);
640     }
641
642     public List query(PreparedStatementCreator psc, RowMapper rowMapper) throws DataAccessException {
643         return (List ) query(psc, new RowMapperResultSetExtractor(rowMapper));
644     }
645
646     public List query(String sql, PreparedStatementSetter pss, RowMapper rowMapper) throws DataAccessException {
647         return (List ) query(sql, pss, new RowMapperResultSetExtractor(rowMapper));
648     }
649
650     public List query(String sql, Object [] args, int[] argTypes, RowMapper rowMapper) throws DataAccessException {
651         return (List ) query(sql, args, argTypes, new RowMapperResultSetExtractor(rowMapper));
652     }
653
654     public List query(String sql, Object [] args, RowMapper rowMapper) throws DataAccessException {
655         return (List ) query(sql, args, new RowMapperResultSetExtractor(rowMapper));
656     }
657
658     public Object queryForObject(String sql, Object [] args, int[] argTypes, RowMapper rowMapper)
659             throws DataAccessException {
660
661         List results = (List ) query(sql, args, argTypes, new RowMapperResultSetExtractor(rowMapper, 1));
662         return DataAccessUtils.requiredSingleResult(results);
663     }
664
665     public Object queryForObject(String sql, Object [] args, RowMapper rowMapper) throws DataAccessException {
666         List results = (List ) query(sql, args, new RowMapperResultSetExtractor(rowMapper, 1));
667         return DataAccessUtils.requiredSingleResult(results);
668     }
669
670     public Object queryForObject(String sql, Object [] args, int[] argTypes, Class requiredType)
671             throws DataAccessException {
672
673         return queryForObject(sql, args, argTypes, getSingleColumnRowMapper(requiredType));
674     }
675
676     public Object queryForObject(String sql, Object [] args, Class requiredType) throws DataAccessException {
677         return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
678     }
679
680     public Map queryForMap(String sql, Object [] args, int[] argTypes) throws DataAccessException {
681         return (Map ) queryForObject(sql, args, argTypes, getColumnMapRowMapper());
682     }
683
684     public Map queryForMap(String sql, Object [] args) throws DataAccessException {
685         return (Map ) queryForObject(sql, args, getColumnMapRowMapper());
686     }
687
688     public long queryForLong(String sql, Object [] args, int[] argTypes) throws DataAccessException {
689         Number number = (Number ) queryForObject(sql, args, argTypes, Long .class);
690         return (number != null ? number.longValue() : 0);
691     }
692
693     public long queryForLong(String sql, Object [] args) throws DataAccessException {
694         Number number = (Number ) queryForObject(sql, args, Long .class);
695         return (number != null ? number.longValue() : 0);
696     }
697
698     public int queryForInt(String sql, Object [] args, int[] argTypes) throws DataAccessException {
699         Number number = (Number ) queryForObject(sql, args, argTypes, Integer .class);
700         return (number != null ? number.intValue() : 0);
701     }
702
703     public int queryForInt(String sql, Object [] args) throws DataAccessException {
704         Number number = (Number ) queryForObject(sql, args, Integer .class);
705         return (number != null ? number.intValue() : 0);
706     }
707
708     public List queryForList(String sql, Object [] args, int[] argTypes, Class elementType) throws DataAccessException {
709         return query(sql, args, argTypes, getSingleColumnRowMapper(elementType));
710     }
711
712     public List queryForList(String sql, Object [] args, Class elementType) throws DataAccessException {
713         return query(sql, args, getSingleColumnRowMapper(elementType));
714     }
715
716     public List queryForList(String sql, Object [] args, int[] argTypes) throws DataAccessException {
717         return query(sql, args, argTypes, getColumnMapRowMapper());
718     }
719
720     public List queryForList(String sql, Object [] args) throws DataAccessException {
721         return query(sql, args, getColumnMapRowMapper());
722     }
723
724     public SqlRowSet queryForRowSet(String sql, Object [] args, int[] argTypes) throws DataAccessException {
725         return (SqlRowSet) query(sql, args, argTypes, new SqlRowSetResultSetExtractor());
726     }
727
728     public SqlRowSet queryForRowSet(String sql, Object [] args) throws DataAccessException {
729         return (SqlRowSet) query(sql, args, new SqlRowSetResultSetExtractor());
730     }
731
732     protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
733             throws DataAccessException {
734
735         logger.debug("Executing prepared SQL update");
736
737         Integer result = (Integer ) execute(psc, new PreparedStatementCallback() {
738             public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
739                 try {
740                     if (pss != null) {
741                         pss.setValues(ps);
742                     }
743                     int rows = ps.executeUpdate();
744                     if (logger.isDebugEnabled()) {
745                         logger.debug("SQL update affected " + rows + " rows");
746                     }
747                     return new Integer (rows);
748                 }
749                 finally {
750                     if (pss instanceof ParameterDisposer) {
751                         ((ParameterDisposer) pss).cleanupParameters();
752                     }
753                 }
754             }
755         });
756         return result.intValue();
757     }
758
759     public int update(PreparedStatementCreator psc) throws DataAccessException {
760         return update(psc, (PreparedStatementSetter) null);
761     }
762
763     public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
764             throws DataAccessException {
765
766         Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
767         logger.debug("Executing SQL update and returning generated keys");
768
769         Integer result = (Integer ) execute(psc, new PreparedStatementCallback() {
770             public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
771                 int rows = ps.executeUpdate();
772                 List generatedKeys = generatedKeyHolder.getKeyList();
773                 generatedKeys.clear();
774                 ResultSet keys = ps.getGeneratedKeys();
775                 if (keys != null) {
776                     try {
777                         RowMapper rowMapper = getColumnMapRowMapper();
778                         RowMapperResultSetExtractor rse = new RowMapperResultSetExtractor(rowMapper, 1);
779                         generatedKeys.addAll((List ) rse.extractData(keys));
780                     }
781                     finally {
782                         JdbcUtils.closeResultSet(keys);
783                     }
784                 }
785                 if (logger.isDebugEnabled()) {
786                     logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
787                 }
788                 return new Integer (rows);
789             }
790         });
791         return result.intValue();
792     }
793
794     public int update(String sql, PreparedStatementSetter pss) throws DataAccessException {
795         return update(new SimplePreparedStatementCreator(sql), pss);
796     }
797
798     public int update(String sql, Object [] args, int[] argTypes) throws DataAccessException {
799         return update(sql, new ArgTypePreparedStatementSetter(args, argTypes));
800     }
801
802     public int update(String sql, Object [] args) throws DataAccessException {
803         return update(sql, new ArgPreparedStatementSetter(args));
804     }
805
806     public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
807         if (logger.isDebugEnabled()) {
808             logger.debug("Executing SQL batch update [" + sql + "]");
809         }
810
811         return (int[]) execute(sql, new PreparedStatementCallback() {
812             public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
813                 try {
814                     int batchSize = pss.getBatchSize();
815                     InterruptibleBatchPreparedStatementSetter ipss =
816                             (pss instanceof InterruptibleBatchPreparedStatementSetter ?
817                             (InterruptibleBatchPreparedStatementSetter) pss : null);
818                     if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
819                         for (int i = 0; i < batchSize; i++) {
820                             pss.setValues(ps, i);
821                             if (ipss != null && ipss.isBatchExhausted(i)) {
822                                 break;
823                             }
824                             else {
825                                 ps.addBatch();
826                             }
827                         }
828                         return ps.executeBatch();
829                     }
830                     else {
831                         int[] rowsAffected = new int[batchSize];
832                         for (int i = 0; i < batchSize; i++) {
833                             pss.setValues(ps, i);
834                             rowsAffected[i] = ps.executeUpdate();
835                         }
836                         return rowsAffected;
837                     }
838                 }
839                 finally {
840                     if (pss instanceof ParameterDisposer) {
841                         ((ParameterDisposer) pss).cleanupParameters();
842                     }
843                 }
844             }
845         });
846     }
847
848
849     //-------------------------------------------------------------------------
850 // Methods dealing with callable statements
851 //-------------------------------------------------------------------------
852
853     public Object execute(CallableStatementCreator csc, CallableStatementCallback action)
854             throws DataAccessException {
855
856         Assert.notNull(csc, "CallableStatementCreator must not be null");
857         Assert.notNull(action, "Callback object must not be null");
858         if (logger.isDebugEnabled()) {
859             String sql = getSql(csc);
860             logger.debug("Calling stored procedure" + (sql != null ? " [" + sql + "]" : ""));
861         }
862
863         Connection con = DataSourceUtils.getConnection(getDataSource());
864         CallableStatement cs = null;
865         try {
866             Connection conToUse = con;
867             if (this.nativeJdbcExtractor != null) {
868                 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
869             }
870             cs = csc.createCallableStatement(conToUse);
871             applyStatementSettings(cs);
872             CallableStatement csToUse = cs;
873             if (this.nativeJdbcExtractor != null) {
874                 csToUse = this.nativeJdbcExtractor.getNativeCallableStatement(cs);
875             }
876             Object result = action.doInCallableStatement(csToUse);
877             handleWarnings(cs.getWarnings());
878             return result;
879         }
880         catch (SQLException ex) {
881             // Release Connection early, to avoid potential connection pool deadlock
882 // in the case when the exception translator hasn't been initialized yet.
883 if (csc instanceof ParameterDisposer) {
884                 ((ParameterDisposer) csc).cleanupParameters();
885             }
886             String sql = getSql(csc);
887             csc = null;
888             JdbcUtils.closeStatement(cs);
889             cs = null;
890             DataSourceUtils.releaseConnection(con, getDataSource());
891             con = null;
892             throw getExceptionTranslator().translate("CallableStatementCallback", sql, ex);
893         }
894         finally {
895             if (csc instanceof ParameterDisposer) {
896                 ((ParameterDisposer) csc).cleanupParameters();
897             }
898             JdbcUtils.closeStatement(cs);
899             DataSourceUtils.releaseConnection(con, getDataSource());
900         }
901     }
902
903     public Object execute(String callString, CallableStatementCallback action) throws DataAccessException {
904         return execute(new SimpleCallableStatementCreator(callString), action);
905     }
906
907     public Map call(CallableStatementCreator csc, final List declaredParameters) throws DataAccessException {
908         return (Map ) execute(csc, new CallableStatementCallback() {
909             public Object doInCallableStatement(CallableStatement cs) throws SQLException {
910                 boolean retVal = cs.execute();
911                 int updateCount = cs.getUpdateCount();
912                 if (logger.isDebugEnabled()) {
913                     logger.debug("CallableStatement.execute() returned '" + retVal + "'");
914                     logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
915                 }
916                 Map returnedResults = new HashMap ();
917                 if (retVal || updateCount != -1) {
918                     returnedResults.putAll(extractReturnedResultSets(cs, declaredParameters, updateCount));
919                 }
920                 returnedResults.putAll(extractOutputParameters(cs, declaredParameters));
921                 return returnedResults;
922             }
923         });
924     }
925
926     /**
927      * Extract returned ResultSets from the completed stored procedure.
928      * @param cs JDBC wrapper for the stored procedure
929      * @param parameters Parameter list for the stored procedure
930      * @return Map that contains returned results
931      */
932     protected Map extractReturnedResultSets(CallableStatement cs, List parameters, int updateCount)
933             throws SQLException {
934
935         Map returnedResults = new HashMap ();
936         int rsIndex = 0;
937         boolean moreResults;
938         if (!skipResultsProcessing) {
939             do {
940                 if (updateCount == -1) {
941                     Object param = null;
942                     if (parameters != null && parameters.size() > rsIndex) {
943                         param = parameters.get(rsIndex);
944                     }
945                     if (param instanceof SqlReturnResultSet) {
946                         SqlReturnResultSet rsParam = (SqlReturnResultSet) param;
947                         returnedResults.putAll(processResultSet(cs.getResultSet(), rsParam));
948                     }
949                     else {
950                         logger.warn("Results returned from stored procedure but a corresponding " +
951                                 "SqlOutParameter/SqlReturnResultSet parameter was not declared");
952                     }
953                     rsIndex++;
954                 }
955                 moreResults = cs.getMoreResults();
956                 updateCount = cs.getUpdateCount();
957                 if (logger.isDebugEnabled()) {
958                     logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
959                 }
960             }
961             while (moreResults || updateCount != -1);
962         }
963         return returnedResults;
964     }
965
966     /**
967      * Extract output parameters from the completed stored procedure.
968      * @param cs JDBC wrapper for the stored procedure
969      * @param parameters parameter list for the stored procedure
970      * @return parameters to the stored procedure
971      * @return Map that contains returned results
972      */
973     protected Map extractOutputParameters(CallableStatement cs, List parameters) throws SQLException {
974         Map returnedResults = new HashMap ();
975         int sqlColIndex = 1;
976         for (int i = 0; i < parameters.size(); i++) {
977             Object param = parameters.get(i);
978             if (param instanceof SqlOutParameter) {
979                 SqlOutParameter outParam = (SqlOutParameter) param;
980                 if (outParam.isReturnTypeSupported()) {
981                     Object out = outParam.getSqlReturnType().getTypeValue(
982                             cs, sqlColIndex, outParam.getSqlType(), outParam.getTypeName());
983                     returnedResults.put(outParam.getName(), out);
984                 }
985                 else {
986                     Object out = cs.getObject(sqlColIndex);
987                     if (out instanceof ResultSet ) {
988                         if (outParam.isResultSetSupported()) {
989                             returnedResults.putAll(processResultSet((ResultSet ) out, outParam));
990                         }
991                         else {
992                             logger.warn("ResultSet returned from stored procedure but no corresponding SqlOutParameter " +
993                                     "with a ResultSetExtractor/RowCallbackHandler/RowMapper declared");
994                             returnedResults.put(outParam.getName(), "ResultSet was returned but not processed");
995                         }
996                     }
997                     else {
998                         returnedResults.put(outParam.getName(), out);
999                     }
1000                }
1001            }
1002            if (!(param instanceof SqlReturnResultSet)) {
1003                sqlColIndex++;
1004            }
1005        }
1006        return returnedResults;
1007    }
1008
1009    /**
1010     * Process the given ResultSet from a stored procedure.
1011     * @param rs the ResultSet to process
1012     * @param param the corresponding stored procedure parameter
1013     * @return Map that contains returned results
1014     */
1015    protected Map processResultSet(ResultSet rs, ResultSetSupportingSqlParameter param) throws SQLException {
1016        Map returnedResults = new HashMap ();
1017        try {
1018            ResultSet rsToUse = rs;
1019            if (this.nativeJdbcExtractor != null) {
1020                rsToUse = this.nativeJdbcExtractor.getNativeResultSet(rs);
1021            }
1022            if (param.getRowMapper() != null) {
1023                RowMapper rowMapper = param.getRowMapper();
1024                Object result = (new RowMapperResultSetExtractor(rowMapper)).extractData(rsToUse);
1025                returnedResults.put(param.getName(), result);
1026            }
1027            else if (param.getRowCallbackHandler() != null) {
1028                RowCallbackHandler rch = param.getRowCallbackHandler();
1029                (new RowCallbackHandlerResultSetExtractor(rch)).extractData(rsToUse);
1030                returnedResults.put(param.getName(), "ResultSet returned from stored procedure was processed");
1031            }
1032            else if (param.getResultSetExtractor() != null) {
1033                Object result = param.getResultSetExtractor().extractData(rsToUse);
1034                returnedResults.put(param.getName(), result);
1035            }
1036        }
1037        finally {
1038            JdbcUtils.closeResultSet(rs);
1039        }
1040        return returnedResults;
1041    }
1042
1043
1044    //-------------------------------------------------------------------------
1045 // Implementation hooks and helper methods
1046 //-------------------------------------------------------------------------
1047
1048    /**
1049     * Create a new RowMapper for reading columns as key-value pairs.
1050     * @return the RowMapper to use
1051     * @see ColumnMapRowMapper
1052     */
1053    protected RowMapper getColumnMapRowMapper() {
1054        return new ColumnMapRowMapper();
1055    }
1056
1057    /**
1058     * Create a new RowMapper for reading result objects from a single column.
1059     * @param requiredType the type that each result object is expected to match
1060     * @return the RowMapper to use
1061     * @see SingleColumnRowMapper
1062     */
1063    protected RowMapper getSingleColumnRowMapper(Class requiredType) {
1064        return new SingleColumnRowMapper(requiredType);
1065    }
1066
1067    /**
1068     * Prepare the given JDBC Statement (or PreparedStatement or CallableStatement),
1069     * applying statement settings such as fetch size, max rows, and query timeout.
1070     * @param stmt the JDBC Statement to prepare
1071     * @see #setFetchSize
1072     * @see #setMaxRows
1073     * @see #setQueryTimeout
1074     * @see org.springframework.jdbc.datasource.DataSourceUtils#applyTransactionTimeout
1075     */
1076    protected void applyStatementSettings(Statement stmt) throws SQLException {
1077        int fetchSize = getFetchSize();
1078        if (fetchSize > 0) {
1079            stmt.setFetchSize(fetchSize);
1080        }
1081        int maxRows = getMaxRows();
1082        if (maxRows > 0) {
1083            stmt.setMaxRows(maxRows);
1084        }
1085        DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());
1086    }
1087
1088    /**
1089     * Throw an SQLWarningException if we're not ignoring warnings,
1090     * else log the warnings (at debug level).
1091     * @param warning the warnings object from the current statement.
1092     * May be <code>null</code>, in which case this method does nothing.
1093     * @see org.springframework.jdbc.SQLWarningException
1094     */
1095    protected void handleWarnings(SQLWarning warning) throws SQLWarningException {
1096        if (warning != null) {
1097            if (isIgnoreWarnings()) {
1098                if (logger.isDebugEnabled()) {
1099                    SQLWarning warningToLog = warning;
1100                    while (warningToLog != null) {
1101                        logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + "', error code '" +
1102                                warningToLog.getErrorCode() + "', message [" + warningToLog.getMessage() + "]");
1103                        warningToLog = warningToLog.getNextWarning();
1104                    }
1105                }
1106            }
1107            else {
1108                throw new SQLWarningException("Warning not ignored", warning);
1109            }
1110        }
1111    }
1112
1113    /**
1114     * Determine SQL from potential provider object.
1115     * @param sqlProvider object that's potentially a SqlProvider
1116     * @return the SQL string, or <code>null</code>
1117     * @see SqlProvider
1118     */
1119    private static String getSql(Object sqlProvider) {
1120        if (sqlProvider instanceof SqlProvider) {
1121            return ((SqlProvider) sqlProvider).getSql();
1122        }
1123        else {
1124            return null;
1125        }
1126    }
1127
1128
1129    /**
1130     * Invocation handler that suppresses close calls on JDBC COnnections.
1131     * Also prepares returned Statement (Prepared/CallbackStatement) objects.
1132     * @see java.sql.Connection#close()
1133     */
1134    private class CloseSuppressingInvocationHandler implements InvocationHandler {
1135
1136        private final Connection target;
1137
1138        public CloseSuppressingInvocationHandler(Connection target) {
1139            this.target = target;
1140        }
1141
1142        public Object invoke(Object proxy, Method method, Object [] args) throws Throwable {
1143            // Invocation on ConnectionProxy interface coming in...
1144
1145            if (method.getName().equals("getTargetConnection")) {
1146                // Handle getTargetConnection method: return underlying Connection.
1147 return this.target;
1148            }
1149            else if (method.getName().equals("equals")) {
1150                // Only consider equal when proxies are identical.
1151 return (proxy == args[0] ? Boolean.TRUE : Boolean.FALSE);
1152            }
1153            else if (method.getName().equals("hashCode")) {
1154                // Use hashCode of PersistenceManager proxy.
1155 return new Integer (hashCode());
1156            }
1157            else if (method.getName().equals("close")) {
1158                // Handle close method: suppress, not valid.
1159 return null;
1160            }
1161
1162            // Invoke method on target Connection.
1163 try {
1164                Object retVal = method.invoke(this.target, args);
1165
1166                // If return value is a JDBC Statement, apply statement settings
1167 // (fetch size, max rows, transaction timeout).
1168 if (retVal instanceof Statement ) {
1169                    applyStatementSettings(((Statement ) retVal));
1170                }
1171
1172                return retVal;
1173            }
1174            catch (InvocationTargetException ex) {
1175                throw ex.getTargetException();
1176            }
1177        }
1178    }
1179
1180
1181
1182    /**
1183     * Simple adapter for PreparedStatementCreator, allowing to use a plain SQL statement.
1184     */
1185    private static class SimplePreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
1186
1187        private final String sql;
1188
1189        public SimplePreparedStatementCreator(String sql) {
1190            Assert.notNull(sql, "SQL must not be null");
1191            this.sql = sql;
1192        }
1193
1194        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
1195            return con.prepareStatement(this.sql);
1196        }
1197
1198        public String getSql() {
1199            return sql;
1200        }
1201    }
1202
1203
1204    /**
1205     * Simple adapter for CallableStatementCreator, allowing to use a plain SQL statement.
1206     */
1207    private static class SimpleCallableStatementCreator implements CallableStatementCreator, SqlProvider {
1208
1209        private final String callString;
1210
1211        public SimpleCallableStatementCreator(String callString) {
1212            Assert.notNull(callString, "Call string must not be null");
1213            this.callString = callString;
1214        }
1215
1216        public CallableStatement createCallableStatement(Connection con) throws SQLException {
1217            return con.prepareCall(this.callString);
1218        }
1219
1220        public String getSql() {
1221            return callString;
1222        }
1223
1224    }
1225
1226
1227    /**
1228     * Adapter to enable use of a RowCallbackHandler inside a ResultSetExtractor.
1229     * <p>Uses a regular ResultSet, so we have to be careful when using it:
1230     * We don't use it for navigating since this could lead to unpredictable consequences.
1231     */
1232    private static class RowCallbackHandlerResultSetExtractor implements ResultSetExtractor {
1233
1234        private final RowCallbackHandler rch;
1235
1236        public RowCallbackHandlerResultSetExtractor(RowCallbackHandler rch) {
1237            this.rch = rch;
1238        }
1239
1240        public Object extractData(ResultSet rs) throws SQLException {
1241            while (rs.next()) {
1242                this.rch.processRow(rs);
1243            }
1244            return null;
1245        }
1246    }
1247
1248}
1249

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags