1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.BatchUpdateException;
24 import java.sql.CallableStatement;
25 import java.sql.Connection;
26 import java.sql.PreparedStatement;
27 import java.sql.ResultSet;
28 import java.sql.SQLException;
29 import java.sql.SQLWarning;
30 import java.sql.Statement;
31 import java.util.ArrayList;
32 import java.util.Collection;
33 import java.util.Collections;
34 import java.util.HashMap;
35 import java.util.LinkedHashMap;
36 import java.util.List;
37 import java.util.Map;
38 import javax.sql.DataSource;
39
40 import org.springframework.dao.DataAccessException;
41 import org.springframework.dao.InvalidDataAccessApiUsageException;
42 import org.springframework.dao.support.DataAccessUtils;
43 import org.springframework.jdbc.SQLWarningException;
44 import org.springframework.jdbc.datasource.ConnectionProxy;
45 import org.springframework.jdbc.datasource.DataSourceUtils;
46 import org.springframework.jdbc.support.JdbcAccessor;
47 import org.springframework.jdbc.support.JdbcUtils;
48 import org.springframework.jdbc.support.KeyHolder;
49 import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
50 import org.springframework.jdbc.support.rowset.SqlRowSet;
51 import org.springframework.util.Assert;
52 import org.springframework.util.LinkedCaseInsensitiveMap;
53 import org.springframework.util.StringUtils;
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100 public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {
101
102 private static final String RETURN_RESULT_SET_PREFIX = "#result-set-";
103
104 private static final String RETURN_UPDATE_COUNT_PREFIX = "#update-count-";
105
106
107
108 private NativeJdbcExtractor nativeJdbcExtractor;
109
110
111 private boolean ignoreWarnings = true;
112
113
114
115
116
117 private int fetchSize = -1;
118
119
120
121
122
123 private int maxRows = -1;
124
125
126
127
128
129 private int queryTimeout = -1;
130
131
132
133
134
135
136 private boolean skipResultsProcessing = false;
137
138
139
140
141
142
143
144 private boolean skipUndeclaredResults = false;
145
146
147
148
149
150
151 private boolean resultsMapCaseInsensitive = false;
152
153
154
155
156
157
158
159 public JdbcTemplate() {
160 }
161
162
163
164
165
166
167 public JdbcTemplate(DataSource dataSource) {
168 setDataSource(dataSource);
169 afterPropertiesSet();
170 }
171
172
173
174
175
176
177
178
179 public JdbcTemplate(DataSource dataSource, boolean lazyInit) {
180 setDataSource(dataSource);
181 setLazyInit(lazyInit);
182 afterPropertiesSet();
183 }
184
185
186
187
188
189
190
191
192 public void setNativeJdbcExtractor(NativeJdbcExtractor extractor) {
193 this.nativeJdbcExtractor = extractor;
194 }
195
196
197
198
199 public NativeJdbcExtractor getNativeJdbcExtractor() {
200 return this.nativeJdbcExtractor;
201 }
202
203
204
205
206
207
208
209
210
211 public void setIgnoreWarnings(boolean ignoreWarnings) {
212 this.ignoreWarnings = ignoreWarnings;
213 }
214
215
216
217
218 public boolean isIgnoreWarnings() {
219 return this.ignoreWarnings;
220 }
221
222
223
224
225
226
227
228
229
230
231 public void setFetchSize(int fetchSize) {
232 this.fetchSize = fetchSize;
233 }
234
235
236
237
238 public int getFetchSize() {
239 return this.fetchSize;
240 }
241
242
243
244
245
246
247
248
249
250
251
252 public void setMaxRows(int maxRows) {
253 this.maxRows = maxRows;
254 }
255
256
257
258
259 public int getMaxRows() {
260 return this.maxRows;
261 }
262
263
264
265
266
267
268
269
270
271
272 public void setQueryTimeout(int queryTimeout) {
273 this.queryTimeout = queryTimeout;
274 }
275
276
277
278
279 public int getQueryTimeout() {
280 return this.queryTimeout;
281 }
282
283
284
285
286
287
288
289 public void setSkipResultsProcessing(boolean skipResultsProcessing) {
290 this.skipResultsProcessing = skipResultsProcessing;
291 }
292
293
294
295
296 public boolean isSkipResultsProcessing() {
297 return this.skipResultsProcessing;
298 }
299
300
301
302
303 public void setSkipUndeclaredResults(boolean skipUndeclaredResults) {
304 this.skipUndeclaredResults = skipUndeclaredResults;
305 }
306
307
308
309
310 public boolean isSkipUndeclaredResults() {
311 return this.skipUndeclaredResults;
312 }
313
314
315
316
317
318 public void setResultsMapCaseInsensitive(boolean resultsMapCaseInsensitive) {
319 this.resultsMapCaseInsensitive = resultsMapCaseInsensitive;
320 }
321
322
323
324
325
326 public boolean isResultsMapCaseInsensitive() {
327 return this.resultsMapCaseInsensitive;
328 }
329
330
331
332
333
334
335 @Override
336 public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {
337 Assert.notNull(action, "Callback object must not be null");
338
339 Connection con = DataSourceUtils.getConnection(getDataSource());
340 try {
341 Connection conToUse = con;
342 if (this.nativeJdbcExtractor != null) {
343
344 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
345 }
346 else {
347
348 conToUse = createConnectionProxy(con);
349 }
350 return action.doInConnection(conToUse);
351 }
352 catch (SQLException ex) {
353
354
355 DataSourceUtils.releaseConnection(con, getDataSource());
356 con = null;
357 throw getExceptionTranslator().translate("ConnectionCallback", getSql(action), ex);
358 }
359 finally {
360 DataSourceUtils.releaseConnection(con, getDataSource());
361 }
362 }
363
364
365
366
367
368
369
370
371
372
373
374
375 protected Connection createConnectionProxy(Connection con) {
376 return (Connection) Proxy.newProxyInstance(
377 ConnectionProxy.class.getClassLoader(),
378 new Class<?>[] {ConnectionProxy.class},
379 new CloseSuppressingInvocationHandler(con));
380 }
381
382
383
384
385
386
387 @Override
388 public <T> T execute(StatementCallback<T> action) throws DataAccessException {
389 Assert.notNull(action, "Callback object must not be null");
390
391 Connection con = DataSourceUtils.getConnection(getDataSource());
392 Statement stmt = null;
393 try {
394 Connection conToUse = con;
395 if (this.nativeJdbcExtractor != null &&
396 this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
397 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
398 }
399 stmt = conToUse.createStatement();
400 applyStatementSettings(stmt);
401 Statement stmtToUse = stmt;
402 if (this.nativeJdbcExtractor != null) {
403 stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
404 }
405 T result = action.doInStatement(stmtToUse);
406 handleWarnings(stmt);
407 return result;
408 }
409 catch (SQLException ex) {
410
411
412 JdbcUtils.closeStatement(stmt);
413 stmt = null;
414 DataSourceUtils.releaseConnection(con, getDataSource());
415 con = null;
416 throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
417 }
418 finally {
419 JdbcUtils.closeStatement(stmt);
420 DataSourceUtils.releaseConnection(con, getDataSource());
421 }
422 }
423
424 @Override
425 public void execute(final String sql) throws DataAccessException {
426 if (logger.isDebugEnabled()) {
427 logger.debug("Executing SQL statement [" + sql + "]");
428 }
429 class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
430 @Override
431 public Object doInStatement(Statement stmt) throws SQLException {
432 stmt.execute(sql);
433 return null;
434 }
435 @Override
436 public String getSql() {
437 return sql;
438 }
439 }
440 execute(new ExecuteStatementCallback());
441 }
442
443 @Override
444 public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
445 Assert.notNull(sql, "SQL must not be null");
446 Assert.notNull(rse, "ResultSetExtractor must not be null");
447 if (logger.isDebugEnabled()) {
448 logger.debug("Executing SQL query [" + sql + "]");
449 }
450 class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
451 @Override
452 public T doInStatement(Statement stmt) throws SQLException {
453 ResultSet rs = null;
454 try {
455 rs = stmt.executeQuery(sql);
456 ResultSet rsToUse = rs;
457 if (nativeJdbcExtractor != null) {
458 rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
459 }
460 return rse.extractData(rsToUse);
461 }
462 finally {
463 JdbcUtils.closeResultSet(rs);
464 }
465 }
466 @Override
467 public String getSql() {
468 return sql;
469 }
470 }
471 return execute(new QueryStatementCallback());
472 }
473
474 @Override
475 public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
476 query(sql, new RowCallbackHandlerResultSetExtractor(rch));
477 }
478
479 @Override
480 public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
481 return query(sql, new RowMapperResultSetExtractor<T>(rowMapper));
482 }
483
484 @Override
485 public Map<String, Object> queryForMap(String sql) throws DataAccessException {
486 return queryForObject(sql, getColumnMapRowMapper());
487 }
488
489 @Override
490 public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException {
491 List<T> results = query(sql, rowMapper);
492 return DataAccessUtils.requiredSingleResult(results);
493 }
494
495 @Override
496 public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException {
497 return queryForObject(sql, getSingleColumnRowMapper(requiredType));
498 }
499
500 @Override
501 @Deprecated
502 public long queryForLong(String sql) throws DataAccessException {
503 Number number = queryForObject(sql, Long.class);
504 return (number != null ? number.longValue() : 0);
505 }
506
507 @Override
508 @Deprecated
509 public int queryForInt(String sql) throws DataAccessException {
510 Number number = queryForObject(sql, Integer.class);
511 return (number != null ? number.intValue() : 0);
512 }
513
514 @Override
515 public <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException {
516 return query(sql, getSingleColumnRowMapper(elementType));
517 }
518
519 @Override
520 public List<Map<String, Object>> queryForList(String sql) throws DataAccessException {
521 return query(sql, getColumnMapRowMapper());
522 }
523
524 @Override
525 public SqlRowSet queryForRowSet(String sql) throws DataAccessException {
526 return query(sql, new SqlRowSetResultSetExtractor());
527 }
528
529 @Override
530 public int update(final String sql) throws DataAccessException {
531 Assert.notNull(sql, "SQL must not be null");
532 if (logger.isDebugEnabled()) {
533 logger.debug("Executing SQL update [" + sql + "]");
534 }
535 class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider {
536 @Override
537 public Integer doInStatement(Statement stmt) throws SQLException {
538 int rows = stmt.executeUpdate(sql);
539 if (logger.isDebugEnabled()) {
540 logger.debug("SQL update affected " + rows + " rows");
541 }
542 return rows;
543 }
544 @Override
545 public String getSql() {
546 return sql;
547 }
548 }
549 return execute(new UpdateStatementCallback());
550 }
551
552 @Override
553 public int[] batchUpdate(final String... sql) throws DataAccessException {
554 Assert.notEmpty(sql, "SQL array must not be empty");
555 if (logger.isDebugEnabled()) {
556 logger.debug("Executing SQL batch update of " + sql.length + " statements");
557 }
558
559 class BatchUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider {
560
561 private String currSql;
562
563 @Override
564 public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException {
565 int[] rowsAffected = new int[sql.length];
566 if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
567 for (String sqlStmt : sql) {
568 this.currSql = appendSql(this.currSql, sqlStmt);
569 stmt.addBatch(sqlStmt);
570 }
571 try {
572 rowsAffected = stmt.executeBatch();
573 }
574 catch (BatchUpdateException ex) {
575 String batchExceptionSql = null;
576 for (int i = 0; i < ex.getUpdateCounts().length; i++) {
577 if (ex.getUpdateCounts()[i] == Statement.EXECUTE_FAILED) {
578 batchExceptionSql = appendSql(batchExceptionSql, sql[i]);
579 }
580 }
581 if (StringUtils.hasLength(batchExceptionSql)) {
582 this.currSql = batchExceptionSql;
583 }
584 throw ex;
585 }
586 }
587 else {
588 for (int i = 0; i < sql.length; i++) {
589 this.currSql = sql[i];
590 if (!stmt.execute(sql[i])) {
591 rowsAffected[i] = stmt.getUpdateCount();
592 }
593 else {
594 throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]);
595 }
596 }
597 }
598 return rowsAffected;
599 }
600
601 private String appendSql(String sql, String statement) {
602 return (StringUtils.isEmpty(sql) ? statement : sql + "; " + statement);
603 }
604
605 @Override
606 public String getSql() {
607 return this.currSql;
608 }
609 }
610
611 return execute(new BatchUpdateStatementCallback());
612 }
613
614
615
616
617
618
619 @Override
620 public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action)
621 throws DataAccessException {
622
623 Assert.notNull(psc, "PreparedStatementCreator must not be null");
624 Assert.notNull(action, "Callback object must not be null");
625 if (logger.isDebugEnabled()) {
626 String sql = getSql(psc);
627 logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));
628 }
629
630 Connection con = DataSourceUtils.getConnection(getDataSource());
631 PreparedStatement ps = null;
632 try {
633 Connection conToUse = con;
634 if (this.nativeJdbcExtractor != null &&
635 this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) {
636 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
637 }
638 ps = psc.createPreparedStatement(conToUse);
639 applyStatementSettings(ps);
640 PreparedStatement psToUse = ps;
641 if (this.nativeJdbcExtractor != null) {
642 psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps);
643 }
644 T result = action.doInPreparedStatement(psToUse);
645 handleWarnings(ps);
646 return result;
647 }
648 catch (SQLException ex) {
649
650
651 if (psc instanceof ParameterDisposer) {
652 ((ParameterDisposer) psc).cleanupParameters();
653 }
654 String sql = getSql(psc);
655 psc = null;
656 JdbcUtils.closeStatement(ps);
657 ps = null;
658 DataSourceUtils.releaseConnection(con, getDataSource());
659 con = null;
660 throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex);
661 }
662 finally {
663 if (psc instanceof ParameterDisposer) {
664 ((ParameterDisposer) psc).cleanupParameters();
665 }
666 JdbcUtils.closeStatement(ps);
667 DataSourceUtils.releaseConnection(con, getDataSource());
668 }
669 }
670
671 @Override
672 public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException {
673 return execute(new SimplePreparedStatementCreator(sql), action);
674 }
675
676
677
678
679
680
681
682
683
684
685
686
687
688 public <T> T query(
689 PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
690 throws DataAccessException {
691
692 Assert.notNull(rse, "ResultSetExtractor must not be null");
693 logger.debug("Executing prepared SQL query");
694
695 return execute(psc, new PreparedStatementCallback<T>() {
696 @Override
697 public T doInPreparedStatement(PreparedStatement ps) throws SQLException {
698 ResultSet rs = null;
699 try {
700 if (pss != null) {
701 pss.setValues(ps);
702 }
703 rs = ps.executeQuery();
704 ResultSet rsToUse = rs;
705 if (nativeJdbcExtractor != null) {
706 rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
707 }
708 return rse.extractData(rsToUse);
709 }
710 finally {
711 JdbcUtils.closeResultSet(rs);
712 if (pss instanceof ParameterDisposer) {
713 ((ParameterDisposer) pss).cleanupParameters();
714 }
715 }
716 }
717 });
718 }
719
720 @Override
721 public <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException {
722 return query(psc, null, rse);
723 }
724
725 @Override
726 public <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException {
727 return query(new SimplePreparedStatementCreator(sql), pss, rse);
728 }
729
730 @Override
731 public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException {
732 return query(sql, newArgTypePreparedStatementSetter(args, argTypes), rse);
733 }
734
735 @Override
736 public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException {
737 return query(sql, newArgPreparedStatementSetter(args), rse);
738 }
739
740 @Override
741 public <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) throws DataAccessException {
742 return query(sql, newArgPreparedStatementSetter(args), rse);
743 }
744
745 @Override
746 public void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException {
747 query(psc, new RowCallbackHandlerResultSetExtractor(rch));
748 }
749
750 @Override
751 public void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException {
752 query(sql, pss, new RowCallbackHandlerResultSetExtractor(rch));
753 }
754
755 @Override
756 public void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException {
757 query(sql, newArgTypePreparedStatementSetter(args, argTypes), rch);
758 }
759
760 @Override
761 public void query(String sql, Object[] args, RowCallbackHandler rch) throws DataAccessException {
762 query(sql, newArgPreparedStatementSetter(args), rch);
763 }
764
765 @Override
766 public void query(String sql, RowCallbackHandler rch, Object... args) throws DataAccessException {
767 query(sql, newArgPreparedStatementSetter(args), rch);
768 }
769
770 @Override
771 public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException {
772 return query(psc, new RowMapperResultSetExtractor<T>(rowMapper));
773 }
774
775 @Override
776 public <T> List<T> query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException {
777 return query(sql, pss, new RowMapperResultSetExtractor<T>(rowMapper));
778 }
779
780 @Override
781 public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException {
782 return query(sql, args, argTypes, new RowMapperResultSetExtractor<T>(rowMapper));
783 }
784
785 @Override
786 public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException {
787 return query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper));
788 }
789
790 @Override
791 public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
792 return query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper));
793 }
794
795 @Override
796 public <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper)
797 throws DataAccessException {
798
799 List<T> results = query(sql, args, argTypes, new RowMapperResultSetExtractor<T>(rowMapper, 1));
800 return DataAccessUtils.requiredSingleResult(results);
801 }
802
803 @Override
804 public <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException {
805 List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
806 return DataAccessUtils.requiredSingleResult(results);
807 }
808
809 @Override
810 public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
811 List<T> results = query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper, 1));
812 return DataAccessUtils.requiredSingleResult(results);
813 }
814
815 @Override
816 public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType)
817 throws DataAccessException {
818
819 return queryForObject(sql, args, argTypes, getSingleColumnRowMapper(requiredType));
820 }
821
822 @Override
823 public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException {
824 return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
825 }
826
827 @Override
828 public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException {
829 return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
830 }
831
832 @Override
833 public Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException {
834 return queryForObject(sql, args, argTypes, getColumnMapRowMapper());
835 }
836
837 @Override
838 public Map<String, Object> queryForMap(String sql, Object... args) throws DataAccessException {
839 return queryForObject(sql, args, getColumnMapRowMapper());
840 }
841
842 @Override
843 @Deprecated
844 public long queryForLong(String sql, Object[] args, int[] argTypes) throws DataAccessException {
845 Number number = queryForObject(sql, args, argTypes, Long.class);
846 return (number != null ? number.longValue() : 0);
847 }
848
849 @Override
850 @Deprecated
851 public long queryForLong(String sql, Object... args) throws DataAccessException {
852 Number number = queryForObject(sql, args, Long.class);
853 return (number != null ? number.longValue() : 0);
854 }
855
856 @Override
857 @Deprecated
858 public int queryForInt(String sql, Object[] args, int[] argTypes) throws DataAccessException {
859 Number number = queryForObject(sql, args, argTypes, Integer.class);
860 return (number != null ? number.intValue() : 0);
861 }
862
863 @Override
864 @Deprecated
865 public int queryForInt(String sql, Object... args) throws DataAccessException {
866 Number number = queryForObject(sql, args, Integer.class);
867 return (number != null ? number.intValue() : 0);
868 }
869
870 @Override
871 public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) throws DataAccessException {
872 return query(sql, args, argTypes, getSingleColumnRowMapper(elementType));
873 }
874
875 @Override
876 public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType) throws DataAccessException {
877 return query(sql, args, getSingleColumnRowMapper(elementType));
878 }
879
880 @Override
881 public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args) throws DataAccessException {
882 return query(sql, args, getSingleColumnRowMapper(elementType));
883 }
884
885 @Override
886 public List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException {
887 return query(sql, args, argTypes, getColumnMapRowMapper());
888 }
889
890 @Override
891 public List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException {
892 return query(sql, args, getColumnMapRowMapper());
893 }
894
895 @Override
896 public SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException {
897 return query(sql, args, argTypes, new SqlRowSetResultSetExtractor());
898 }
899
900 @Override
901 public SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException {
902 return query(sql, args, new SqlRowSetResultSetExtractor());
903 }
904
905 protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
906 throws DataAccessException {
907
908 logger.debug("Executing prepared SQL update");
909 return execute(psc, new PreparedStatementCallback<Integer>() {
910 @Override
911 public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
912 try {
913 if (pss != null) {
914 pss.setValues(ps);
915 }
916 int rows = ps.executeUpdate();
917 if (logger.isDebugEnabled()) {
918 logger.debug("SQL update affected " + rows + " rows");
919 }
920 return rows;
921 }
922 finally {
923 if (pss instanceof ParameterDisposer) {
924 ((ParameterDisposer) pss).cleanupParameters();
925 }
926 }
927 }
928 });
929 }
930
931 @Override
932 public int update(PreparedStatementCreator psc) throws DataAccessException {
933 return update(psc, (PreparedStatementSetter) null);
934 }
935
936 @Override
937 public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
938 throws DataAccessException {
939
940 Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
941 logger.debug("Executing SQL update and returning generated keys");
942
943 return execute(psc, new PreparedStatementCallback<Integer>() {
944 @Override
945 public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
946 int rows = ps.executeUpdate();
947 List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
948 generatedKeys.clear();
949 ResultSet keys = ps.getGeneratedKeys();
950 if (keys != null) {
951 try {
952 RowMapperResultSetExtractor<Map<String, Object>> rse =
953 new RowMapperResultSetExtractor<Map<String, Object>>(getColumnMapRowMapper(), 1);
954 generatedKeys.addAll(rse.extractData(keys));
955 }
956 finally {
957 JdbcUtils.closeResultSet(keys);
958 }
959 }
960 if (logger.isDebugEnabled()) {
961 logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
962 }
963 return rows;
964 }
965 });
966 }
967
968 @Override
969 public int update(String sql, PreparedStatementSetter pss) throws DataAccessException {
970 return update(new SimplePreparedStatementCreator(sql), pss);
971 }
972
973 @Override
974 public int update(String sql, Object[] args, int[] argTypes) throws DataAccessException {
975 return update(sql, newArgTypePreparedStatementSetter(args, argTypes));
976 }
977
978 @Override
979 public int update(String sql, Object... args) throws DataAccessException {
980 return update(sql, newArgPreparedStatementSetter(args));
981 }
982
983 @Override
984 public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
985 if (logger.isDebugEnabled()) {
986 logger.debug("Executing SQL batch update [" + sql + "]");
987 }
988
989 return execute(sql, new PreparedStatementCallback<int[]>() {
990 @Override
991 public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException {
992 try {
993 int batchSize = pss.getBatchSize();
994 InterruptibleBatchPreparedStatementSetter ipss =
995 (pss instanceof InterruptibleBatchPreparedStatementSetter ?
996 (InterruptibleBatchPreparedStatementSetter) pss : null);
997 if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
998 for (int i = 0; i < batchSize; i++) {
999 pss.setValues(ps, i);
1000 if (ipss != null && ipss.isBatchExhausted(i)) {
1001 break;
1002 }
1003 ps.addBatch();
1004 }
1005 return ps.executeBatch();
1006 }
1007 else {
1008 List<Integer> rowsAffected = new ArrayList<Integer>();
1009 for (int i = 0; i < batchSize; i++) {
1010 pss.setValues(ps, i);
1011 if (ipss != null && ipss.isBatchExhausted(i)) {
1012 break;
1013 }
1014 rowsAffected.add(ps.executeUpdate());
1015 }
1016 int[] rowsAffectedArray = new int[rowsAffected.size()];
1017 for (int i = 0; i < rowsAffectedArray.length; i++) {
1018 rowsAffectedArray[i] = rowsAffected.get(i);
1019 }
1020 return rowsAffectedArray;
1021 }
1022 }
1023 finally {
1024 if (pss instanceof ParameterDisposer) {
1025 ((ParameterDisposer) pss).cleanupParameters();
1026 }
1027 }
1028 }
1029 });
1030 }
1031
1032 @Override
1033 public int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException {
1034 return batchUpdate(sql, batchArgs, new int[0]);
1035 }
1036
1037 @Override
1038 public int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException {
1039 return BatchUpdateUtils.executeBatchUpdate(sql, batchArgs, argTypes, this);
1040 }
1041
1042 @Override
1043 public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize,
1044 final ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException {
1045
1046 if (logger.isDebugEnabled()) {
1047 logger.debug("Executing SQL batch update [" + sql + "] with a batch size of " + batchSize);
1048 }
1049 return execute(sql, new PreparedStatementCallback<int[][]>() {
1050 @Override
1051 public int[][] doInPreparedStatement(PreparedStatement ps) throws SQLException {
1052 List<int[]> rowsAffected = new ArrayList<int[]>();
1053 try {
1054 boolean batchSupported = true;
1055 if (!JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
1056 batchSupported = false;
1057 logger.warn("JDBC Driver does not support Batch updates; resorting to single statement execution");
1058 }
1059 int n = 0;
1060 for (T obj : batchArgs) {
1061 pss.setValues(ps, obj);
1062 n++;
1063 if (batchSupported) {
1064 ps.addBatch();
1065 if (n % batchSize == 0 || n == batchArgs.size()) {
1066 if (logger.isDebugEnabled()) {
1067 int batchIdx = (n % batchSize == 0) ? n / batchSize : (n / batchSize) + 1;
1068 int items = n - ((n % batchSize == 0) ? n / batchSize - 1 : (n / batchSize)) * batchSize;
1069 logger.debug("Sending SQL batch update #" + batchIdx + " with " + items + " items");
1070 }
1071 rowsAffected.add(ps.executeBatch());
1072 }
1073 }
1074 else {
1075 int i = ps.executeUpdate();
1076 rowsAffected.add(new int[] {i});
1077 }
1078 }
1079 int[][] result = new int[rowsAffected.size()][];
1080 for (int i = 0; i < result.length; i++) {
1081 result[i] = rowsAffected.get(i);
1082 }
1083 return result;
1084 } finally {
1085 if (pss instanceof ParameterDisposer) {
1086 ((ParameterDisposer) pss).cleanupParameters();
1087 }
1088 }
1089 }
1090 });
1091 }
1092
1093
1094
1095
1096
1097 @Override
1098 public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action)
1099 throws DataAccessException {
1100
1101 Assert.notNull(csc, "CallableStatementCreator must not be null");
1102 Assert.notNull(action, "Callback object must not be null");
1103 if (logger.isDebugEnabled()) {
1104 String sql = getSql(csc);
1105 logger.debug("Calling stored procedure" + (sql != null ? " [" + sql + "]" : ""));
1106 }
1107
1108 Connection con = DataSourceUtils.getConnection(getDataSource());
1109 CallableStatement cs = null;
1110 try {
1111 Connection conToUse = con;
1112 if (this.nativeJdbcExtractor != null) {
1113 conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
1114 }
1115 cs = csc.createCallableStatement(conToUse);
1116 applyStatementSettings(cs);
1117 CallableStatement csToUse = cs;
1118 if (this.nativeJdbcExtractor != null) {
1119 csToUse = this.nativeJdbcExtractor.getNativeCallableStatement(cs);
1120 }
1121 T result = action.doInCallableStatement(csToUse);
1122 handleWarnings(cs);
1123 return result;
1124 }
1125 catch (SQLException ex) {
1126
1127
1128 if (csc instanceof ParameterDisposer) {
1129 ((ParameterDisposer) csc).cleanupParameters();
1130 }
1131 String sql = getSql(csc);
1132 csc = null;
1133 JdbcUtils.closeStatement(cs);
1134 cs = null;
1135 DataSourceUtils.releaseConnection(con, getDataSource());
1136 con = null;
1137 throw getExceptionTranslator().translate("CallableStatementCallback", sql, ex);
1138 }
1139 finally {
1140 if (csc instanceof ParameterDisposer) {
1141 ((ParameterDisposer) csc).cleanupParameters();
1142 }
1143 JdbcUtils.closeStatement(cs);
1144 DataSourceUtils.releaseConnection(con, getDataSource());
1145 }
1146 }
1147
1148 @Override
1149 public <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException {
1150 return execute(new SimpleCallableStatementCreator(callString), action);
1151 }
1152
1153 @Override
1154 public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)
1155 throws DataAccessException {
1156
1157 final List<SqlParameter> updateCountParameters = new ArrayList<SqlParameter>();
1158 final List<SqlParameter> resultSetParameters = new ArrayList<SqlParameter>();
1159 final List<SqlParameter> callParameters = new ArrayList<SqlParameter>();
1160 for (SqlParameter parameter : declaredParameters) {
1161 if (parameter.isResultsParameter()) {
1162 if (parameter instanceof SqlReturnResultSet) {
1163 resultSetParameters.add(parameter);
1164 }
1165 else {
1166 updateCountParameters.add(parameter);
1167 }
1168 }
1169 else {
1170 callParameters.add(parameter);
1171 }
1172 }
1173 return execute(csc, new CallableStatementCallback<Map<String, Object>>() {
1174 @Override
1175 public Map<String, Object> doInCallableStatement(CallableStatement cs) throws SQLException {
1176 boolean retVal = cs.execute();
1177 int updateCount = cs.getUpdateCount();
1178 if (logger.isDebugEnabled()) {
1179 logger.debug("CallableStatement.execute() returned '" + retVal + "'");
1180 logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
1181 }
1182 Map<String, Object> returnedResults = createResultsMap();
1183 if (retVal || updateCount != -1) {
1184 returnedResults.putAll(extractReturnedResults(cs, updateCountParameters, resultSetParameters, updateCount));
1185 }
1186 returnedResults.putAll(extractOutputParameters(cs, callParameters));
1187 return returnedResults;
1188 }
1189 });
1190 }
1191
1192
1193
1194
1195
1196
1197
1198
1199 protected Map<String, Object> extractReturnedResults(CallableStatement cs,
1200 List<SqlParameter> updateCountParameters, List<SqlParameter> resultSetParameters, int updateCount)
1201 throws SQLException {
1202
1203 Map<String, Object> returnedResults = new HashMap<String, Object>();
1204 int rsIndex = 0;
1205 int updateIndex = 0;
1206 boolean moreResults;
1207 if (!this.skipResultsProcessing) {
1208 do {
1209 if (updateCount == -1) {
1210 if (resultSetParameters != null && resultSetParameters.size() > rsIndex) {
1211 SqlReturnResultSet declaredRsParam = (SqlReturnResultSet) resultSetParameters.get(rsIndex);
1212 returnedResults.putAll(processResultSet(cs.getResultSet(), declaredRsParam));
1213 rsIndex++;
1214 }
1215 else {
1216 if (!this.skipUndeclaredResults) {
1217 String rsName = RETURN_RESULT_SET_PREFIX + (rsIndex + 1);
1218 SqlReturnResultSet undeclaredRsParam = new SqlReturnResultSet(rsName, new ColumnMapRowMapper());
1219 if (logger.isDebugEnabled()) {
1220 logger.debug("Added default SqlReturnResultSet parameter named '" + rsName + "'");
1221 }
1222 returnedResults.putAll(processResultSet(cs.getResultSet(), undeclaredRsParam));
1223 rsIndex++;
1224 }
1225 }
1226 }
1227 else {
1228 if (updateCountParameters != null && updateCountParameters.size() > updateIndex) {
1229 SqlReturnUpdateCount ucParam = (SqlReturnUpdateCount) updateCountParameters.get(updateIndex);
1230 String declaredUcName = ucParam.getName();
1231 returnedResults.put(declaredUcName, updateCount);
1232 updateIndex++;
1233 }
1234 else {
1235 if (!this.skipUndeclaredResults) {
1236 String undeclaredName = RETURN_UPDATE_COUNT_PREFIX + (updateIndex + 1);
1237 if (logger.isDebugEnabled()) {
1238 logger.debug("Added default SqlReturnUpdateCount parameter named '" + undeclaredName + "'");
1239 }
1240 returnedResults.put(undeclaredName, updateCount);
1241 updateIndex++;
1242 }
1243 }
1244 }
1245 moreResults = cs.getMoreResults();
1246 updateCount = cs.getUpdateCount();
1247 if (logger.isDebugEnabled()) {
1248 logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
1249 }
1250 }
1251 while (moreResults || updateCount != -1);
1252 }
1253 return returnedResults;
1254 }
1255
1256
1257
1258
1259
1260
1261
1262 protected Map<String, Object> extractOutputParameters(CallableStatement cs, List<SqlParameter> parameters)
1263 throws SQLException {
1264
1265 Map<String, Object> returnedResults = new HashMap<String, Object>();
1266 int sqlColIndex = 1;
1267 for (SqlParameter param : parameters) {
1268 if (param instanceof SqlOutParameter) {
1269 SqlOutParameter outParam = (SqlOutParameter) param;
1270 if (outParam.isReturnTypeSupported()) {
1271 Object out = outParam.getSqlReturnType().getTypeValue(
1272 cs, sqlColIndex, outParam.getSqlType(), outParam.getTypeName());
1273 returnedResults.put(outParam.getName(), out);
1274 }
1275 else {
1276 Object out = cs.getObject(sqlColIndex);
1277 if (out instanceof ResultSet) {
1278 if (outParam.isResultSetSupported()) {
1279 returnedResults.putAll(processResultSet((ResultSet) out, outParam));
1280 }
1281 else {
1282 String rsName = outParam.getName();
1283 SqlReturnResultSet rsParam = new SqlReturnResultSet(rsName, new ColumnMapRowMapper());
1284 returnedResults.putAll(processResultSet((ResultSet) out, rsParam));
1285 if (logger.isDebugEnabled()) {
1286 logger.debug("Added default SqlReturnResultSet parameter named '" + rsName + "'");
1287 }
1288 }
1289 }
1290 else {
1291 returnedResults.put(outParam.getName(), out);
1292 }
1293 }
1294 }
1295 if (!(param.isResultsParameter())) {
1296 sqlColIndex++;
1297 }
1298 }
1299 return returnedResults;
1300 }
1301
1302
1303
1304
1305
1306
1307
1308 @SuppressWarnings({ "unchecked", "rawtypes" })
1309 protected Map<String, Object> processResultSet(ResultSet rs, ResultSetSupportingSqlParameter param) throws SQLException {
1310 if (rs == null) {
1311 return Collections.emptyMap();
1312 }
1313 Map<String, Object> returnedResults = new HashMap<String, Object>();
1314 try {
1315 ResultSet rsToUse = rs;
1316 if (this.nativeJdbcExtractor != null) {
1317 rsToUse = this.nativeJdbcExtractor.getNativeResultSet(rs);
1318 }
1319 if (param.getRowMapper() != null) {
1320 RowMapper rowMapper = param.getRowMapper();
1321 Object result = (new RowMapperResultSetExtractor(rowMapper)).extractData(rsToUse);
1322 returnedResults.put(param.getName(), result);
1323 }
1324 else if (param.getRowCallbackHandler() != null) {
1325 RowCallbackHandler rch = param.getRowCallbackHandler();
1326 (new RowCallbackHandlerResultSetExtractor(rch)).extractData(rsToUse);
1327 returnedResults.put(param.getName(), "ResultSet returned from stored procedure was processed");
1328 }
1329 else if (param.getResultSetExtractor() != null) {
1330 Object result = param.getResultSetExtractor().extractData(rsToUse);
1331 returnedResults.put(param.getName(), result);
1332 }
1333 }
1334 finally {
1335 JdbcUtils.closeResultSet(rs);
1336 }
1337 return returnedResults;
1338 }
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350 protected RowMapper<Map<String, Object>> getColumnMapRowMapper() {
1351 return new ColumnMapRowMapper();
1352 }
1353
1354
1355
1356
1357
1358
1359
1360 protected <T> RowMapper<T> getSingleColumnRowMapper(Class<T> requiredType) {
1361 return new SingleColumnRowMapper<T>(requiredType);
1362 }
1363
1364
1365
1366
1367
1368
1369
1370
1371 protected Map<String, Object> createResultsMap() {
1372 if (isResultsMapCaseInsensitive()) {
1373 return new LinkedCaseInsensitiveMap<Object>();
1374 }
1375 else {
1376 return new LinkedHashMap<String, Object>();
1377 }
1378 }
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390 protected void applyStatementSettings(Statement stmt) throws SQLException {
1391 int fetchSize = getFetchSize();
1392 if (fetchSize >= 0) {
1393 stmt.setFetchSize(fetchSize);
1394 }
1395 int maxRows = getMaxRows();
1396 if (maxRows >= 0) {
1397 stmt.setMaxRows(maxRows);
1398 }
1399 DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());
1400 }
1401
1402
1403
1404
1405
1406
1407
1408
1409 protected PreparedStatementSetter newArgPreparedStatementSetter(Object[] args) {
1410 return new ArgumentPreparedStatementSetter(args);
1411 }
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421 protected PreparedStatementSetter newArgTypePreparedStatementSetter(Object[] args, int[] argTypes) {
1422 return new ArgumentTypePreparedStatementSetter(args, argTypes);
1423 }
1424
1425
1426
1427
1428
1429
1430
1431
1432 protected void handleWarnings(Statement stmt) throws SQLException {
1433 if (isIgnoreWarnings()) {
1434 if (logger.isDebugEnabled()) {
1435 SQLWarning warningToLog = stmt.getWarnings();
1436 while (warningToLog != null) {
1437 logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + "', error code '" +
1438 warningToLog.getErrorCode() + "', message [" + warningToLog.getMessage() + "]");
1439 warningToLog = warningToLog.getNextWarning();
1440 }
1441 }
1442 }
1443 else {
1444 handleWarnings(stmt.getWarnings());
1445 }
1446 }
1447
1448
1449
1450
1451
1452
1453
1454 protected void handleWarnings(SQLWarning warning) throws SQLWarningException {
1455 if (warning != null) {
1456 throw new SQLWarningException("Warning not ignored", warning);
1457 }
1458 }
1459
1460
1461
1462
1463
1464
1465
1466 private static String getSql(Object sqlProvider) {
1467 if (sqlProvider instanceof SqlProvider) {
1468 return ((SqlProvider) sqlProvider).getSql();
1469 }
1470 else {
1471 return null;
1472 }
1473 }
1474
1475
1476
1477
1478
1479
1480
1481 private class CloseSuppressingInvocationHandler implements InvocationHandler {
1482
1483 private final Connection target;
1484
1485 public CloseSuppressingInvocationHandler(Connection target) {
1486 this.target = target;
1487 }
1488
1489 @Override
1490 @SuppressWarnings("rawtypes")
1491 public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
1492
1493
1494 if (method.getName().equals("equals")) {
1495
1496 return (proxy == args[0]);
1497 }
1498 else if (method.getName().equals("hashCode")) {
1499
1500 return System.identityHashCode(proxy);
1501 }
1502 else if (method.getName().equals("unwrap")) {
1503 if (((Class) args[0]).isInstance(proxy)) {
1504 return proxy;
1505 }
1506 }
1507 else if (method.getName().equals("isWrapperFor")) {
1508 if (((Class) args[0]).isInstance(proxy)) {
1509 return true;
1510 }
1511 }
1512 else if (method.getName().equals("close")) {
1513
1514 return null;
1515 }
1516 else if (method.getName().equals("isClosed")) {
1517 return false;
1518 }
1519 else if (method.getName().equals("getTargetConnection")) {
1520
1521 return this.target;
1522 }
1523
1524
1525 try {
1526 Object retVal = method.invoke(this.target, args);
1527
1528
1529
1530 if (retVal instanceof Statement) {
1531 applyStatementSettings(((Statement) retVal));
1532 }
1533
1534 return retVal;
1535 }
1536 catch (InvocationTargetException ex) {
1537 throw ex.getTargetException();
1538 }
1539 }
1540 }
1541
1542
1543
1544
1545
1546 private static class SimplePreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
1547
1548 private final String sql;
1549
1550 public SimplePreparedStatementCreator(String sql) {
1551 Assert.notNull(sql, "SQL must not be null");
1552 this.sql = sql;
1553 }
1554
1555 @Override
1556 public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
1557 return con.prepareStatement(this.sql);
1558 }
1559
1560 @Override
1561 public String getSql() {
1562 return this.sql;
1563 }
1564 }
1565
1566
1567
1568
1569
1570 private static class SimpleCallableStatementCreator implements CallableStatementCreator, SqlProvider {
1571
1572 private final String callString;
1573
1574 public SimpleCallableStatementCreator(String callString) {
1575 Assert.notNull(callString, "Call string must not be null");
1576 this.callString = callString;
1577 }
1578
1579 @Override
1580 public CallableStatement createCallableStatement(Connection con) throws SQLException {
1581 return con.prepareCall(this.callString);
1582 }
1583
1584 @Override
1585 public String getSql() {
1586 return this.callString;
1587 }
1588 }
1589
1590
1591
1592
1593
1594
1595
1596 private static class RowCallbackHandlerResultSetExtractor implements ResultSetExtractor<Object> {
1597
1598 private final RowCallbackHandler rch;
1599
1600 public RowCallbackHandlerResultSetExtractor(RowCallbackHandler rch) {
1601 this.rch = rch;
1602 }
1603
1604 @Override
1605 public Object extractData(ResultSet rs) throws SQLException {
1606 while (rs.next()) {
1607 this.rch.processRow(rs);
1608 }
1609 return null;
1610 }
1611 }
1612
1613 }