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.sql.BatchUpdateException;
20 import java.sql.CallableStatement;
21 import java.sql.Connection;
22 import java.sql.DatabaseMetaData;
23 import java.sql.PreparedStatement;
24 import java.sql.ResultSet;
25 import java.sql.SQLException;
26 import java.sql.SQLWarning;
27 import java.sql.Statement;
28 import java.sql.Types;
29 import java.util.ArrayList;
30 import java.util.Arrays;
31 import java.util.LinkedList;
32 import java.util.List;
33 import java.util.Map;
34 import javax.sql.DataSource;
35
36 import org.junit.Before;
37 import org.junit.Rule;
38 import org.junit.Test;
39 import org.junit.rules.ExpectedException;
40
41 import org.springframework.dao.DataAccessException;
42 import org.springframework.dao.InvalidDataAccessApiUsageException;
43 import org.springframework.jdbc.BadSqlGrammarException;
44 import org.springframework.jdbc.CannotGetJdbcConnectionException;
45 import org.springframework.jdbc.SQLWarningException;
46 import org.springframework.jdbc.UncategorizedSQLException;
47 import org.springframework.jdbc.core.support.AbstractInterruptibleBatchPreparedStatementSetter;
48 import org.springframework.jdbc.datasource.SingleConnectionDataSource;
49 import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
50 import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;
51 import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
52 import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractorAdapter;
53 import org.springframework.util.LinkedCaseInsensitiveMap;
54
55 import static org.hamcrest.Matchers.*;
56 import static org.junit.Assert.*;
57 import static org.mockito.BDDMockito.*;
58 import static org.springframework.tests.Matchers.*;
59
60
61
62
63
64
65
66
67
68 public class JdbcTemplateTests {
69
70 @Rule
71 public ExpectedException thrown = ExpectedException.none();
72
73 private Connection connection;
74 private DataSource dataSource;
75 private PreparedStatement preparedStatement;
76 private Statement statement;
77 private ResultSet resultSet;
78 private JdbcTemplate template;
79 private CallableStatement callableStatement;
80
81 @Before
82 public void setup() throws Exception {
83 this.connection = mock(Connection.class);
84 this.dataSource = mock(DataSource.class);
85 this.preparedStatement = mock(PreparedStatement.class);
86 this.statement = mock(Statement.class);
87 this.resultSet = mock(ResultSet.class);
88 this.template = new JdbcTemplate(this.dataSource);
89 this.callableStatement = mock(CallableStatement.class);
90 given(this.dataSource.getConnection()).willReturn(this.connection);
91 given(this.connection.prepareStatement(anyString())).willReturn(this.preparedStatement);
92 given(this.preparedStatement.executeQuery()).willReturn(this.resultSet);
93 given(this.preparedStatement.executeQuery(anyString())).willReturn(this.resultSet);
94 given(this.preparedStatement.getConnection()).willReturn(this.connection);
95 given(this.statement.getConnection()).willReturn(this.connection);
96 given(this.statement.executeQuery(anyString())).willReturn(this.resultSet);
97 given(this.connection.prepareCall(anyString())).willReturn(this.callableStatement);
98 given(this.callableStatement.getResultSet()).willReturn(this.resultSet);
99 }
100
101 @Test
102 public void testBeanProperties() throws Exception {
103 assertTrue("datasource ok", this.template.getDataSource() == this.dataSource);
104 assertTrue("ignores warnings by default", this.template.isIgnoreWarnings());
105 this.template.setIgnoreWarnings(false);
106 assertTrue("can set NOT to ignore warnings", !this.template.isIgnoreWarnings());
107 }
108
109 @Test
110 public void testUpdateCount() throws Exception {
111 final String sql = "UPDATE INVOICE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
112 int idParam = 11111;
113 given(this.preparedStatement.executeUpdate()).willReturn(1);
114 Dispatcher d = new Dispatcher(idParam, sql);
115 int rowsAffected = this.template.update(d);
116 assertTrue("1 update affected 1 row", rowsAffected == 1);
117 verify(this.preparedStatement).setInt(1, idParam);
118 verify(this.preparedStatement).close();
119 verify(this.connection).close();
120 }
121
122 @Test
123 public void testBogusUpdate() throws Exception {
124 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
125 final int idParam = 6666;
126
127
128 SQLException sqlException = new SQLException("bad update");
129 given(this.preparedStatement.executeUpdate()).willThrow(sqlException);
130
131 Dispatcher d = new Dispatcher(idParam, sql);
132 this.thrown.expect(UncategorizedSQLException.class);
133 this.thrown.expect(exceptionCause(equalTo(sqlException)));
134 try {
135 this.template.update(d);
136 }
137 finally {
138 verify(this.preparedStatement).setInt(1, idParam);
139 verify(this.preparedStatement).close();
140 verify(this.connection, atLeastOnce()).close();
141 }
142 }
143
144 @Test
145 public void testStringsWithStaticSql() throws Exception {
146 doTestStrings(false, null, null, null, null, new JdbcTemplateCallback() {
147 @Override
148 public void doInJdbcTemplate(JdbcTemplate template, String sql,
149 RowCallbackHandler rch) {
150 template.query(sql, rch);
151 }
152 });
153 }
154
155 @Test
156 public void testStringsWithStaticSqlAndFetchSizeAndMaxRows() throws Exception {
157 doTestStrings(false, 10, 20, 30, null, new JdbcTemplateCallback() {
158 @Override
159 public void doInJdbcTemplate(JdbcTemplate template, String sql,
160 RowCallbackHandler rch) {
161 template.query(sql, rch);
162 }
163 });
164 }
165
166 @Test
167 public void testStringsWithEmptyPreparedStatementSetter() throws Exception {
168 doTestStrings(true, null, null, null, null, new JdbcTemplateCallback() {
169 @Override
170 public void doInJdbcTemplate(JdbcTemplate template, String sql,
171 RowCallbackHandler rch) {
172 template.query(sql, (PreparedStatementSetter) null, rch);
173 }
174 });
175 }
176
177 @Test
178 public void testStringsWithPreparedStatementSetter() throws Exception {
179 final Integer argument = 99;
180 doTestStrings(true, null, null, null, argument, new JdbcTemplateCallback() {
181 @Override
182 public void doInJdbcTemplate(JdbcTemplate template, String sql,
183 RowCallbackHandler rch) {
184 template.query(sql, new PreparedStatementSetter() {
185 @Override
186 public void setValues(PreparedStatement ps) throws SQLException {
187 ps.setObject(1, argument);
188 }
189 }, rch);
190 }
191 });
192 }
193
194 @Test
195 public void testStringsWithEmptyPreparedStatementArgs() throws Exception {
196 doTestStrings(true, null, null, null, null, new JdbcTemplateCallback() {
197 @Override
198 public void doInJdbcTemplate(JdbcTemplate template, String sql,
199 RowCallbackHandler rch) {
200 template.query(sql, (Object[]) null, rch);
201 }
202 });
203 }
204
205 @Test
206 public void testStringsWithPreparedStatementArgs() throws Exception {
207 final Integer argument = 99;
208 doTestStrings(true, null, null, null, argument, new JdbcTemplateCallback() {
209 @Override
210 public void doInJdbcTemplate(JdbcTemplate template, String sql,
211 RowCallbackHandler rch) {
212 template.query(sql, new Object[] { argument }, rch);
213 }
214 });
215 }
216
217 private void doTestStrings(
218 boolean usePreparedStatement,
219 Integer fetchSize, Integer maxRows, Integer queryTimeout, Object argument,
220 JdbcTemplateCallback jdbcTemplateCallback)
221 throws Exception {
222
223 String sql = "SELECT FORENAME FROM CUSTMR";
224 String[] results = { "rod", "gary", " portia" };
225
226 class StringHandler implements RowCallbackHandler {
227 private List<String> list = new LinkedList<String>();
228 @Override
229 public void processRow(ResultSet rs) throws SQLException {
230 this.list.add(rs.getString(1));
231 }
232 public String[] getStrings() {
233 return this.list.toArray(new String[this.list.size()]);
234 }
235 }
236
237 given(this.resultSet.next()).willReturn(true, true, true, false);
238 given(this.resultSet.getString(1)).willReturn(results[0], results[1], results[2]);
239 given(this.connection.createStatement()).willReturn(this.preparedStatement);
240
241 StringHandler sh = new StringHandler();
242 JdbcTemplate template = new JdbcTemplate();
243 template.setDataSource(this.dataSource);
244 if (fetchSize != null) {
245 template.setFetchSize(fetchSize.intValue());
246 }
247 if (maxRows != null) {
248 template.setMaxRows(maxRows.intValue());
249 }
250 if (queryTimeout != null) {
251 template.setQueryTimeout(queryTimeout.intValue());
252 }
253 jdbcTemplateCallback.doInJdbcTemplate(template, sql, sh);
254
255
256 String[] forenames = sh.getStrings();
257 assertTrue("same length", forenames.length == results.length);
258 for (int i = 0; i < forenames.length; i++) {
259 assertTrue("Row " + i + " matches", forenames[i].equals(results[i]));
260 }
261
262 if (fetchSize != null) {
263 verify(this.preparedStatement).setFetchSize(fetchSize.intValue());
264 }
265 if (maxRows != null) {
266 verify(this.preparedStatement).setMaxRows(maxRows.intValue());
267 }
268 if (queryTimeout != null) {
269 verify(this.preparedStatement).setQueryTimeout(queryTimeout.intValue());
270 }
271 if (argument != null) {
272 verify(this.preparedStatement).setObject(1, argument);
273 }
274 verify(this.resultSet).close();
275 verify(this.preparedStatement).close();
276 verify(this.connection).close();
277 }
278
279 @Test
280 public void testLeaveConnectionOpenOnRequest() throws Exception {
281 String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";
282
283 given(this.resultSet.next()).willReturn(false);
284 given(this.connection.isClosed()).willReturn(false);
285 given(this.connection.createStatement()).willReturn(this.preparedStatement);
286
287 willThrow(new RuntimeException()).given(this.connection).close();
288
289 SingleConnectionDataSource scf = new SingleConnectionDataSource(this.dataSource.getConnection(), false);
290 this.template = new JdbcTemplate(scf, false);
291 RowCountCallbackHandler rcch = new RowCountCallbackHandler();
292 this.template.query(sql, rcch);
293
294 verify(this.resultSet).close();
295 verify(this.preparedStatement).close();
296 }
297
298 @Test
299 public void testConnectionCallback() throws Exception {
300 this.template.setNativeJdbcExtractor(new PlainNativeJdbcExtractor());
301 String result = this.template.execute(new ConnectionCallback<String>() {
302 @Override
303 public String doInConnection(Connection con) {
304 assertSame(JdbcTemplateTests.this.connection, con);
305 return "test";
306 }
307 });
308 assertEquals("test", result);
309 }
310
311 @Test
312 public void testConnectionCallbackWithStatementSettings() throws Exception {
313 String result = this.template.execute(new ConnectionCallback<String>() {
314 @Override
315 public String doInConnection(Connection con) throws SQLException {
316 PreparedStatement ps = con.prepareStatement("some SQL");
317 ps.setFetchSize(10);
318 ps.setMaxRows(20);
319 ps.close();
320 assertSame(JdbcTemplateTests.this.connection, new PlainNativeJdbcExtractor().getNativeConnection(con));
321 return "test";
322 }
323 });
324
325 assertEquals("test", result);
326 verify(this.preparedStatement).setFetchSize(10);
327 verify(this.preparedStatement).setMaxRows(20);
328 verify(this.preparedStatement).close();
329 verify(this.connection).close();
330 }
331
332 @Test
333 public void testCloseConnectionOnRequest() throws Exception {
334 String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";
335
336 given(this.resultSet.next()).willReturn(false);
337 given(this.connection.createStatement()).willReturn(this.preparedStatement);
338
339 RowCountCallbackHandler rcch = new RowCountCallbackHandler();
340 this.template.query(sql, rcch);
341
342 verify(this.resultSet).close();
343 verify(this.preparedStatement).close();
344 verify(this.connection).close();
345 }
346
347
348
349
350 @Test
351 public void testExceptionComesBack() throws Exception {
352 final String sql = "SELECT ID FROM CUSTMR";
353 final RuntimeException runtimeException = new RuntimeException("Expected");
354
355 given(this.resultSet.next()).willReturn(true);
356 given(this.connection.createStatement()).willReturn(this.preparedStatement);
357
358 this.thrown.expect(sameInstance(runtimeException));
359 try {
360 this.template.query(sql, new RowCallbackHandler() {
361 @Override
362 public void processRow(ResultSet rs) {
363 throw runtimeException;
364 }
365 });
366 }
367 finally {
368 verify(this.resultSet).close();
369 verify(this.preparedStatement).close();
370 verify(this.connection).close();
371 }
372 }
373
374
375
376
377 @Test
378 public void testSqlUpdate() throws Exception {
379 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
380 int rowsAffected = 33;
381
382 given(this.statement.executeUpdate(sql)).willReturn(rowsAffected);
383 given(this.connection.createStatement()).willReturn(this.statement);
384
385 int actualRowsAffected = this.template.update(sql);
386 assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected);
387 verify(this.statement).close();
388 verify(this.connection).close();
389 }
390
391
392
393
394 @Test
395 public void testSqlUpdateWithArguments() throws Exception {
396 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ? and PR = ?";
397 int rowsAffected = 33;
398 given(this.preparedStatement.executeUpdate()).willReturn(rowsAffected);
399
400 int actualRowsAffected = this.template.update(sql,
401 new Object[] {4, new SqlParameterValue(Types.NUMERIC, 2, new Float(1.4142))});
402 assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected);
403 verify(this.preparedStatement).setObject(1, 4);
404 verify(this.preparedStatement).setObject(2, new Float(1.4142), Types.NUMERIC, 2);
405 verify(this.preparedStatement).close();
406 verify(this.connection).close();
407 }
408
409 @Test
410 public void testSqlUpdateEncountersSqlException() throws Exception {
411 SQLException sqlException = new SQLException("bad update");
412 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
413
414 given(this.statement.executeUpdate(sql)).willThrow(sqlException);
415 given(this.connection.createStatement()).willReturn(this.statement);
416
417 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
418 try {
419 this.template.update(sql);
420 }
421 finally {
422 verify(this.statement).close();
423 verify(this.connection, atLeastOnce()).close();
424 }
425 }
426
427 @Test
428 public void testSqlUpdateWithThreadConnection() throws Exception {
429 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
430 int rowsAffected = 33;
431
432 given(this.statement.executeUpdate(sql)).willReturn(rowsAffected);
433 given(this.connection.createStatement()).willReturn(this.statement);
434
435 int actualRowsAffected = this.template.update(sql);
436 assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected);
437
438 verify(this.statement).close();
439 verify(this.connection).close();
440 }
441
442 @Test
443 public void testBatchUpdate() throws Exception {
444 final String[] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
445 "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2"};
446
447 given(this.statement.executeBatch()).willReturn(new int[] {1, 1});
448 mockDatabaseMetaData(true);
449 given(this.connection.createStatement()).willReturn(this.statement);
450
451 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
452
453 int[] actualRowsAffected = template.batchUpdate(sql);
454 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
455
456 verify(this.statement).addBatch(sql[0]);
457 verify(this.statement).addBatch(sql[1]);
458 verify(this.statement).close();
459 verify(this.connection, atLeastOnce()).close();
460 }
461
462 @Test
463 public void testBatchUpdateWithBatchFailure() throws Exception {
464 final String[] sql = {"A", "B", "C", "D"};
465 given(this.statement.executeBatch()).willThrow(
466 new BatchUpdateException(new int[] { 1, Statement.EXECUTE_FAILED, 1,
467 Statement.EXECUTE_FAILED }));
468 mockDatabaseMetaData(true);
469 given(this.connection.createStatement()).willReturn(this.statement);
470
471 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
472 try {
473 template.batchUpdate(sql);
474 }
475 catch (UncategorizedSQLException ex) {
476 assertThat(ex.getSql(), equalTo("B; D"));
477 }
478 }
479
480 @Test
481 public void testBatchUpdateWithNoBatchSupport() throws Exception {
482 final String[] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
483 "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2"};
484
485 given(this.statement.execute(sql[0])).willReturn(false);
486 given(this.statement.getUpdateCount()).willReturn(1, 1);
487 given(this.statement.execute(sql[1])).willReturn(false);
488
489 mockDatabaseMetaData(false);
490 given(this.connection.createStatement()).willReturn(this.statement);
491
492 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
493
494 int[] actualRowsAffected = template.batchUpdate(sql);
495 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
496
497 verify(this.statement, never()).addBatch(anyString());
498 verify(this.statement).close();
499 verify(this.connection, atLeastOnce()).close();
500 }
501
502 @Test
503 public void testBatchUpdateWithNoBatchSupportAndSelect() throws Exception {
504 final String[] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
505 "SELECT * FROM NOSUCHTABLE"};
506
507 given(this.statement.execute(sql[0])).willReturn(false);
508 given(this.statement.getUpdateCount()).willReturn(1);
509 given(this.statement.execute(sql[1])).willReturn(true);
510 mockDatabaseMetaData(false);
511 given(this.connection.createStatement()).willReturn(this.statement);
512
513 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
514 this.thrown.expect(InvalidDataAccessApiUsageException.class);
515 try {
516 template.batchUpdate(sql);
517 }
518 finally {
519 verify(this.statement, never()).addBatch(anyString());
520 verify(this.statement).close();
521 verify(this.connection, atLeastOnce()).close();
522 }
523 }
524
525 @Test
526 public void testBatchUpdateWithPreparedStatement() throws Exception {
527 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
528 final int[] ids = new int[] { 100, 200 };
529 final int[] rowsAffected = new int[] { 1, 2 };
530
531 given(this.preparedStatement.executeBatch()).willReturn(rowsAffected);
532 mockDatabaseMetaData(true);
533
534 BatchPreparedStatementSetter setter =
535 new BatchPreparedStatementSetter() {
536 @Override
537 public void setValues(PreparedStatement ps, int i)
538 throws SQLException {
539 ps.setInt(1, ids[i]);
540 }
541 @Override
542 public int getBatchSize() {
543 return ids.length;
544 }
545 };
546
547 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
548
549 int[] actualRowsAffected = template.batchUpdate(sql, setter);
550 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
551 assertEquals(rowsAffected[0], actualRowsAffected[0]);
552 assertEquals(rowsAffected[1], actualRowsAffected[1]);
553
554 verify(this.preparedStatement, times(2)).addBatch();
555 verify(this.preparedStatement).setInt(1, ids[0]);
556 verify(this.preparedStatement).setInt(1, ids[1]);
557 verify(this.preparedStatement).close();
558 verify(this.connection, atLeastOnce()).close();
559 }
560
561 @Test
562 public void testInterruptibleBatchUpdate() throws Exception {
563 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
564 final int[] ids = new int[] { 100, 200 };
565 final int[] rowsAffected = new int[] { 1, 2 };
566
567 given(this.preparedStatement.executeBatch()).willReturn(rowsAffected);
568 mockDatabaseMetaData(true);
569
570 BatchPreparedStatementSetter setter =
571 new InterruptibleBatchPreparedStatementSetter() {
572 @Override
573 public void setValues(PreparedStatement ps, int i) throws SQLException {
574 if (i < ids.length) {
575 ps.setInt(1, ids[i]);
576 }
577 }
578 @Override
579 public int getBatchSize() {
580 return 1000;
581 }
582 @Override
583 public boolean isBatchExhausted(int i) {
584 return (i >= ids.length);
585 }
586 };
587
588 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
589
590 int[] actualRowsAffected = template.batchUpdate(sql, setter);
591 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
592 assertEquals(rowsAffected[0], actualRowsAffected[0]);
593 assertEquals(rowsAffected[1], actualRowsAffected[1]);
594
595 verify(this.preparedStatement, times(2)).addBatch();
596 verify(this.preparedStatement).setInt(1, ids[0]);
597 verify(this.preparedStatement).setInt(1, ids[1]);
598 verify(this.preparedStatement).close();
599 verify(this.connection, atLeastOnce()).close();
600 }
601
602 @Test
603 public void testInterruptibleBatchUpdateWithBaseClass() throws Exception {
604 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
605 final int[] ids = new int[] { 100, 200 };
606 final int[] rowsAffected = new int[] { 1, 2 };
607
608 given(this.preparedStatement.executeBatch()).willReturn(rowsAffected);
609 mockDatabaseMetaData(true);
610
611 BatchPreparedStatementSetter setter =
612 new AbstractInterruptibleBatchPreparedStatementSetter() {
613 @Override
614 protected boolean setValuesIfAvailable(PreparedStatement ps, int i) throws SQLException {
615 if (i < ids.length) {
616 ps.setInt(1, ids[i]);
617 return true;
618 }
619 else {
620 return false;
621 }
622 }
623 };
624
625 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
626
627 int[] actualRowsAffected = template.batchUpdate(sql, setter);
628 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
629 assertEquals(rowsAffected[0], actualRowsAffected[0]);
630 assertEquals(rowsAffected[1], actualRowsAffected[1]);
631
632 verify(this.preparedStatement, times(2)).addBatch();
633 verify(this.preparedStatement).setInt(1, ids[0]);
634 verify(this.preparedStatement).setInt(1, ids[1]);
635 verify(this.preparedStatement).close();
636 verify(this.connection, atLeastOnce()).close();
637 }
638
639 @Test
640 public void testInterruptibleBatchUpdateWithBaseClassAndNoBatchSupport() throws Exception {
641 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
642 final int[] ids = new int[] { 100, 200 };
643 final int[] rowsAffected = new int[] { 1, 2 };
644
645 given(this.preparedStatement.executeUpdate()).willReturn(rowsAffected[0], rowsAffected[1]);
646 mockDatabaseMetaData(false);
647
648 BatchPreparedStatementSetter setter =
649 new AbstractInterruptibleBatchPreparedStatementSetter() {
650 @Override
651 protected boolean setValuesIfAvailable(PreparedStatement ps, int i) throws SQLException {
652 if (i < ids.length) {
653 ps.setInt(1, ids[i]);
654 return true;
655 }
656 else {
657 return false;
658 }
659 }
660 };
661
662 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
663
664 int[] actualRowsAffected = template.batchUpdate(sql, setter);
665 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
666 assertEquals(rowsAffected[0], actualRowsAffected[0]);
667 assertEquals(rowsAffected[1], actualRowsAffected[1]);
668
669 verify(this.preparedStatement, never()).addBatch();
670 verify(this.preparedStatement).setInt(1, ids[0]);
671 verify(this.preparedStatement).setInt(1, ids[1]);
672 verify(this.preparedStatement).close();
673 verify(this.connection, atLeastOnce()).close();
674 }
675
676 @Test
677 public void testBatchUpdateWithPreparedStatementAndNoBatchSupport() throws Exception {
678 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
679 final int[] ids = new int[] { 100, 200 };
680 final int[] rowsAffected = new int[] { 1, 2 };
681
682 given(this.preparedStatement.executeUpdate()).willReturn(rowsAffected[0], rowsAffected[1]);
683
684 BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
685 @Override
686 public void setValues(PreparedStatement ps, int i) throws SQLException {
687 ps.setInt(1, ids[i]);
688 }
689 @Override
690 public int getBatchSize() {
691 return ids.length;
692 }
693 };
694
695 int[] actualRowsAffected = this.template.batchUpdate(sql, setter);
696 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
697 assertEquals(rowsAffected[0], actualRowsAffected[0]);
698 assertEquals(rowsAffected[1], actualRowsAffected[1]);
699
700 verify(this.preparedStatement, never()).addBatch();
701 verify(this.preparedStatement).setInt(1, ids[0]);
702 verify(this.preparedStatement).setInt(1, ids[1]);
703 verify(this.preparedStatement).close();
704 verify(this.connection).close();
705 }
706
707 @Test
708 public void testBatchUpdateFails() throws Exception {
709 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
710 final int[] ids = new int[] { 100, 200 };
711 SQLException sqlException = new SQLException();
712
713 given(this.preparedStatement.executeBatch()).willThrow(sqlException);
714 mockDatabaseMetaData(true);
715
716 BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
717 @Override
718 public void setValues(PreparedStatement ps, int i) throws SQLException {
719 ps.setInt(1, ids[i]);
720 }
721 @Override
722 public int getBatchSize() {
723 return ids.length;
724 }
725 };
726
727 this.thrown.expect(DataAccessException.class);
728 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
729 try {
730 this.template.batchUpdate(sql, setter);
731 }
732 finally {
733 verify(this.preparedStatement, times(2)).addBatch();
734 verify(this.preparedStatement).setInt(1, ids[0]);
735 verify(this.preparedStatement).setInt(1, ids[1]);
736 verify(this.preparedStatement).close();
737 verify(this.connection, atLeastOnce()).close();
738 }
739 }
740
741 @Test
742 public void testBatchUpdateWithListOfObjectArrays() throws Exception {
743
744 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
745 final List<Object[]> ids = new ArrayList<Object[]>();
746 ids.add(new Object[] {100});
747 ids.add(new Object[] {200});
748 final int[] rowsAffected = new int[] { 1, 2 };
749
750 given(this.preparedStatement.executeBatch()).willReturn(rowsAffected);
751 mockDatabaseMetaData(true);
752
753 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
754
755 int[] actualRowsAffected = template.batchUpdate(sql, ids);
756
757 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
758 assertEquals(rowsAffected[0], actualRowsAffected[0]);
759 assertEquals(rowsAffected[1], actualRowsAffected[1]);
760
761 verify(this.preparedStatement, times(2)).addBatch();
762 verify(this.preparedStatement).setObject(1, 100);
763 verify(this.preparedStatement).setObject(1, 200);
764 verify(this.preparedStatement).close();
765 verify(this.connection, atLeastOnce()).close();
766 }
767
768 @Test
769 public void testBatchUpdateWithListOfObjectArraysPlusTypeInfo() throws Exception {
770 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
771 final List<Object[]> ids = new ArrayList<Object[]>();
772 ids.add(new Object[] {100});
773 ids.add(new Object[] {200});
774 final int[] sqlTypes = new int[] {Types.NUMERIC};
775 final int[] rowsAffected = new int[] { 1, 2 };
776
777 given(this.preparedStatement.executeBatch()).willReturn(rowsAffected);
778 mockDatabaseMetaData(true);
779
780 this.template = new JdbcTemplate(this.dataSource, false);
781 int[] actualRowsAffected = this.template.batchUpdate(sql, ids, sqlTypes);
782
783 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
784 assertEquals(rowsAffected[0], actualRowsAffected[0]);
785 assertEquals(rowsAffected[1], actualRowsAffected[1]);
786 verify(this.preparedStatement, times(2)).addBatch();
787 verify(this.preparedStatement).setObject(1, 100, sqlTypes[0]);
788 verify(this.preparedStatement).setObject(1, 200, sqlTypes[0]);
789 verify(this.preparedStatement).close();
790 verify(this.connection, atLeastOnce()).close();
791 }
792
793 @Test
794 public void testBatchUpdateWithCollectionOfObjects() throws Exception {
795 final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
796 final List<Integer> ids = Arrays.asList(100, 200, 300);
797 final int[] rowsAffected1 = new int[] { 1, 2 };
798 final int[] rowsAffected2 = new int[] { 3 };
799
800 given(this.preparedStatement.executeBatch()).willReturn(rowsAffected1, rowsAffected2);
801 mockDatabaseMetaData(true);
802
803 ParameterizedPreparedStatementSetter<Integer> setter = new ParameterizedPreparedStatementSetter<Integer>() {
804 @Override
805 public void setValues(PreparedStatement ps, Integer argument) throws SQLException {
806 ps.setInt(1, argument.intValue());
807 }
808 };
809
810 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
811
812 int[][] actualRowsAffected = template.batchUpdate(sql, ids, 2, setter);
813 assertTrue("executed 2 updates", actualRowsAffected[0].length == 2);
814 assertEquals(rowsAffected1[0], actualRowsAffected[0][0]);
815 assertEquals(rowsAffected1[1], actualRowsAffected[0][1]);
816 assertEquals(rowsAffected2[0], actualRowsAffected[1][0]);
817
818 verify(this.preparedStatement, times(3)).addBatch();
819 verify(this.preparedStatement).setInt(1, ids.get(0));
820 verify(this.preparedStatement).setInt(1, ids.get(1));
821 verify(this.preparedStatement).setInt(1, ids.get(2));
822 verify(this.preparedStatement).close();
823 verify(this.connection, atLeastOnce()).close();
824 }
825
826 @Test
827 public void testCouldntGetConnectionForOperationOrExceptionTranslator() throws SQLException {
828 SQLException sqlException = new SQLException("foo", "07xxx");
829 this.dataSource = mock(DataSource.class);
830 given(this.dataSource.getConnection()).willThrow(sqlException);
831 JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
832 RowCountCallbackHandler rcch = new RowCountCallbackHandler();
833 this.thrown.expect(CannotGetJdbcConnectionException.class);
834 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
835 template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
836 }
837
838 @Test
839 public void testCouldntGetConnectionForOperationWithLazyExceptionTranslator() throws SQLException {
840 SQLException sqlException = new SQLException("foo", "07xxx");
841 this.dataSource = mock(DataSource.class);
842 given(this.dataSource.getConnection()).willThrow(sqlException);
843 this.template = new JdbcTemplate();
844 this.template.setDataSource(this.dataSource);
845 this.template.afterPropertiesSet();
846 RowCountCallbackHandler rcch = new RowCountCallbackHandler();
847 this.thrown.expect(CannotGetJdbcConnectionException.class);
848 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
849 this.template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
850 }
851
852 @Test
853 public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitializedViaBeanProperty()
854 throws Exception {
855 doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(true);
856 }
857
858 @Test
859 public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitializedInAfterPropertiesSet()
860 throws Exception {
861 doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(false);
862 }
863
864
865
866
867
868 private void doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(boolean beanProperty)
869 throws SQLException {
870 SQLException sqlException = new SQLException("foo", "07xxx");
871 this.dataSource = mock(DataSource.class);
872 given(this.dataSource.getConnection()).willThrow(sqlException);
873 this.template = new JdbcTemplate();
874 this.template.setDataSource(this.dataSource);
875 this.template.setLazyInit(false);
876 if (beanProperty) {
877
878 this.template.setExceptionTranslator(new SQLErrorCodeSQLExceptionTranslator(this.dataSource));
879 }
880 else {
881
882 this.template.afterPropertiesSet();
883 }
884 RowCountCallbackHandler rcch = new RowCountCallbackHandler();
885 this.thrown.expect(CannotGetJdbcConnectionException.class);
886 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
887 this.template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
888 }
889
890 @Test
891 public void testPreparedStatementSetterSucceeds() throws Exception {
892 final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1";
893 final String name = "Gary";
894 int expectedRowsUpdated = 1;
895
896 given(this.preparedStatement.executeUpdate()).willReturn(expectedRowsUpdated);
897
898 PreparedStatementSetter pss = new PreparedStatementSetter() {
899 @Override
900 public void setValues(PreparedStatement ps) throws SQLException {
901 ps.setString(1, name);
902 }
903 };
904 int actualRowsUpdated = new JdbcTemplate(this.dataSource).update(sql, pss);
905 assertTrue("updated correct # of rows", actualRowsUpdated == expectedRowsUpdated);
906 verify(this.preparedStatement).setString(1, name);
907 verify(this.preparedStatement).close();
908 verify(this.connection).close();
909 }
910
911 @Test
912 public void testPreparedStatementSetterFails() throws Exception {
913 final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1";
914 final String name = "Gary";
915 SQLException sqlException = new SQLException();
916 given(this.preparedStatement.executeUpdate()).willThrow(sqlException);
917
918 PreparedStatementSetter pss = new PreparedStatementSetter() {
919 @Override
920 public void setValues(PreparedStatement ps) throws SQLException {
921 ps.setString(1, name);
922 }
923 };
924 this.thrown.expect(DataAccessException.class);
925 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
926 try {
927 new JdbcTemplate(this.dataSource).update(sql, pss);
928 }
929 finally {
930 verify(this.preparedStatement).setString(1, name);
931 verify(this.preparedStatement).close();
932 verify(this.connection, atLeastOnce()).close();
933 }
934 }
935
936 @Test
937 public void testCouldntClose() throws Exception {
938 SQLException sqlException = new SQLException("bar");
939 given(this.connection.createStatement()).willReturn(this.statement);
940 given(this.resultSet.next()).willReturn(false);
941 willThrow(sqlException).given(this.resultSet).close();
942 willThrow(sqlException).given(this.statement).close();
943 willThrow(sqlException).given(this.connection).close();
944
945 RowCountCallbackHandler rcch = new RowCountCallbackHandler();
946 this.template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
947 verify(this.connection).close();
948 }
949
950
951
952
953 @Test
954 public void testFatalWarning() throws Exception {
955 String sql = "SELECT forename from custmr";
956 SQLWarning warnings = new SQLWarning("My warning");
957
958 given(this.resultSet.next()).willReturn(false);
959 given(this.preparedStatement.getWarnings()).willReturn(warnings);
960 given(this.connection.createStatement()).willReturn(this.preparedStatement);
961
962 JdbcTemplate t = new JdbcTemplate(this.dataSource);
963 t.setIgnoreWarnings(false);
964 this.thrown.expect(SQLWarningException.class);
965 this.thrown.expect(exceptionCause(sameInstance(warnings)));
966 try {
967 t.query(sql, new RowCallbackHandler() {
968 @Override
969 public void processRow(ResultSet rs) throws SQLException {
970 rs.getByte(1);
971 }
972 });
973 }
974 finally {
975 verify(this.resultSet).close();
976 verify(this.preparedStatement).close();
977 verify(this.connection).close();
978 }
979 }
980
981 @Test
982 public void testIgnoredWarning() throws Exception {
983 String sql = "SELECT forename from custmr";
984 SQLWarning warnings = new SQLWarning("My warning");
985
986 given(this.resultSet.next()).willReturn(false);
987 given(this.connection.createStatement()).willReturn(this.preparedStatement);
988 given(this.preparedStatement.getWarnings()).willReturn(warnings);
989
990
991
992 this.template.setIgnoreWarnings(true);
993 this.template.query(sql, new RowCallbackHandler() {
994 @Override
995 public void processRow(ResultSet rs) throws java.sql.SQLException {
996 rs.getByte(1);
997 }
998 });
999
1000 verify(this.resultSet).close();
1001 verify(this.preparedStatement).close();
1002 verify(this.connection).close();
1003 }
1004
1005 @Test
1006 public void testSQLErrorCodeTranslation() throws Exception {
1007 final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
1008 final String sql = "SELECT ID FROM CUSTOMER";
1009
1010 given(this.resultSet.next()).willReturn(true);
1011 mockDatabaseMetaData(false);
1012 given(this.connection.createStatement()).willReturn(this.preparedStatement);
1013
1014 this.thrown.expect(BadSqlGrammarException.class);
1015 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
1016 try {
1017 this.template.query(sql, new RowCallbackHandler() {
1018 @Override
1019 public void processRow(ResultSet rs) throws SQLException {
1020 throw sqlException;
1021 }
1022 });
1023 fail("Should have thrown BadSqlGrammarException");
1024 }
1025 finally {
1026 verify(this.resultSet).close();
1027 verify(this.preparedStatement).close();
1028 verify(this.connection, atLeastOnce()).close();
1029 }
1030 }
1031
1032 @Test
1033 public void testSQLErrorCodeTranslationWithSpecifiedDbName() throws Exception {
1034 final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
1035 final String sql = "SELECT ID FROM CUSTOMER";
1036
1037 given(this.resultSet.next()).willReturn(true);
1038 given(this.connection.createStatement()).willReturn(this.preparedStatement);
1039
1040 JdbcTemplate template = new JdbcTemplate();
1041 template.setDataSource(this.dataSource);
1042 template.setDatabaseProductName("MySQL");
1043 template.afterPropertiesSet();
1044
1045 this.thrown.expect(BadSqlGrammarException.class);
1046 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
1047 try {
1048 template.query(sql, new RowCallbackHandler() {
1049 @Override
1050 public void processRow(ResultSet rs) throws SQLException {
1051 throw sqlException;
1052 }
1053 });
1054 }
1055 finally {
1056 verify(this.resultSet).close();
1057 verify(this.preparedStatement).close();
1058 verify(this.connection).close();
1059 }
1060 }
1061
1062
1063
1064
1065
1066
1067 @Test
1068 public void testUseCustomSQLErrorCodeTranslator() throws Exception {
1069
1070 final SQLException sqlException = new SQLException("I have a known problem", "07000", 1054);
1071 final String sql = "SELECT ID FROM CUSTOMER";
1072
1073 given(this.resultSet.next()).willReturn(true);
1074 given(this.connection.createStatement()).willReturn(this.preparedStatement);
1075
1076 JdbcTemplate template = new JdbcTemplate();
1077 template.setDataSource(this.dataSource);
1078
1079 template.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
1080 template.afterPropertiesSet();
1081
1082 this.thrown.expect(BadSqlGrammarException.class);
1083 this.thrown.expect(exceptionCause(sameInstance(sqlException)));
1084 try {
1085 template.query(sql, new RowCallbackHandler() {
1086 @Override
1087 public void processRow(ResultSet rs) throws SQLException {
1088 throw sqlException;
1089 }
1090 });
1091 }
1092 finally {
1093 verify(this.resultSet).close();
1094 verify(this.preparedStatement).close();
1095 verify(this.connection).close();
1096 }
1097 }
1098
1099 @Test
1100 public void testNativeJdbcExtractorInvoked() throws Exception {
1101
1102 final Statement statement2 = mock(Statement.class);
1103 given(statement2.executeQuery(anyString())).willReturn(this.resultSet);
1104
1105 final PreparedStatement preparedStatement2 = mock(PreparedStatement.class);
1106 given(preparedStatement2.executeQuery()).willReturn(this.resultSet);
1107
1108 final ResultSet returnResultSet = mock(ResultSet.class);
1109 given(returnResultSet.next()).willReturn(false);
1110
1111 final CallableStatement callableStatement = mock(CallableStatement.class);
1112 final CallableStatement callableStatement2 = mock(CallableStatement.class);
1113 given(callableStatement2.execute()).willReturn(true);
1114 given(callableStatement2.getUpdateCount()).willReturn(-1);
1115 given(callableStatement2.getResultSet()).willReturn(returnResultSet);
1116 given(callableStatement2.getUpdateCount()).willReturn(-1);
1117
1118 given(this.connection.createStatement()).willReturn(this.statement);
1119
1120 this.template.setNativeJdbcExtractor(new NativeJdbcExtractor() {
1121 @Override
1122 public boolean isNativeConnectionNecessaryForNativeStatements() {
1123 return false;
1124 }
1125 @Override
1126 public boolean isNativeConnectionNecessaryForNativePreparedStatements() {
1127 return false;
1128 }
1129 @Override
1130 public boolean isNativeConnectionNecessaryForNativeCallableStatements() {
1131 return false;
1132 }
1133 @Override
1134 public Connection getNativeConnection(Connection con) {
1135 return con;
1136 }
1137 @Override
1138 public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException {
1139 return stmt.getConnection();
1140 }
1141 @Override
1142 public Statement getNativeStatement(Statement stmt) {
1143 assertTrue(stmt == JdbcTemplateTests.this.statement);
1144 return statement2;
1145 }
1146 @Override
1147 public PreparedStatement getNativePreparedStatement(PreparedStatement ps) {
1148 assertTrue(ps == JdbcTemplateTests.this.preparedStatement);
1149 return preparedStatement2;
1150 }
1151 @Override
1152 public CallableStatement getNativeCallableStatement(CallableStatement cs) {
1153 assertTrue(cs == callableStatement);
1154 return callableStatement2;
1155 }
1156 @Override
1157 public ResultSet getNativeResultSet(ResultSet rs) {
1158 return rs;
1159 }
1160 });
1161
1162 this.template.query("my query", new ResultSetExtractor<Object>() {
1163 @Override
1164 public Object extractData(ResultSet rs2) {
1165 assertEquals(JdbcTemplateTests.this.resultSet, rs2);
1166 return null;
1167 }
1168 });
1169
1170 this.template.query(new PreparedStatementCreator() {
1171 @Override
1172 public PreparedStatement createPreparedStatement(Connection conn) {
1173 return JdbcTemplateTests.this.preparedStatement;
1174 }
1175 }, new ResultSetExtractor<Object>() {
1176 @Override
1177 public Object extractData(ResultSet rs2) {
1178 assertEquals(JdbcTemplateTests.this.resultSet, rs2);
1179 return null;
1180 }
1181 });
1182
1183 this.template.call(new CallableStatementCreator() {
1184 @Override
1185 public CallableStatement createCallableStatement(Connection con) {
1186 return callableStatement;
1187 }
1188 }, new ArrayList<SqlParameter>());
1189
1190 verify(this.resultSet, times(2)).close();
1191 verify(this.statement).close();
1192 verify(this.preparedStatement).close();
1193 verify(returnResultSet).close();
1194 verify(callableStatement).close();
1195 verify(this.connection, atLeastOnce()).close();
1196 }
1197
1198 @Test
1199 public void testStaticResultSetClosed() throws Exception {
1200 ResultSet resultSet2 = mock(ResultSet.class);
1201 reset(this.preparedStatement);
1202 given(this.preparedStatement.executeQuery()).willReturn(resultSet2);
1203 given(this.connection.createStatement()).willReturn(this.statement);
1204
1205 try {
1206 this.template.query("my query", new ResultSetExtractor<Object>() {
1207 @Override
1208 public Object extractData(ResultSet rs) {
1209 throw new InvalidDataAccessApiUsageException("");
1210 }
1211 });
1212 fail("Should have thrown InvalidDataAccessApiUsageException");
1213 }
1214 catch (InvalidDataAccessApiUsageException idaauex) {
1215
1216 }
1217
1218 try {
1219 this.template.query(new PreparedStatementCreator() {
1220 @Override
1221 public PreparedStatement createPreparedStatement(Connection con)
1222 throws SQLException {
1223 return con.prepareStatement("my query");
1224 }
1225 }, new ResultSetExtractor<Object>() {
1226 @Override
1227 public Object extractData(ResultSet rs2) {
1228 throw new InvalidDataAccessApiUsageException("");
1229 }
1230 });
1231 fail("Should have thrown InvalidDataAccessApiUsageException");
1232 }
1233 catch (InvalidDataAccessApiUsageException idaauex) {
1234
1235 }
1236
1237 verify(this.resultSet).close();
1238 verify(resultSet2).close();
1239 verify(this.preparedStatement).close();
1240 verify(this.connection, atLeastOnce()).close();
1241 }
1242
1243 @Test
1244 public void testExecuteClosed() throws Exception {
1245 given(this.resultSet.next()).willReturn(true);
1246 given(this.callableStatement.execute()).willReturn(true);
1247 given(this.callableStatement.getUpdateCount()).willReturn(-1);
1248
1249 List<SqlParameter> params = new ArrayList<SqlParameter>();
1250 params.add(new SqlReturnResultSet("", new RowCallbackHandler() {
1251 @Override
1252 public void processRow(ResultSet rs) {
1253 throw new InvalidDataAccessApiUsageException("");
1254 }
1255
1256 }));
1257
1258 this.thrown.expect(InvalidDataAccessApiUsageException.class);
1259 try {
1260 this.template.call(new CallableStatementCreator() {
1261 @Override
1262 public CallableStatement createCallableStatement(Connection conn)
1263 throws SQLException {
1264 return conn.prepareCall("my query");
1265 }
1266 }, params);
1267 }
1268 finally {
1269 verify(this.resultSet).close();
1270 verify(this.callableStatement).close();
1271 verify(this.connection).close();
1272 }
1273 }
1274
1275 @Test
1276 public void testCaseInsensitiveResultsMap() throws Exception {
1277
1278 given(this.callableStatement.execute()).willReturn(false);
1279 given(this.callableStatement.getUpdateCount()).willReturn(-1);
1280 given(this.callableStatement.getObject(1)).willReturn("X");
1281
1282 assertTrue("default should have been NOT case insensitive",
1283 !this.template.isResultsMapCaseInsensitive());
1284
1285 this.template.setResultsMapCaseInsensitive(true);
1286 assertTrue("now it should have been set to case insensitive",
1287 this.template.isResultsMapCaseInsensitive());
1288
1289 List<SqlParameter> params = new ArrayList<SqlParameter>();
1290 params.add(new SqlOutParameter("a", 12));
1291
1292 Map<String, Object> out = this.template.call(new CallableStatementCreator() {
1293 @Override
1294 public CallableStatement createCallableStatement(Connection conn)
1295 throws SQLException {
1296 return conn.prepareCall("my query");
1297 }
1298 }, params);
1299
1300 assertThat(out, instanceOf(LinkedCaseInsensitiveMap.class));
1301 assertNotNull("we should have gotten the result with upper case", out.get("A"));
1302 assertNotNull("we should have gotten the result with lower case", out.get("a"));
1303 verify(this.callableStatement).close();
1304 verify(this.connection).close();
1305 }
1306
1307 private void mockDatabaseMetaData(boolean supportsBatchUpdates) throws SQLException {
1308 DatabaseMetaData databaseMetaData = mock(DatabaseMetaData.class);
1309 given(databaseMetaData.getDatabaseProductName()).willReturn("MySQL");
1310 given(databaseMetaData.supportsBatchUpdates()).willReturn(supportsBatchUpdates);
1311 given(this.connection.getMetaData()).willReturn(databaseMetaData);
1312 }
1313
1314 private static class PlainNativeJdbcExtractor extends NativeJdbcExtractorAdapter {
1315
1316 @Override
1317 protected Connection doGetNativeConnection(Connection connection) throws SQLException {
1318 return connection;
1319 }
1320 }
1321
1322
1323 private static interface JdbcTemplateCallback {
1324
1325 void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch);
1326 }
1327
1328
1329 private static class Dispatcher implements PreparedStatementCreator, SqlProvider {
1330
1331 private int id;
1332 private String sql;
1333
1334 public Dispatcher(int id, String sql) {
1335 this.id = id;
1336 this.sql = sql;
1337 }
1338
1339 @Override
1340 public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
1341 PreparedStatement ps = connection.prepareStatement(this.sql);
1342 ps.setInt(1, this.id);
1343 return ps;
1344 }
1345
1346 @Override
1347 public String getSql() {
1348 return this.sql;
1349 }
1350 }
1351 }