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 SQLException {
721                 ps.setInt(1, ids[i]);
722             }
723             public int getBatchSize() {
724                 return ids.length;
725             }
726         };
727
728         JdbcTemplate template = new JdbcTemplate(mockDataSource, false);
729
730         int[] actualRowsAffected = template.batchUpdate(sql, setter);
731         assertTrue("executed 2 updates", actualRowsAffected.length == 2);
732         assertEquals(rowsAffected[0], actualRowsAffected[0]);
733         assertEquals(rowsAffected[1], actualRowsAffected[1]);
734
735         ctrlPreparedStatement.verify();
736         ctrlDatabaseMetaData.verify();
737     }
738
739     public void testBatchUpdateWithPreparedStatementAndNoBatchSupport() throws Exception {
740         final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
741         final int[] ids = new int[] { 100, 200 };
742         final int[] rowsAffected = new int[] { 1, 2 };
743
744         MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement .class);
745         PreparedStatement mockPreparedStatement = (PreparedStatement ) ctrlPreparedStatement.getMock();
746         mockPreparedStatement.getConnection();
747         ctrlPreparedStatement.setReturnValue(mockConnection);
748         mockPreparedStatement.setInt(1, ids[0]);
749         ctrlPreparedStatement.setVoidCallable();
750         mockPreparedStatement.executeUpdate();
751         ctrlPreparedStatement.setReturnValue(rowsAffected[0]);
752         mockPreparedStatement.setInt(1, ids[1]);
753         ctrlPreparedStatement.setVoidCallable();
754         mockPreparedStatement.executeUpdate();
755         ctrlPreparedStatement.setReturnValue(rowsAffected[1]);
756         mockPreparedStatement.getWarnings();
757         ctrlPreparedStatement.setReturnValue(null);
758         mockPreparedStatement.close();
759         ctrlPreparedStatement.setVoidCallable();
760
761         mockConnection.prepareStatement(sql);
762         ctrlConnection.setReturnValue(mockPreparedStatement);
763
764         ctrlPreparedStatement.replay();
765         replay();
766
767         BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
768             public void setValues(PreparedStatement ps, int i) throws SQLException {
769                 ps.setInt(1, ids[i]);
770             }
771             public int getBatchSize() {
772                 return ids.length;
773             }
774         };
775
776         JdbcTemplate template = new JdbcTemplate(mockDataSource);
777
778         int[] actualRowsAffected = template.batchUpdate(sql, setter);
779         assertTrue("executed 2 updates", actualRowsAffected.length == 2);
780         assertEquals(rowsAffected[0], actualRowsAffected[0]);
781         assertEquals(rowsAffected[1], actualRowsAffected[1]);
782
783         ctrlPreparedStatement.verify();
784     }
785
786     public void testBatchUpdateFails() throws Exception {
787         final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
788         final int[] ids = new int[] { 100, 200 };
789         SQLException sex = new SQLException ();
790
791         MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement .class);
792         PreparedStatement mockPreparedStatement = (PreparedStatement ) ctrlPreparedStatement.getMock();
793         mockPreparedStatement.getConnection();
794         ctrlPreparedStatement.setReturnValue(mockConnection);
795         mockPreparedStatement.setInt(1, ids[0]);
796         ctrlPreparedStatement.setVoidCallable();
797         mockPreparedStatement.addBatch();
798         ctrlPreparedStatement.setVoidCallable();
799         mockPreparedStatement.setInt(1, ids[1]);
800         ctrlPreparedStatement.setVoidCallable();
801         mockPreparedStatement.addBatch();
802         ctrlPreparedStatement.setVoidCallable();
803         mockPreparedStatement.executeBatch();
804         ctrlPreparedStatement.setThrowable(sex);
805         mockPreparedStatement.close();
806         ctrlPreparedStatement.setVoidCallable();
807
808         MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData .class);
809         DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData ) ctrlDatabaseMetaData.getMock();
810         mockDatabaseMetaData.getDatabaseProductName();
811         ctrlDatabaseMetaData.setReturnValue("MySQL");
812         mockDatabaseMetaData.supportsBatchUpdates();
813         ctrlDatabaseMetaData.setReturnValue(true);
814
815         ctrlConnection.reset();
816         mockConnection.prepareStatement(sql);
817         ctrlConnection.setReturnValue(mockPreparedStatement);
818         mockConnection.getMetaData();
819         ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
820         mockConnection.close();
821         ctrlConnection.setVoidCallable(2);
822
823         ctrlPreparedStatement.replay();
824         ctrlDatabaseMetaData.replay();
825         replay();
826
827         BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
828             public void setValues(PreparedStatement ps, int i) throws SQLException {
829                 ps.setInt(1, ids[i]);
830             }
831             public int getBatchSize() {
832                 return ids.length;
833             }
834         };
835
836         try {
837             JdbcTemplate template = new JdbcTemplate(mockDataSource);
838             template.batchUpdate(sql, setter);
839             fail("Should have failed because of SQLException in bulk update");
840         }
841         catch (DataAccessException ex) {
842             assertTrue("Root cause is SQLException", ex.getCause() == sex);
843         }
844
845         ctrlPreparedStatement.verify();
846         ctrlDatabaseMetaData.verify();
847     }
848
849     public void testCouldntGetConnectionOrExceptionTranslator() throws SQLException {
850         SQLException sex = new SQLException ("foo", "07xxx");
851
852         ctrlDataSource = MockControl.createControl(DataSource .class);
853         mockDataSource = (DataSource ) ctrlDataSource.getMock();
854         mockDataSource.getConnection();
855         // Expect two calls (one call after caching data product name): make get metadata fail also
856 ctrlDataSource.setThrowable(sex, 2);
857         replay();
858
859         try {
860             JdbcTemplate template = new JdbcTemplate(mockDataSource, false);
861             RowCountCallbackHandler rcch = new RowCountCallbackHandler();
862             template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
863             fail("Shouldn't have executed query without a connection");
864         }
865         catch (CannotGetJdbcConnectionException ex) {
866             // pass
867 assertTrue("Check root cause", ex.getCause() == sex);
868         }
869         
870         ctrlDataSource.verify();
871     }
872
873     public void testCouldntGetConnectionForOperationOrExceptionTranslator() throws SQLException {
874         SQLException sex = new SQLException ("foo", "07xxx");
875
876         // Change behavior in setUp() because we only expect one call to getConnection():
877 // none is necessary to get metadata for exception translator
878 ctrlDataSource = MockControl.createControl(DataSource .class);
879         mockDataSource = (DataSource ) ctrlDataSource.getMock();
880         mockDataSource.getConnection();
881         // Expect two calls (one call after caching data product name): make get Metadata fail also
882 ctrlDataSource.setThrowable(sex, 2);
883         replay();
884
885         try {
886             JdbcTemplate template = new JdbcTemplate(mockDataSource, false);
887             RowCountCallbackHandler rcch = new RowCountCallbackHandler();
888             template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
889             fail("Shouldn't have executed query without a connection");
890         }
891         catch (CannotGetJdbcConnectionException ex) {
892             // pass
893 assertTrue("Check root cause", ex.getCause() == sex);
894         }
895
896         ctrlDataSource.verify();
897     }
898
899     public void testCouldntGetConnectionForOperationWithLazyExceptionTranslator() throws SQLException {
900         SQLException sex = new SQLException ("foo", "07xxx");
901
902         ctrlDataSource = MockControl.createControl(DataSource .class);
903         mockDataSource = (DataSource ) ctrlDataSource.getMock();
904         mockDataSource.getConnection();
905         ctrlDataSource.setThrowable(sex, 1);
906         replay();
907
908         try {
909             JdbcTemplate template2 = new JdbcTemplate();
910             template2.setDataSource(mockDataSource);
911             template2.afterPropertiesSet();
912             RowCountCallbackHandler rcch = new RowCountCallbackHandler();
913             template2.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
914             fail("Shouldn't have executed query without a connection");
915         }
916         catch (CannotGetJdbcConnectionException ex) {
917             // pass
918 assertTrue("Check root cause", ex.getCause() == sex);
919         }
920
921         ctrlDataSource.verify();
922     }
923
924     /**
925      * Verify that afterPropertiesSet invokes exception translator.
926      */
927     public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitialized() throws SQLException {
928         SQLException sex = new SQLException ("foo", "07xxx");
929
930         ctrlDataSource = MockControl.createControl(DataSource .class);
931         mockDataSource = (DataSource ) ctrlDataSource.getMock();
932         //mockConnection.getMetaData();
933 //ctrlConnection.setReturnValue(null, 1);
934 //mockConnection.close();
935 //ctrlConnection.setVoidCallable(1);
936 ctrlConnection.replay();
937         
938         // Change behaviour in setUp() because we only expect one call to getConnection():
939 // none is necessary to get metadata for exception translator
940 ctrlDataSource = MockControl.createControl(DataSource .class);
941         mockDataSource = (DataSource ) ctrlDataSource.getMock();
942         // Upfront call for metadata - no longer the case
943 //mockDataSource.getConnection();
944 //ctrlDataSource.setReturnValue(mockConnection, 1);
945 // One call for operation
946 mockDataSource.getConnection();
947         ctrlDataSource.setThrowable(sex, 2);
948         ctrlDataSource.replay();
949
950         try {
951             JdbcTemplate template = new JdbcTemplate(mockDataSource, false);
952             RowCountCallbackHandler rcch = new RowCountCallbackHandler();
953             template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
954             fail("Shouldn't have executed query without a connection");
955         }
956         catch (CannotGetJdbcConnectionException ex) {
957             // pass
958 assertTrue("Check root cause", ex.getCause() == sex);
959         }
960     
961         ctrlDataSource.verify();
962         ctrlConnection.verify();
963     }
964     
965     public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitializedViaBeanProperty()
966             throws Exception {
967         doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(true);
968     }
969     
970     public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitializedInAfterPropertiesSet()
971             throws Exception {
972         doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(false);
973     }
974     
975     /**
976      * If beanProperty is true, initialize via exception translator bean property;
977      * if false, use afterPropertiesSet().
978      */
979     private void doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(boolean beanProperty)
980             throws SQLException {
981
982         SQLException sex = new SQLException ("foo", "07xxx");
983
984         ctrlConnection = MockControl.createControl(Connection .class);
985         mockConnection = (Connection ) ctrlConnection.getMock();
986         //mockConnection.getMetaData();
987 //ctrlConnection.setReturnValue(null, 1);
988 //mockConnection.close();
989 //ctrlConnection.setVoidCallable(1);
990 ctrlConnection.replay();
991     
992         // Change behaviour in setUp() because we only expect one call to getConnection():
993 // none is necessary to get metadata for exception translator
994 ctrlDataSource = MockControl.createControl(DataSource .class);
995         mockDataSource = (DataSource ) ctrlDataSource.getMock();
996         // Upfront call for metadata - no longer the case
997 //mockDataSource.getConnection();
998 //ctrlDataSource.setReturnValue(mockConnection, 1);
999 // One call for operation
1000 mockDataSource.getConnection();
1001        ctrlDataSource.setThrowable(sex, 2);
1002        ctrlDataSource.replay();
1003
1004        try {
1005            JdbcTemplate template2 = new JdbcTemplate();
1006            template2.setDataSource(mockDataSource);
1007            template2.setLazyInit(false);
1008            if (beanProperty) {
1009                // This will get a connection.
1010 template2.setExceptionTranslator(new SQLErrorCodeSQLExceptionTranslator(mockDataSource));
1011            }
1012            else {
1013                // This will cause creation of default SQL translator.
1014 // Note that only call should be effective.
1015 template2.afterPropertiesSet();
1016                template2.afterPropertiesSet();
1017            }
1018            RowCountCallbackHandler rcch = new RowCountCallbackHandler();
1019            template2.query(
1020                "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3",
1021                rcch);
1022            fail("Shouldn't have executed query without a connection");
1023        }
1024        catch (CannotGetJdbcConnectionException ex) {
1025            // pass
1026 assertTrue("Check root cause", ex.getCause() == sex);
1027        }
1028
1029        ctrlDataSource.verify();
1030        ctrlConnection.verify();
1031    }
1032
1033    public void testPreparedStatementSetterSucceeds() throws Exception {
1034        final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1";
1035        final String name = "Gary";
1036        int expectedRowsUpdated = 1;
1037
1038        MockControl ctrlPreparedStatement =
1039            MockControl.createControl(PreparedStatement .class);
1040        PreparedStatement mockPreparedStatement =
1041            (PreparedStatement ) ctrlPreparedStatement.getMock();
1042        mockPreparedStatement.setString(1, name);
1043        ctrlPreparedStatement.setVoidCallable();
1044        mockPreparedStatement.executeUpdate();
1045        ctrlPreparedStatement.setReturnValue(expectedRowsUpdated);
1046        mockPreparedStatement.getWarnings();
1047        ctrlPreparedStatement.setReturnValue(null);
1048        mockPreparedStatement.close();
1049        ctrlPreparedStatement.setVoidCallable();
1050
1051        mockConnection.prepareStatement(sql);
1052        ctrlConnection.setReturnValue(mockPreparedStatement);
1053
1054        ctrlPreparedStatement.replay();
1055        replay();
1056
1057        PreparedStatementSetter pss = new PreparedStatementSetter() {
1058            public void setValues(PreparedStatement ps) throws SQLException {
1059                ps.setString(1, name);
1060            }
1061        };
1062        int actualRowsUpdated =
1063            new JdbcTemplate(mockDataSource).update(sql, pss);
1064        assertTrue(
1065            "updated correct # of rows",
1066            actualRowsUpdated == expectedRowsUpdated);
1067
1068        ctrlPreparedStatement.verify();
1069    }
1070
1071    public void testPreparedStatementSetterFails() throws Exception {
1072        final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1";
1073        final String name = "Gary";
1074        SQLException sex = new SQLException ();
1075
1076        MockControl ctrlPreparedStatement =
1077            MockControl.createControl(PreparedStatement .class);
1078        PreparedStatement mockPreparedStatement =
1079            (PreparedStatement ) ctrlPreparedStatement.getMock();
1080        mockPreparedStatement.setString(1, name);
1081        ctrlPreparedStatement.setVoidCallable();
1082        mockPreparedStatement.executeUpdate();
1083        ctrlPreparedStatement.setThrowable(sex);
1084        mockPreparedStatement.close();
1085        ctrlPreparedStatement.setVoidCallable();
1086
1087        mockConnection.prepareStatement(sql);
1088        ctrlConnection.setReturnValue(mockPreparedStatement);
1089
1090        ctrlPreparedStatement.replay();
1091        replay();
1092
1093        PreparedStatementSetter pss = new PreparedStatementSetter() {
1094            public void setValues(PreparedStatement ps) throws SQLException {
1095                ps.setString(1, name);
1096            }
1097        };
1098        try {
1099            new JdbcTemplate(mockDataSource).update(sql, pss);
1100            fail("Should have failed with SQLException");
1101        }
1102        catch (DataAccessException ex) {
1103            assertTrue("root cause was preserved", ex.getCause() == sex);
1104        }
1105
1106        ctrlPreparedStatement.verify();
1107    }
1108
1109    public void testCouldntClose() throws Exception {
1110        MockControl ctrlStatement = MockControl.createControl(Statement .class);
1111        Statement mockStatement = (Statement ) ctrlStatement.getMock();
1112        MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
1113        ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1114        mockConnection.createStatement();
1115        ctrlConnection.setReturnValue(mockStatement);
1116        String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";
1117        mockStatement.executeQuery(sql);
1118        ctrlStatement.setReturnValue(mockResultSet);
1119        mockResultSet.next();
1120        ctrlResultSet.setReturnValue(false);
1121        SQLException sex = new SQLException ("bar");
1122        mockResultSet.close();
1123        ctrlResultSet.setThrowable(sex);
1124        mockStatement.getWarnings();
1125        ctrlStatement.setReturnValue(null);
1126        mockStatement.close();
1127        ctrlStatement.setThrowable(sex);
1128        mockConnection.close();
1129        ctrlConnection.setThrowable(sex);
1130
1131        ctrlStatement.replay();
1132        ctrlResultSet.replay();
1133        replay();
1134
1135        JdbcTemplate template2 = new JdbcTemplate(mockDataSource);
1136        RowCountCallbackHandler rcch = new RowCountCallbackHandler();
1137        template2.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
1138
1139        ctrlStatement.verify();
1140        ctrlResultSet.verify();
1141    }
1142
1143    /**
1144     * Mock objects allow us to produce warnings at will
1145     */
1146    public void testFatalWarning() throws Exception {
1147        String sql = "SELECT forename from custmr";
1148        SQLWarning warnings = new SQLWarning ("My warning");
1149
1150        MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
1151        ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1152        mockResultSet.next();
1153        ctrlResultSet.setReturnValue(false);
1154        mockResultSet.close();
1155        ctrlResultSet.setVoidCallable();
1156
1157        MockControl ctrlStatement = MockControl.createControl(PreparedStatement .class);
1158        PreparedStatement mockStatement = (PreparedStatement ) ctrlStatement.getMock();
1159        mockStatement.executeQuery(sql);
1160        ctrlStatement.setReturnValue(mockResultSet);
1161        mockStatement.getWarnings();
1162        ctrlStatement.setReturnValue(warnings);
1163        mockStatement.close();
1164        ctrlStatement.setVoidCallable();
1165
1166        mockConnection.createStatement();
1167        ctrlConnection.setReturnValue(mockStatement);
1168
1169        ctrlResultSet.replay();
1170        ctrlStatement.replay();
1171        replay();
1172
1173        JdbcTemplate t = new JdbcTemplate(mockDataSource);
1174        t.setIgnoreWarnings(false);
1175        try {
1176            t.query(sql, new RowCallbackHandler() {
1177                public void processRow(ResultSet rs) throws SQLException {
1178                    rs.getByte(1);
1179                }
1180            });
1181            fail("Should have thrown exception on warning");
1182        }
1183        catch (SQLWarningException ex) {
1184            // Pass
1185 assertTrue(
1186                "Root cause of warning was correct",
1187                ex.getCause() == warnings);
1188        }
1189
1190        ctrlResultSet.verify();
1191        ctrlStatement.verify();
1192    }
1193
1194    public void testIgnoredWarning() throws Exception {
1195        String sql = "SELECT forename from custmr";
1196        SQLWarning warnings = new SQLWarning ("My warning");
1197
1198        MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
1199        ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1200        mockResultSet.next();
1201        ctrlResultSet.setReturnValue(false);
1202        mockResultSet.close();
1203        ctrlResultSet.setVoidCallable();
1204
1205        MockControl ctrlStatement =
1206            MockControl.createControl(PreparedStatement .class);
1207        PreparedStatement mockStatement =
1208            (PreparedStatement ) ctrlStatement.getMock();
1209        mockStatement.executeQuery(sql);
1210        ctrlStatement.setReturnValue(mockResultSet);
1211        mockStatement.getWarnings();
1212        ctrlStatement.setReturnValue(warnings);
1213        mockStatement.close();
1214        ctrlStatement.setVoidCallable();
1215
1216        mockConnection.createStatement();
1217        ctrlConnection.setReturnValue(mockStatement);
1218
1219        ctrlResultSet.replay();
1220        ctrlStatement.replay();
1221        replay();
1222
1223        // Too long: truncation
1224 JdbcTemplate template = new JdbcTemplate(mockDataSource);
1225        template.setIgnoreWarnings(true);
1226        template.query(sql, new RowCallbackHandler() {
1227            public void processRow(ResultSet rs)
1228                throws java.sql.SQLException {
1229                rs.getByte(1);
1230            }
1231        });
1232
1233        ctrlResultSet.verify();
1234        ctrlStatement.verify();
1235    }
1236
1237    public void testSQLErrorCodeTranslation() throws Exception {
1238        final SQLException sex = new SQLException ("I have a known problem", "99999", 1054);
1239        final String sql = "SELECT ID FROM CUSTOMER";
1240
1241        MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
1242        ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1243        mockResultSet.next();
1244        ctrlResultSet.setReturnValue(true);
1245        mockResultSet.close();
1246        ctrlResultSet.setVoidCallable();
1247
1248        MockControl ctrlStatement = MockControl.createControl(PreparedStatement .class);
1249        PreparedStatement mockStatement = (PreparedStatement ) ctrlStatement.getMock();
1250        mockStatement.executeQuery(sql);
1251        ctrlStatement.setReturnValue(mockResultSet);
1252        mockStatement.close();
1253        ctrlStatement.setVoidCallable();
1254
1255        MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData .class);
1256        DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData ) ctrlDatabaseMetaData.getMock();
1257        mockDatabaseMetaData.getDatabaseProductName();
1258        ctrlDatabaseMetaData.setReturnValue("MySQL");
1259
1260        mockConnection.createStatement();
1261        ctrlConnection.setReturnValue(mockStatement);
1262        mockConnection.getMetaData();
1263        ctrlConnection.setReturnValue(mockDatabaseMetaData);
1264
1265        ctrlResultSet.replay();
1266        ctrlStatement.replay();
1267        ctrlDatabaseMetaData.replay();
1268        replay();
1269
1270        JdbcTemplate template = new JdbcTemplate(mockDataSource);
1271        try {
1272            template.query(sql, new RowCallbackHandler() {
1273                public void processRow(ResultSet rs) throws SQLException {
1274                    throw sex;
1275                }
1276            });
1277            fail("Should have thrown BadSqlGrammarException");
1278        }
1279        catch (BadSqlGrammarException ex) {
1280            // expected
1281 assertTrue("Wanted same exception back, not " + ex, sex == ex.getCause());
1282        }
1283
1284        ctrlResultSet.verify();
1285        ctrlStatement.verify();
1286        ctrlDatabaseMetaData.verify();
1287    }
1288
1289    public void testSQLErrorCodeTranslationWithSpecifiedDbName() throws Exception {
1290        final SQLException sex = new SQLException ("I have a known problem", "99999", 1054);
1291        final String sql = "SELECT ID FROM CUSTOMER";
1292
1293        MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
1294        ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1295        mockResultSet.next();
1296        ctrlResultSet.setReturnValue(true);
1297        mockResultSet.close();
1298        ctrlResultSet.setVoidCallable();
1299
1300        MockControl ctrlStatement = MockControl.createControl(PreparedStatement .class);
1301        PreparedStatement mockStatement = (PreparedStatement ) ctrlStatement.getMock();
1302        mockStatement.executeQuery(sql);
1303        ctrlStatement.setReturnValue(mockResultSet);
1304        mockStatement.close();
1305        ctrlStatement.setVoidCallable();
1306
1307        mockConnection.createStatement();
1308        ctrlConnection.setReturnValue(mockStatement);
1309
1310        ctrlResultSet.replay();
1311        ctrlStatement.replay();
1312        replay();
1313
1314        JdbcTemplate template = new JdbcTemplate();
1315        template.setDataSource(mockDataSource);
1316        template.setDatabaseProductName("MySQL");
1317        template.afterPropertiesSet();
1318        try {
1319            template.query(sql, new RowCallbackHandler() {
1320                public void processRow(ResultSet rs) throws SQLException {
1321                    throw sex;
1322                }
1323            });
1324            fail("Should have thrown BadSqlGrammarException");
1325        }
1326        catch (BadSqlGrammarException ex) {
1327            // expected
1328 assertTrue("Wanted same exception back, not " + ex, sex == ex.getCause());
1329        }
1330
1331        ctrlResultSet.verify();
1332        ctrlStatement.verify();
1333    }
1334
1335    /**
1336     * Test that we see an SQLException translated using Error Code.
1337     * If we provide the SQLExceptionTranslator, we shouldn't use a connection
1338     * to get the metadata
1339     */
1340    public void testUseCustomSQLErrorCodeTranslator() throws Exception {
1341        // Bad SQL state
1342 final SQLException sex = new SQLException ("I have a known problem", "07000", 1054);
1343        final String sql = "SELECT ID FROM CUSTOMER";
1344
1345        MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
1346        ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1347        mockResultSet.next();
1348        ctrlResultSet.setReturnValue(true);
1349        mockResultSet.close();
1350        ctrlResultSet.setVoidCallable();
1351
1352        MockControl ctrlStatement = MockControl.createControl(PreparedStatement .class);
1353        PreparedStatement mockStatement = (PreparedStatement ) ctrlStatement.getMock();
1354        mockStatement.executeQuery(sql);
1355        ctrlStatement.setReturnValue(mockResultSet);
1356        mockStatement.close();
1357        ctrlStatement.setVoidCallable();
1358
1359        mockConnection.createStatement();
1360        ctrlConnection.setReturnValue(mockStatement);
1361        
1362        // Change behaviour in setUp() because we only expect one call to getConnection():
1363 // none is necessary to get metadata for exception translator
1364 ctrlConnection = MockControl.createControl(Connection .class);
1365        mockConnection = (Connection ) ctrlConnection.getMock();
1366        mockConnection.createStatement();
1367        ctrlConnection.setReturnValue(mockStatement, 1);
1368        mockConnection.close();
1369        ctrlConnection.setVoidCallable(1);
1370        ctrlConnection.replay();
1371
1372        ctrlDataSource = MockControl.createControl(DataSource .class);
1373        mockDataSource = (DataSource ) ctrlDataSource.getMock();
1374        mockDataSource.getConnection();
1375        ctrlDataSource.setReturnValue(mockConnection, 1);
1376        ctrlDataSource.replay();
1377        ///// end changed behaviour
1378
1379        ctrlResultSet.replay();
1380        ctrlStatement.replay();
1381
1382        JdbcTemplate template = new JdbcTemplate();
1383        template.setDataSource(mockDataSource);
1384        // Set custom exception translator
1385 template.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
1386        template.afterPropertiesSet();
1387        try {
1388            template.query(sql, new RowCallbackHandler() {
1389                public void processRow(ResultSet rs)
1390                    throws SQLException {
1391                    throw sex;
1392                }
1393            });
1394            fail("Should have thrown exception");
1395        }
1396        catch (BadSqlGrammarException ex) {
1397            assertTrue(
1398                "Wanted same exception back, not " + ex,
1399                sex == ex.getCause());
1400        }
1401
1402        ctrlResultSet.verify();
1403        ctrlStatement.verify();
1404        
1405        // We didn't call superclass replay() so we need to check these ourselves
1406 ctrlDataSource.verify();
1407        ctrlConnection.verify();
1408    }
1409
1410    public void testNativeJdbcExtractorInvoked() throws Exception {
1411        MockControl ctrlResultSet = MockControl.createControl(ResultSet .class);
1412        final ResultSet mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1413        mockResultSet.close();
1414        ctrlResultSet.setVoidCallable(2);
1415
1416        MockControl ctrlStatement = MockControl.createControl(Statement .class);
1417        final Statement mockStatement = (Statement ) ctrlStatement.getMock();
1418        mockStatement.getWarnings();
1419        ctrlStatement.setReturnValue(null);
1420        mockStatement.close();
1421        ctrlStatement.setVoidCallable();
1422        MockControl ctrlStatement2 = MockControl.createControl(Statement .class);
1423        final Statement mockStatement2 = (Statement ) ctrlStatement2.getMock();
1424        mockStatement2.executeQuery("my query");
1425        ctrlStatement2.setReturnValue(mockResultSet, 1);
1426
1427        MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement .class);
1428        final PreparedStatement mockPreparedStatement = (PreparedStatement ) ctrlPreparedStatement.getMock();
1429        mockPreparedStatement.getWarnings();
1430        ctrlPreparedStatement.setReturnValue(null);
1431        mockPreparedStatement.close();
1432        ctrlPreparedStatement.setVoidCallable();
1433        MockControl ctrlPreparedStatement2 = MockControl.createControl(PreparedStatement .class);
1434        final PreparedStatement mockPreparedStatement2 = (PreparedStatement ) ctrlPreparedStatement2.getMock();
1435        mockPreparedStatement2.executeQuery();
1436        ctrlPreparedStatement2.setReturnValue(mockResultSet, 1);
1437
1438        MockControl ctrlCallableStatement = MockControl.createControl(CallableStatement .class);
1439        final CallableStatement mockCallableStatement = (CallableStatement ) ctrlCallableStatement.getMock();
1440        mockCallableStatement.getWarnings();
1441        ctrlCallableStatement.setReturnValue(null);
1442        mockCallableStatement.close();
1443        ctrlCallableStatement.setVoidCallable();
1444        MockControl ctrlCallableStatement2 = MockControl.createControl(CallableStatement .class);
1445        final CallableStatement mockCallableStatement2 = (CallableStatement ) ctrlCallableStatement2.getMock();
1446        mockCallableStatement2.execute();
1447        ctrlCallableStatement2.setReturnValue(true);
1448        mockCallableStatement2.getUpdateCount();
1449        ctrlCallableStatement2.setReturnValue(-1);
1450        mockCallableStatement2.getMoreResults();
1451        ctrlCallableStatement2.setReturnValue(false);
1452        mockCallableStatement2.getUpdateCount();
1453        ctrlCallableStatement2.setReturnValue(-1);
1454
1455        ctrlResultSet.replay();
1456        ctrlStatement.replay();
1457        ctrlStatement2.replay();
1458        ctrlPreparedStatement.replay();
1459        ctrlPreparedStatement2.replay();
1460        ctrlCallableStatement.replay();
1461        ctrlCallableStatement2.replay();
1462
1463        mockConnection.createStatement();
1464        ctrlConnection.setReturnValue(mockStatement, 1);
1465        replay();
1466
1467        JdbcTemplate template = new JdbcTemplate(mockDataSource);
1468        template.setNativeJdbcExtractor(new NativeJdbcExtractor() {
1469            public boolean isNativeConnectionNecessaryForNativeStatements() {
1470                return false;
1471            }
1472            public boolean isNativeConnectionNecessaryForNativePreparedStatements() {
1473                return false;
1474            }
1475            public boolean isNativeConnectionNecessaryForNativeCallableStatements() {
1476                return false;
1477            }
1478            public Connection getNativeConnection(Connection con) {
1479                return con;
1480            }
1481            public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException {
1482                return stmt.getConnection();
1483            }
1484            public Statement getNativeStatement(Statement stmt) {
1485                assertTrue(stmt == mockStatement);
1486                return mockStatement2;
1487            }
1488            public PreparedStatement getNativePreparedStatement(PreparedStatement ps) {
1489                assertTrue(ps == mockPreparedStatement);
1490                return mockPreparedStatement2;
1491            }
1492            public CallableStatement getNativeCallableStatement(CallableStatement cs) {
1493                assertTrue(cs == mockCallableStatement);
1494                return mockCallableStatement2;
1495            }
1496            public ResultSet getNativeResultSet(ResultSet rs) {
1497                return rs;
1498            }
1499        });
1500
1501        template.query("my query", new ResultSetExtractor() {
1502            public Object extractData(ResultSet rs2) {
1503                assertEquals(mockResultSet, rs2);
1504                return null;
1505            }
1506        });
1507
1508        template.query(new PreparedStatementCreator() {
1509            public PreparedStatement createPreparedStatement(Connection conn) {
1510                return mockPreparedStatement;
1511            }
1512        }, new ResultSetExtractor() {
1513            public Object extractData(ResultSet rs2) {
1514                assertEquals(mockResultSet, rs2);
1515                return null;
1516            }
1517        });
1518
1519        template.call(new CallableStatementCreator() {
1520            public CallableStatement createCallableStatement(Connection con) {
1521                return mockCallableStatement;
1522            }
1523        },
1524        new ArrayList ());
1525
1526        ctrlStatement.verify();
1527        ctrlStatement2.verify();
1528        ctrlPreparedStatement.verify();
1529        ctrlPreparedStatement2.verify();
1530        ctrlCallableStatement.verify();
1531        ctrlCallableStatement2.verify();
1532    }
1533
1534    public void testStaticResultSetClosed() throws Exception {
1535        MockControl ctrlResultSet;
1536        ResultSet mockResultSet;
1537        MockControl ctrlStatement;
1538        Statement mockStatement;
1539        MockControl ctrlResultSet2;
1540        ResultSet mockResultSet2;
1541        MockControl ctrlPreparedStatement;
1542        PreparedStatement mockPreparedStatement;
1543
1544        ctrlResultSet = MockControl.createControl(ResultSet .class);
1545        mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1546        mockResultSet.close();
1547        ctrlResultSet.setVoidCallable();
1548
1549        ctrlStatement = MockControl.createControl(Statement .class);
1550        mockStatement = (Statement ) ctrlStatement.getMock();
1551        mockStatement.executeQuery("my query");
1552        ctrlStatement.setReturnValue(mockResultSet);
1553        mockStatement.close();
1554        ctrlStatement.setVoidCallable();
1555
1556        ctrlResultSet2 = MockControl.createControl(ResultSet .class);
1557        mockResultSet2 = (ResultSet ) ctrlResultSet2.getMock();
1558        mockResultSet2.close();
1559        ctrlResultSet2.setVoidCallable();
1560
1561        ctrlPreparedStatement = MockControl.createControl(PreparedStatement .class);
1562        mockPreparedStatement = (PreparedStatement ) ctrlPreparedStatement.getMock();
1563        mockPreparedStatement.executeQuery();
1564        ctrlPreparedStatement.setReturnValue(mockResultSet2);
1565        mockPreparedStatement.close();
1566        ctrlPreparedStatement.setVoidCallable();
1567
1568        mockConnection.createStatement();
1569        ctrlConnection.setReturnValue(mockStatement);
1570        mockConnection.prepareStatement("my query");
1571        ctrlConnection.setReturnValue(mockPreparedStatement);
1572
1573        ctrlResultSet.replay();
1574        ctrlStatement.replay();
1575        ctrlResultSet2.replay();
1576        ctrlPreparedStatement.replay();
1577        replay();
1578
1579        JdbcTemplate template = new JdbcTemplate(mockDataSource);
1580
1581        try {
1582            template.query("my query", new ResultSetExtractor() {
1583                public Object extractData(ResultSet rs) {
1584                    throw new InvalidDataAccessApiUsageException("");
1585                }
1586            });
1587            fail("Should have thrown InvalidDataAccessApiUsageException");
1588        }
1589        catch (InvalidDataAccessApiUsageException idaauex) {
1590            // ok
1591 }
1592
1593        try {
1594            template.query(new PreparedStatementCreator() {
1595                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
1596                    return con.prepareStatement("my query");
1597                }
1598                public String getSql() {
1599                    return null;
1600                }
1601            }, new ResultSetExtractor() {
1602                public Object extractData(ResultSet rs2) {
1603                    throw new InvalidDataAccessApiUsageException("");
1604                }
1605            });
1606            fail("Should have thrown InvalidDataAccessApiUsageException");
1607        }
1608        catch (InvalidDataAccessApiUsageException idaauex) {
1609            // ok
1610 }
1611
1612        // verify confirms if test is successful by checking if close() called
1613 ctrlResultSet.verify();
1614        ctrlStatement.verify();
1615        ctrlResultSet2.verify();
1616        ctrlPreparedStatement.verify();
1617    }
1618
1619    public void testExecuteClosed() throws Exception {
1620        MockControl ctrlResultSet;
1621        ResultSet mockResultSet;
1622        MockControl ctrlCallable;
1623        CallableStatement mockCallable;
1624
1625        ctrlResultSet = MockControl.createControl(ResultSet .class);
1626        mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1627        mockResultSet.next();
1628        ctrlResultSet.setReturnValue(true);
1629        mockResultSet.close();
1630        ctrlResultSet.setVoidCallable();
1631
1632        ctrlCallable = MockControl.createControl(CallableStatement .class);
1633        mockCallable = (CallableStatement ) ctrlCallable.getMock();
1634        mockCallable.execute();
1635        ctrlCallable.setReturnValue(true);
1636        mockCallable.getUpdateCount();
1637        ctrlCallable.setReturnValue(-1);
1638        mockCallable.getResultSet();
1639        ctrlCallable.setReturnValue(mockResultSet);
1640        mockCallable.close();
1641        ctrlCallable.setVoidCallable();
1642
1643        mockConnection.prepareCall("my query");
1644        ctrlConnection.setReturnValue(mockCallable);
1645
1646        ctrlResultSet.replay();
1647        ctrlCallable.replay();
1648        replay();
1649
1650        List params = new ArrayList ();
1651        params.add(new SqlReturnResultSet("", new RowCallbackHandler() {
1652            public void processRow(ResultSet rs) {
1653                throw new InvalidDataAccessApiUsageException("");
1654            }
1655
1656        }));
1657
1658        JdbcTemplate template = new JdbcTemplate(mockDataSource);
1659        try {
1660            template.call(new CallableStatementCreator() {
1661                public CallableStatement createCallableStatement(Connection conn)
1662                    throws SQLException {
1663                    return conn.prepareCall("my query");
1664                }
1665            }, params);
1666        }
1667        catch (InvalidDataAccessApiUsageException idaauex) {
1668            // ok
1669 }
1670
1671        // verify confirms if test is successful by checking if close() called
1672 ctrlResultSet.verify();
1673        ctrlCallable.verify();
1674    }
1675
1676
1677    private static interface JdbcTemplateCallback {
1678
1679        void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch);
1680    }
1681
1682
1683    private static class Dispatcher implements PreparedStatementCreator, SqlProvider {
1684
1685        private int id;
1686        private String sql;
1687
1688        public Dispatcher(int id, String sql) {
1689            this.id = id;
1690            this.sql = sql;
1691        }
1692
1693        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
1694            PreparedStatement ps = conn.prepareStatement(sql);
1695            ps.setInt(1, id);
1696            return ps;
1697        }
1698
1699        public String getSql() {
1700            return sql;
1701        }
1702    }
1703
1704}
1705

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags