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


1 /*
2  * Copyright 2002-2005 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.sql.CallableStatement ;
20 import java.sql.Connection ;
21 import java.sql.DatabaseMetaData ;
22 import java.sql.PreparedStatement ;
23 import java.sql.ResultSet ;
24 import java.sql.SQLException ;
25 import java.sql.SQLWarning ;
26 import java.sql.Statement ;
27 import java.util.ArrayList ;
28 import java.util.LinkedList ;
29 import java.util.List ;
30
31 import javax.sql.DataSource ;
32
33 import org.easymock.MockControl;
34
35 import org.springframework.dao.DataAccessException;
36 import org.springframework.dao.InvalidDataAccessApiUsageException;
37 import org.springframework.dao.UncategorizedDataAccessException;
38 import org.springframework.jdbc.AbstractJdbcTests;
39 import org.springframework.jdbc.BadSqlGrammarException;
40 import org.springframework.jdbc.CannotGetJdbcConnectionException;
41 import org.springframework.jdbc.SQLWarningException;
42 import org.springframework.jdbc.UncategorizedSQLException;
43 import org.springframework.jdbc.datasource.SingleConnectionDataSource;
44 import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
45 import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;
46 import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
47
48 /**
49  * Mock object based tests for JdbcTemplate.
50  *
51  * @author Rod Johnson
52  * @author Thomas Risberg
53  */
54 public class JdbcTemplateTests extends AbstractJdbcTests {
55
56     public void testBeanProperties() throws Exception {
57         replay();
58
59         JdbcTemplate template = new JdbcTemplate(mockDataSource);
60         assertTrue("datasource ok", template.getDataSource() == mockDataSource);
61         assertTrue("ignores warnings by default", template.isIgnoreWarnings());
62         template.setIgnoreWarnings(false);
63         assertTrue("can set NOT to ignore warnings", !template.isIgnoreWarnings());
64     }
65
66     public void testCannotRunStaticSqlWithBindParameters() throws Exception {
67         final String sql = "UPDATE FOO SET NAME='tony' WHERE ID > ?";
68
69         replay();
70
71         JdbcTemplate t = new JdbcTemplate(mockDataSource);
72         try {
73             t.query(sql, new RowCountCallbackHandler());
74             fail("Should have objected to bind variables");
75         }
76         catch (InvalidDataAccessApiUsageException ex) {
77             // OK
78 }
79     }
80
81     public void testUpdateCount() throws Exception {
82         final String sql =
83             "UPDATE INVOICE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
84         int idParam = 11111;
85
86         MockControl ctrlPreparedStatement =
87             MockControl.createControl(PreparedStatement .class);
88         PreparedStatement mockPreparedStatement =
89             (PreparedStatement ) ctrlPreparedStatement.getMock();
90         mockPreparedStatement.setInt(1, idParam);
91         ctrlPreparedStatement.setVoidCallable();
92         mockPreparedStatement.executeUpdate();
93         ctrlPreparedStatement.setReturnValue(1);
94         mockPreparedStatement.getWarnings();
95         ctrlPreparedStatement.setReturnValue(null);
96         mockPreparedStatement.close();
97         ctrlPreparedStatement.setVoidCallable();
98
99         mockConnection.prepareStatement(sql);
100         ctrlConnection.setReturnValue(mockPreparedStatement);
101
102         ctrlPreparedStatement.replay();
103         replay();
104
105         Dispatcher d = new Dispatcher(idParam, sql);
106         JdbcTemplate template = new JdbcTemplate(mockDataSource);
107
108         int rowsAffected = template.update(d);
109         assertTrue("1 update affected 1 row", rowsAffected == 1);
110
111         /*
112         d = new Dispatcher(idParam);
113         rowsAffected = template.update(d);
114         assertTrue("bogus update affected 0 rows", rowsAffected == 0);
115         */
116
117         ctrlPreparedStatement.verify();
118     }
119
120     public void testBogusUpdate() throws Exception {
121         final String sql =
122             "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
123         final int idParam = 6666;
124
125         // It's because Integers aren't canonical
126 SQLException sex = new SQLException ("bad update");
127
128         MockControl ctrlPreparedStatement =
129             MockControl.createControl(PreparedStatement .class);
130         PreparedStatement mockPreparedStatement =
131             (PreparedStatement ) ctrlPreparedStatement.getMock();
132         mockPreparedStatement.setInt(1, idParam);
133         ctrlPreparedStatement.setVoidCallable();
134         mockPreparedStatement.executeUpdate();
135         ctrlPreparedStatement.setThrowable(sex);
136         mockPreparedStatement.close();
137         ctrlPreparedStatement.setVoidCallable();
138
139         mockConnection.prepareStatement(sql);
140         ctrlConnection.setReturnValue(mockPreparedStatement);
141
142         ctrlPreparedStatement.replay();
143         replay();
144
145         Dispatcher d = new Dispatcher(idParam, sql);
146         JdbcTemplate template = new JdbcTemplate(mockDataSource);
147
148         try {
149             template.update(d);
150             fail("Bogus update should throw exception");
151         }
152         catch (UncategorizedDataAccessException ex) {
153             // pass
154 assertTrue(
155                 "Correct exception",
156                 ex instanceof UncategorizedSQLException);
157             assertTrue("Root cause is correct", ex.getCause() == sex);
158             //assertTrue("no update occurred", !je.getDataWasUpdated());
159 }
160
161         ctrlPreparedStatement.verify();
162     }
163
164     public void testStringsWithStaticSql() throws Exception {
165         doTestStrings(new JdbcTemplateCallback() {
166             public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
167                 template.query(sql, rch);
168             }
169         }, false, null, null, null);
170     }
171
172     public void testStringsWithStaticSqlAndFetchSizeAndMaxRows() throws Exception {
173         doTestStrings(new JdbcTemplateCallback() {
174             public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
175                 template.query(sql, rch);
176             }
177         }, false, new Integer (10), new Integer (20), null);
178     }
179
180     public void testStringsWithEmptyPreparedStatementSetter() throws Exception {
181         doTestStrings(new JdbcTemplateCallback() {
182             public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
183                 template.query(sql, (PreparedStatementSetter) null, rch);
184             }
185         }, true, null, null, null);
186     }
187
188     public void testStringsWithPreparedStatementSetter() throws Exception {
189         final Integer argument = new Integer (99);
190         doTestStrings(new JdbcTemplateCallback() {
191             public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
192                 template.query(sql, new PreparedStatementSetter() {
193                     public void setValues(PreparedStatement ps) throws SQLException {
194                         ps.setObject(1, argument);
195                     }
196                 }, rch);
197             }
198         }, true, null, null, argument);
199     }
200
201     public void testStringsWithEmptyPreparedStatementArgs() throws Exception {
202         doTestStrings(new JdbcTemplateCallback() {
203             public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
204                 template.query(sql, (Object []) null, rch);
205             }
206         }, true, null, null, null);
207     }
208
209     public void testStringsWithPreparedStatementArgs() throws Exception {
210         final Integer argument = new Integer (99);
211         doTestStrings(new JdbcTemplateCallback() {
212             public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
213                 template.query(sql, new Object [] {argument}, rch);
214             }
215         }, true, null, null, argument);
216     }
217
218     private void doTestStrings(
219             JdbcTemplateCallback jdbcTemplateCallback, boolean usePreparedStatement,
220             Integer fetchSize, Integer maxRows, Object argument)
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 list = new LinkedList ();
228             public void processRow(ResultSet rs) throws SQLException {
229                 list.add(rs.getString(1));
230             }
231             public String [] getStrings() {
232                 return (String []) list.toArray(new String [list.size()]);
233             }
234         }
235
236         MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
237         ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
238         mockResultSet.next();
239         ctrlResultSet.setReturnValue(true);
240         mockResultSet.getString(1);
241         ctrlResultSet.setReturnValue(results[0]);
242         mockResultSet.next();
243         ctrlResultSet.setReturnValue(true);
244         mockResultSet.getString(1);
245         ctrlResultSet.setReturnValue(results[1]);
246         mockResultSet.next();
247         ctrlResultSet.setReturnValue(true);
248         mockResultSet.getString(1);
249         ctrlResultSet.setReturnValue(results[2]);
250         mockResultSet.next();
251         ctrlResultSet.setReturnValue(false);
252         mockResultSet.close();
253         ctrlResultSet.setVoidCallable();
254
255         MockControl ctrlStatement =
256             MockControl.createControl(PreparedStatement .class);
257         PreparedStatement mockStatement =
258             (PreparedStatement ) ctrlStatement.getMock();
259         if (fetchSize != null) {
260             mockStatement.setFetchSize(fetchSize.intValue());
261         }
262         if (maxRows != null) {
263             mockStatement.setMaxRows(maxRows.intValue());
264         }
265         if (argument != null) {
266             mockStatement.setObject(1, argument);
267         }
268         if (usePreparedStatement) {
269             mockStatement.executeQuery();
270         }
271         else {
272             mockStatement.executeQuery(sql);
273         }
274         ctrlStatement.setReturnValue(mockResultSet);
275         mockStatement.getWarnings();
276         ctrlStatement.setReturnValue(null);
277         mockStatement.close();
278         ctrlStatement.setVoidCallable();
279
280         if (usePreparedStatement) {
281             mockConnection.prepareStatement(sql);
282         }
283         else {
284             mockConnection.createStatement();
285         }
286         ctrlConnection.setReturnValue(mockStatement);
287
288         ctrlResultSet.replay();
289         ctrlStatement.replay();
290         replay();
291
292         StringHandler sh = new StringHandler();
293         JdbcTemplate template = new JdbcTemplate();
294         template.setDataSource(mockDataSource);
295         if (fetchSize != null) {
296             template.setFetchSize(fetchSize.intValue());
297         }
298         if (maxRows != null) {
299             template.setMaxRows(maxRows.intValue());
300         }
301         jdbcTemplateCallback.doInJdbcTemplate(template, sql, sh);
302
303         // Match
304 String [] forenames = sh.getStrings();
305         assertTrue("same length", forenames.length == results.length);
306         for (int i = 0; i < forenames.length; i++) {
307             assertTrue("Row " + i + " matches", forenames[i].equals(results[i]));
308         }
309
310         ctrlResultSet.verify();
311         ctrlStatement.verify();
312     }
313
314     public void testLeaveConnectionOpenOnRequest() throws Exception {
315         String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";
316
317         MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
318         ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
319         ctrlResultSet = MockControl.createControl(ResultSet .class);
320         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
321         mockResultSet.next();
322         ctrlResultSet.setReturnValue(false);
323         mockResultSet.close();
324         ctrlResultSet.setVoidCallable();
325
326         MockControl ctrlStatement = MockControl.createControl(PreparedStatement .class);
327         PreparedStatement mockStatement = (PreparedStatement ) ctrlStatement.getMock();
328         ctrlStatement = MockControl.createControl(PreparedStatement .class);
329         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
330         mockStatement.executeQuery(sql);
331         ctrlStatement.setReturnValue(mockResultSet);
332         mockStatement.getWarnings();
333         ctrlStatement.setReturnValue(null);
334         mockStatement.close();
335         ctrlStatement.setVoidCallable();
336
337         mockConnection.isClosed();
338         ctrlConnection.setReturnValue(false, 2);
339         mockConnection.createStatement();
340         ctrlConnection.setReturnValue(mockStatement);
341         // if close is called entire test will fail
342 mockConnection.close();
343         ctrlConnection.setDefaultThrowable(new RuntimeException ());
344
345         ctrlResultSet.replay();
346         ctrlStatement.replay();
347         replay();
348
349         SingleConnectionDataSource scf = new SingleConnectionDataSource(mockDataSource.getConnection(), false);
350         JdbcTemplate template2 = new JdbcTemplate(scf, false);
351         RowCountCallbackHandler rcch = new RowCountCallbackHandler();
352         template2.query(sql, rcch);
353
354         ctrlResultSet.verify();
355         ctrlStatement.verify();
356     }
357
358     public void testConnectionCallback() throws Exception {
359         replay();
360
361         JdbcTemplate template = new JdbcTemplate(mockDataSource);
362         Object result = template.execute(new ConnectionCallback() {
363             public Object doInConnection(Connection con) {
364                 assertEquals(con, mockConnection);
365                 return "test";
366             }
367         });
368
369         assertEquals("test", result);
370     }
371
372     public void testCloseConnectionOnRequest() throws Exception {
373         String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";
374
375         MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
376         ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
377         mockResultSet.next();
378         ctrlResultSet.setReturnValue(false);
379         mockResultSet.close();
380         ctrlResultSet.setVoidCallable();
381
382         MockControl ctrlStatement = MockControl.createControl(PreparedStatement .class);
383         PreparedStatement mockStatement = (PreparedStatement ) ctrlStatement.getMock();
384         mockStatement.executeQuery(sql);
385         ctrlStatement.setReturnValue(mockResultSet);
386         mockStatement.getWarnings();
387         ctrlStatement.setReturnValue(null);
388         mockStatement.close();
389         ctrlStatement.setVoidCallable();
390
391         mockConnection.createStatement();
392         ctrlConnection.setReturnValue(mockStatement);
393
394         ctrlResultSet.replay();
395         ctrlStatement.replay();
396         replay();
397
398         JdbcTemplate template = new JdbcTemplate(mockDataSource);
399         RowCountCallbackHandler rcch = new RowCountCallbackHandler();
400         template.query(sql, rcch);
401
402         ctrlResultSet.verify();
403         ctrlStatement.verify();
404     }
405
406     /**
407      * Test that we see a runtime exception come back.
408      */
409     public void testExceptionComesBack() throws Exception {
410         final String sql = "SELECT ID FROM CUSTMR";
411         final RuntimeException rex = new RuntimeException ("What I want to see");
412
413         MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
414         ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
415         mockResultSet.next();
416         ctrlResultSet.setReturnValue(true);
417         mockResultSet.close();
418         ctrlResultSet.setVoidCallable();
419
420         MockControl ctrlStatement = MockControl.createControl(PreparedStatement .class);
421         PreparedStatement mockStatement = (PreparedStatement ) ctrlStatement.getMock();
422         mockStatement.executeQuery(sql);
423         ctrlStatement.setReturnValue(mockResultSet);
424         mockStatement.getWarnings();
425         ctrlStatement.setReturnValue(null);
426         mockStatement.close();
427         ctrlStatement.setVoidCallable();
428
429         mockConnection.createStatement();
430         ctrlConnection.setReturnValue(mockStatement);
431
432         ctrlResultSet.replay();
433         ctrlStatement.replay();
434         replay();
435
436         JdbcTemplate template = new JdbcTemplate(mockDataSource);
437         try {
438             template.query(sql, new RowCallbackHandler() {
439                 public void processRow(ResultSet rs) {
440                     throw rex;
441                 }
442             });
443             fail("Should have thrown exception");
444         }
445         catch (RuntimeException ex) {
446             assertTrue("Wanted same exception back, not " + ex, ex == rex);
447         }
448     }
449
450     /**
451      * Test update with static SQL.
452      */
453     public void testSqlUpdate() throws Exception {
454         final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
455         int rowsAffected = 33;
456
457         MockControl ctrlStatement = MockControl.createControl(Statement .class);
458         Statement mockStatement = (Statement ) ctrlStatement.getMock();
459         mockStatement.executeUpdate(sql);
460         ctrlStatement.setReturnValue(rowsAffected);
461         mockStatement.getWarnings();
462         ctrlStatement.setReturnValue(null);
463         mockStatement.close();
464         ctrlStatement.setVoidCallable();
465
466         mockConnection.createStatement();
467         ctrlConnection.setReturnValue(mockStatement);
468
469         ctrlStatement.replay();
470         replay();
471
472         JdbcTemplate template = new JdbcTemplate(mockDataSource);
473
474         int actualRowsAffected = template.update(sql);
475         assertTrue(
476             "Actual rows affected is correct",
477             actualRowsAffected == rowsAffected);
478
479         ctrlStatement.verify();
480     }
481
482     public void testSqlUpdateEncountersSqlException() throws Exception {
483         SQLException sex = new SQLException ("bad update");
484         final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
485
486         MockControl ctrlStatement = MockControl.createControl(Statement .class);
487         Statement mockStatement = (Statement ) ctrlStatement.getMock();
488         mockStatement.executeUpdate(sql);
489         ctrlStatement.setThrowable(sex);
490         mockStatement.close();
491         ctrlStatement.setVoidCallable();
492
493         mockConnection.createStatement();
494         ctrlConnection.setReturnValue(mockStatement);
495
496         ctrlStatement.replay();
497         replay();
498
499         JdbcTemplate template = new JdbcTemplate(mockDataSource);
500         try {
501             template.update(sql);
502         }
503         catch (DataAccessException ex) {
504             assertTrue("root cause is correct", ex.getCause() == sex);
505         }
506
507         ctrlStatement.verify();
508     }
509
510     public void testSqlUpdateWithThreadConnection() throws Exception {
511         final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
512         int rowsAffected = 33;
513
514         MockControl ctrlStatement = MockControl.createControl(Statement .class);
515         Statement mockStatement = (Statement ) ctrlStatement.getMock();
516         mockStatement.executeUpdate(sql);
517         ctrlStatement.setReturnValue(rowsAffected);
518         mockStatement.getWarnings();
519         ctrlStatement.setReturnValue(null);
520         mockStatement.close();
521         ctrlStatement.setVoidCallable();
522
523         mockConnection.createStatement();
524         ctrlConnection.setReturnValue(mockStatement);
525
526         ctrlStatement.replay();
527         replay();
528
529         JdbcTemplate template = new JdbcTemplate(mockDataSource);
530
531         int actualRowsAffected = template.update(sql);
532         assertTrue(
533             "Actual rows affected is correct",
534             actualRowsAffected == rowsAffected);
535
536         ctrlStatement.verify();
537     }
538
539     public void testBatchUpdate() throws Exception {
540         final String [] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
541                 "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2"};
542
543         MockControl ctrlStatement = MockControl.createControl(Statement .class);
544         Statement mockStatement = (Statement ) ctrlStatement.getMock();
545         mockStatement.getConnection();
546         ctrlStatement.setReturnValue(mockConnection);
547         mockStatement.addBatch(sql[0]);
548         ctrlStatement.setVoidCallable();
549         mockStatement.addBatch(sql[1]);
550         ctrlStatement.setVoidCallable();
551         mockStatement.executeBatch();
552         ctrlStatement.setReturnValue(new int[] {1, 1});
553         mockStatement.getWarnings();
554         ctrlStatement.setReturnValue(null);
555         mockStatement.close();
556         ctrlStatement.setVoidCallable();
557
558         MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData .class);
559         DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData ) ctrlDatabaseMetaData.getMock();
560         mockDatabaseMetaData.getDatabaseProductName();
561         ctrlDatabaseMetaData.setReturnValue("MySQL");
562         mockDatabaseMetaData.supportsBatchUpdates();
563         ctrlDatabaseMetaData.setReturnValue(true);
564
565         mockConnection.getMetaData();
566         ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
567         mockConnection.createStatement();
568         ctrlConnection.setReturnValue(mockStatement);
569
570         ctrlStatement.replay();
571         ctrlDatabaseMetaData.replay();
572         replay();
573
574         JdbcTemplate template = new JdbcTemplate(mockDataSource, false);
575
576         int[] actualRowsAffected = template.batchUpdate(sql);
577         assertTrue("executed 2 updates", actualRowsAffected.length == 2);
578
579         ctrlStatement.verify();
580         ctrlDatabaseMetaData.verify();
581     }
582
583     public void testBatchUpdateWithNoBatchSupport() throws Exception {
584         final String [] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
585                 "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2"};
586
587         MockControl ctrlStatement = MockControl.createControl(Statement .class);
588         Statement mockStatement = (Statement ) ctrlStatement.getMock();
589         mockStatement.getConnection();
590         ctrlStatement.setReturnValue(mockConnection);
591         mockStatement.execute(sql[0]);
592         ctrlStatement.setReturnValue(false);
593         mockStatement.getUpdateCount();
594         ctrlStatement.setReturnValue(1);
595         mockStatement.execute(sql[1]);
596         ctrlStatement.setReturnValue(false);
597         mockStatement.getUpdateCount();
598         ctrlStatement.setReturnValue(1);
599         mockStatement.getWarnings();
600         ctrlStatement.setReturnValue(null);
601         mockStatement.close();
602         ctrlStatement.setVoidCallable();
603
604         MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData .class);
605         DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData ) ctrlDatabaseMetaData.getMock();
606         mockDatabaseMetaData.getDatabaseProductName();
607         ctrlDatabaseMetaData.setReturnValue("MySQL");
608         mockDatabaseMetaData.supportsBatchUpdates();
609         ctrlDatabaseMetaData.setReturnValue(false);
610
611         mockConnection.getMetaData();
612         ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
613         mockConnection.createStatement();
614         ctrlConnection.setReturnValue(mockStatement);
615
616         ctrlStatement.replay();
617         ctrlDatabaseMetaData.replay();
618         replay();
619
620         JdbcTemplate template = new JdbcTemplate(mockDataSource, false);
621
622         int[] actualRowsAffected = template.batchUpdate(sql);
623         assertTrue("executed 2 updates", actualRowsAffected.length == 2);
624
625         ctrlStatement.verify();
626         ctrlDatabaseMetaData.verify();
627     }
628
629     public void testBatchUpdateWithNoBatchSupportAndSelect() throws Exception {
630         final String [] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
631                 "SELECT * FROM NOSUCHTABLE"};
632
633         MockControl ctrlStatement = MockControl.createControl(Statement .class);
634         Statement mockStatement = (Statement ) ctrlStatement.getMock();
635         mockStatement.getConnection();
636         ctrlStatement.setReturnValue(mockConnection);
637         mockStatement.execute(sql[0]);
638         ctrlStatement.setReturnValue(false);
639         mockStatement.getUpdateCount();
640         ctrlStatement.setReturnValue(1);
641         mockStatement.execute(sql[1]);
642         ctrlStatement.setReturnValue(true);
643         mockStatement.close();
644         ctrlStatement.setVoidCallable();
645
646         MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData .class);
647         DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData ) ctrlDatabaseMetaData.getMock();
648         mockDatabaseMetaData.getDatabaseProductName();
649         ctrlDatabaseMetaData.setReturnValue("MySQL");
650         mockDatabaseMetaData.supportsBatchUpdates();
651         ctrlDatabaseMetaData.setReturnValue(false);
652
653         mockConnection.getMetaData();
654         ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
655         mockConnection.createStatement();
656         ctrlConnection.setReturnValue(mockStatement);
657
658         ctrlStatement.replay();
659         ctrlDatabaseMetaData.replay();
660         replay();
661
662         JdbcTemplate template = new JdbcTemplate(mockDataSource, false);
663
664         try {
665             int[] actualRowsAffected = template.batchUpdate(sql);
666             fail("Shouldn't have executed batch statement with a select");
667         }
668         catch (DataAccessException ex) {
669             // pass
670 assertTrue("Check exception type", ex.getClass() == InvalidDataAccessApiUsageException.class);
671         }
672         
673         ctrlStatement.verify();
674         ctrlDatabaseMetaData.verify();
675     }
676
677     public void testBatchUpdateWithPreparedStatement() 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         MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement .class);
683         PreparedStatement mockPreparedStatement = (PreparedStatement ) ctrlPreparedStatement.getMock();
684         mockPreparedStatement.getConnection();
685         ctrlPreparedStatement.setReturnValue(mockConnection);
686         mockPreparedStatement.setInt(1, ids[0]);
687         ctrlPreparedStatement.setVoidCallable();
688         mockPreparedStatement.addBatch();
689         ctrlPreparedStatement.setVoidCallable();
690         mockPreparedStatement.setInt(1, ids[1]);
691         ctrlPreparedStatement.setVoidCallable();
692         mockPreparedStatement.addBatch();
693         ctrlPreparedStatement.setVoidCallable();
694         mockPreparedStatement.executeBatch();
695         ctrlPreparedStatement.setReturnValue(rowsAffected);
696         mockPreparedStatement.getWarnings();
697         ctrlPreparedStatement.setReturnValue(null);
698         mockPreparedStatement.close();
699         ctrlPreparedStatement.setVoidCallable();
700
701         MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData .class);
702         DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData ) ctrlDatabaseMetaData.getMock();
703         mockDatabaseMetaData.getDatabaseProductName();
704         ctrlDatabaseMetaData.setReturnValue("MySQL");
705         mockDatabaseMetaData.supportsBatchUpdates();
706         ctrlDatabaseMetaData.setReturnValue(true);
707
708         mockConnection.prepareStatement(sql);
709         ctrlConnection.setReturnValue(mockPreparedStatement);
710         mockConnection.getMetaData();
711         ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
712
713         ctrlPreparedStatement.replay();
714         ctrlDatabaseMetaData.replay();
715         replay();
716
717         BatchPreparedStatementSetter setter =
718             new BatchPreparedStatementSetter() {
719             public void setValues(PreparedStatement ps, int i)
720                 throws