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


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.math.BigDecimal ;
20 import java.sql.PreparedStatement ;
21 import java.sql.ResultSet ;
22 import java.sql.ResultSetMetaData ;
23 import java.sql.SQLException ;
24 import java.sql.Statement ;
25 import java.util.List ;
26 import java.util.Map ;
27
28 import org.easymock.MockControl;
29
30 import org.springframework.jdbc.AbstractJdbcTests;
31
32 /**
33  * @author Juergen Hoeller
34  * @since 19.12.2004
35  */
36 public class JdbcTemplateQueryTests extends AbstractJdbcTests {
37
38     public void testQueryForList() throws Exception {
39         String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
40
41         MockControl ctrlResultSetMetaData;
42         ResultSetMetaData mockResultSetMetaData;
43         MockControl ctrlResultSet;
44         ResultSet mockResultSet;
45         MockControl ctrlStatement;
46         Statement mockStatement;
47
48         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
49         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
50         mockResultSetMetaData.getColumnCount();
51         ctrlResultSetMetaData.setReturnValue(1, 2);
52         mockResultSetMetaData.getColumnName(1);
53         ctrlResultSetMetaData.setReturnValue("age", 2);
54
55         ctrlResultSet = MockControl.createControl(ResultSet .class);
56         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
57         mockResultSet.getMetaData();
58         ctrlResultSet.setReturnValue(mockResultSetMetaData, 2);
59         mockResultSet.next();
60         ctrlResultSet.setReturnValue(true);
61         mockResultSet.getObject(1);
62         ctrlResultSet.setReturnValue(new Integer (11));
63         mockResultSet.next();
64         ctrlResultSet.setReturnValue(true);
65         mockResultSet.getObject(1);
66         ctrlResultSet.setReturnValue(new Integer (12));
67         mockResultSet.next();
68         ctrlResultSet.setReturnValue(false);
69         mockResultSet.close();
70         ctrlResultSet.setVoidCallable();
71
72         ctrlStatement = MockControl.createControl(Statement .class);
73         mockStatement = (Statement ) ctrlStatement.getMock();
74         mockStatement.executeQuery(sql);
75         ctrlStatement.setReturnValue(mockResultSet);
76         mockStatement.getWarnings();
77         ctrlStatement.setReturnValue(null);
78         mockStatement.close();
79         ctrlStatement.setVoidCallable();
80
81         mockConnection.createStatement();
82         ctrlConnection.setReturnValue(mockStatement);
83
84         ctrlResultSetMetaData.replay();
85         ctrlResultSet.replay();
86         ctrlStatement.replay();
87         replay();
88
89         JdbcTemplate template = new JdbcTemplate(mockDataSource);
90
91         List li = template.queryForList(sql);
92         assertEquals("All rows returned", 2, li.size());
93         assertEquals("First row is Integer", 11, ((Integer )((Map )li.get(0)).get("age")).intValue());
94         assertEquals("Second row is Integer", 12, ((Integer )((Map )li.get(1)).get("age")).intValue());
95
96         ctrlResultSetMetaData.verify();
97         ctrlResultSet.verify();
98         ctrlStatement.verify();
99     }
100
101     public void testQueryForListWithEmptyResult() throws Exception {
102         String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
103
104         MockControl ctrlResultSet;
105         ResultSet mockResultSet;
106         MockControl ctrlStatement;
107         Statement mockStatement;
108
109         ctrlResultSet = MockControl.createControl(ResultSet .class);
110         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
111         mockResultSet.next();
112         ctrlResultSet.setReturnValue(false);
113         mockResultSet.close();
114         ctrlResultSet.setVoidCallable();
115
116         ctrlStatement = MockControl.createControl(Statement .class);
117         mockStatement = (Statement ) ctrlStatement.getMock();
118         mockStatement.executeQuery(sql);
119         ctrlStatement.setReturnValue(mockResultSet);
120         mockStatement.getWarnings();
121         ctrlStatement.setReturnValue(null);
122         mockStatement.close();
123         ctrlStatement.setVoidCallable();
124
125         mockConnection.createStatement();
126         ctrlConnection.setReturnValue(mockStatement);
127
128         ctrlResultSet.replay();
129         ctrlStatement.replay();
130         replay();
131
132         JdbcTemplate template = new JdbcTemplate(mockDataSource);
133         List li = template.queryForList(sql);
134         assertEquals("All rows returned", 0, li.size());
135
136         ctrlResultSet.verify();
137         ctrlStatement.verify();
138     }
139
140     public void testQueryForListWithSingleRowAndColumn() throws Exception {
141         String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
142
143         MockControl ctrlResultSetMetaData;
144         ResultSetMetaData mockResultSetMetaData;
145         MockControl ctrlResultSet;
146         ResultSet mockResultSet;
147         MockControl ctrlStatement;
148         Statement mockStatement;
149
150         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
151         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
152         mockResultSetMetaData.getColumnCount();
153         ctrlResultSetMetaData.setReturnValue(1);
154         mockResultSetMetaData.getColumnName(1);
155         ctrlResultSetMetaData.setReturnValue("age", 1);
156
157         ctrlResultSet = MockControl.createControl(ResultSet .class);
158         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
159         mockResultSet.getMetaData();
160         ctrlResultSet.setReturnValue(mockResultSetMetaData);
161         mockResultSet.next();
162         ctrlResultSet.setReturnValue(true);
163         mockResultSet.getObject(1);
164         ctrlResultSet.setReturnValue(new Integer (11));
165         mockResultSet.next();
166         ctrlResultSet.setReturnValue(false);
167         mockResultSet.close();
168         ctrlResultSet.setVoidCallable();
169
170         ctrlStatement = MockControl.createControl(Statement .class);
171         mockStatement = (Statement ) ctrlStatement.getMock();
172         mockStatement.executeQuery(sql);
173         ctrlStatement.setReturnValue(mockResultSet);
174         mockStatement.getWarnings();
175         ctrlStatement.setReturnValue(null);
176         mockStatement.close();
177         ctrlStatement.setVoidCallable();
178
179         mockConnection.createStatement();
180         ctrlConnection.setReturnValue(mockStatement);
181
182         ctrlResultSetMetaData.replay();
183         ctrlResultSet.replay();
184         ctrlStatement.replay();
185         replay();
186
187         JdbcTemplate template = new JdbcTemplate(mockDataSource);
188
189         List li = template.queryForList(sql);
190         assertEquals("All rows returned", 1, li.size());
191         assertEquals("First row is Integer", 11, ((Integer )((Map )li.get(0)).get("age")).intValue());
192
193         ctrlResultSetMetaData.verify();
194         ctrlResultSet.verify();
195         ctrlStatement.verify();
196     }
197
198     public void testQueryForListWithIntegerElement() throws Exception {
199         String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
200
201         MockControl ctrlResultSetMetaData;
202         ResultSetMetaData mockResultSetMetaData;
203         MockControl ctrlResultSet;
204         ResultSet mockResultSet;
205         MockControl ctrlStatement;
206         Statement mockStatement;
207
208         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
209         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
210         mockResultSetMetaData.getColumnCount();
211         ctrlResultSetMetaData.setReturnValue(1);
212
213         ctrlResultSet = MockControl.createControl(ResultSet .class);
214         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
215         mockResultSet.getMetaData();
216         ctrlResultSet.setReturnValue(mockResultSetMetaData);
217         mockResultSet.next();
218         ctrlResultSet.setReturnValue(true);
219         mockResultSet.getObject(1);
220         ctrlResultSet.setReturnValue(new Integer (11));
221         mockResultSet.next();
222         ctrlResultSet.setReturnValue(false);
223         mockResultSet.close();
224         ctrlResultSet.setVoidCallable();
225
226         ctrlStatement = MockControl.createControl(Statement .class);
227         mockStatement = (Statement ) ctrlStatement.getMock();
228         mockStatement.executeQuery(sql);
229         ctrlStatement.setReturnValue(mockResultSet);
230         mockStatement.getWarnings();
231         ctrlStatement.setReturnValue(null);
232         mockStatement.close();
233         ctrlStatement.setVoidCallable();
234
235         mockConnection.createStatement();
236         ctrlConnection.setReturnValue(mockStatement);
237
238         ctrlResultSetMetaData.replay();
239         ctrlResultSet.replay();
240         ctrlStatement.replay();
241         replay();
242
243         JdbcTemplate template = new JdbcTemplate(mockDataSource);
244
245         List li = template.queryForList(sql, Integer .class);
246         assertEquals("All rows returned", 1, li.size());
247         assertEquals("Element is Integer", 11, ((Integer ) li.get(0)).intValue());
248
249         ctrlResultSetMetaData.verify();
250         ctrlResultSet.verify();
251         ctrlStatement.verify();
252     }
253
254     public void testQueryForMapWithSingleRowAndColumn() throws Exception {
255         String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";
256
257         MockControl ctrlResultSetMetaData;
258         ResultSetMetaData mockResultSetMetaData;
259         MockControl ctrlResultSet;
260         ResultSet mockResultSet;
261         MockControl ctrlStatement;
262         Statement mockStatement;
263
264         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
265         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
266         mockResultSetMetaData.getColumnCount();
267         ctrlResultSetMetaData.setReturnValue(1);
268         mockResultSetMetaData.getColumnName(1);
269         ctrlResultSetMetaData.setReturnValue("age", 1);
270
271         ctrlResultSet = MockControl.createControl(ResultSet .class);
272         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
273         mockResultSet.getMetaData();
274         ctrlResultSet.setReturnValue(mockResultSetMetaData);
275         mockResultSet.next();
276         ctrlResultSet.setReturnValue(true);
277         mockResultSet.getObject(1);
278         ctrlResultSet.setReturnValue(new Integer (11));
279         mockResultSet.next();
280         ctrlResultSet.setReturnValue(false);
281         mockResultSet.close();
282         ctrlResultSet.setVoidCallable();
283
284         ctrlStatement = MockControl.createControl(Statement .class);
285         mockStatement = (Statement ) ctrlStatement.getMock();
286         mockStatement.executeQuery(sql);
287         ctrlStatement.setReturnValue(mockResultSet);
288         mockStatement.getWarnings();
289         ctrlStatement.setReturnValue(null);
290         mockStatement.close();
291         ctrlStatement.setVoidCallable();
292
293         mockConnection.createStatement();
294         ctrlConnection.setReturnValue(mockStatement);
295
296         ctrlResultSetMetaData.replay();
297         ctrlResultSet.replay();
298         ctrlStatement.replay();
299         replay();
300
301         JdbcTemplate template = new JdbcTemplate(mockDataSource);
302
303         Map map = template.queryForMap(sql);
304         assertEquals("Wow is Integer", 11, ((Integer ) map.get("age")).intValue());
305
306         ctrlResultSetMetaData.verify();
307         ctrlResultSet.verify();
308         ctrlStatement.verify();
309     }
310
311
312     public void testQueryForObjectWithRowMapper() throws Exception {
313         String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";
314
315         MockControl ctrlResultSet;
316         ResultSet mockResultSet;
317         MockControl ctrlStatement;
318         Statement mockStatement;
319
320         ctrlResultSet = MockControl.createControl(ResultSet .class);
321         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
322         mockResultSet.next();
323         ctrlResultSet.setReturnValue(true);
324         mockResultSet.getInt(1);
325         ctrlResultSet.setReturnValue(22);
326         mockResultSet.next();
327         ctrlResultSet.setReturnValue(false);
328         mockResultSet.close();
329         ctrlResultSet.setVoidCallable();
330
331         ctrlStatement = MockControl.createControl(Statement .class);
332         mockStatement = (Statement ) ctrlStatement.getMock();
333         mockStatement.executeQuery(sql);
334         ctrlStatement.setReturnValue(mockResultSet);
335         mockStatement.getWarnings();
336         ctrlStatement.setReturnValue(null);
337         mockStatement.close();
338         ctrlStatement.setVoidCallable();
339
340         mockConnection.createStatement();
341         ctrlConnection.setReturnValue(mockStatement);
342
343         ctrlResultSet.replay();
344         ctrlStatement.replay();
345         replay();
346
347         JdbcTemplate template = new JdbcTemplate(mockDataSource);
348
349         Object o = template.queryForObject(sql, new RowMapper() {
350             public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
351                 return new Integer (rs.getInt(1));
352             }
353         });
354         assertTrue("Correct result type", o instanceof Integer );
355
356         ctrlResultSet.verify();
357         ctrlStatement.verify();
358     }
359
360     public void testQueryForObjectWithInteger() throws Exception {
361         String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";
362
363         MockControl ctrlResultSetMetaData;
364         ResultSetMetaData mockResultSetMetaData;
365         MockControl ctrlResultSet;
366         ResultSet mockResultSet;
367         MockControl ctrlStatement;
368         Statement mockStatement;
369
370         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
371         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
372         mockResultSetMetaData.getColumnCount();
373         ctrlResultSetMetaData.setReturnValue(1);
374
375         ctrlResultSet = MockControl.createControl(ResultSet .class);
376         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
377         mockResultSet.getMetaData();
378         ctrlResultSet.setReturnValue(mockResultSetMetaData);
379         mockResultSet.next();
380         ctrlResultSet.setReturnValue(true);
381         mockResultSet.getObject(1);
382         ctrlResultSet.setReturnValue(new Integer (22));
383         mockResultSet.next();
384         ctrlResultSet.setReturnValue(false);
385         mockResultSet.close();
386         ctrlResultSet.setVoidCallable();
387
388         ctrlStatement = MockControl.createControl(Statement .class);
389         mockStatement = (Statement ) ctrlStatement.getMock();
390         mockStatement.executeQuery(sql);
391         ctrlStatement.setReturnValue(mockResultSet);
392         mockStatement.getWarnings();
393         ctrlStatement.setReturnValue(null);
394         mockStatement.close();
395         ctrlStatement.setVoidCallable();
396
397         mockConnection.createStatement();
398         ctrlConnection.setReturnValue(mockStatement);
399
400         ctrlResultSetMetaData.replay();
401         ctrlResultSet.replay();
402         ctrlStatement.replay();
403         replay();
404
405         JdbcTemplate template = new JdbcTemplate(mockDataSource);
406
407         Object o = template.queryForObject(sql, Integer .class);
408         assertTrue("Correct result type", o instanceof Integer );
409
410         ctrlResultSetMetaData.verify();
411         ctrlResultSet.verify();
412         ctrlStatement.verify();
413     }
414
415     public void testQueryForInt() throws Exception {
416         String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";
417
418         MockControl ctrlResultSetMetaData;
419         ResultSetMetaData mockResultSetMetaData;
420         MockControl ctrlResultSet;
421         ResultSet mockResultSet;
422         MockControl ctrlStatement;
423         Statement mockStatement;
424
425         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
426         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
427         mockResultSetMetaData.getColumnCount();
428         ctrlResultSetMetaData.setReturnValue(1);
429
430         ctrlResultSet = MockControl.createControl(ResultSet .class);
431         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
432         mockResultSet.getMetaData();
433         ctrlResultSet.setReturnValue(mockResultSetMetaData);
434         mockResultSet.next();
435         ctrlResultSet.setReturnValue(true);
436         mockResultSet.getObject(1);
437         ctrlResultSet.setReturnValue(new Integer (22));
438         mockResultSet.next();
439         ctrlResultSet.setReturnValue(false);
440         mockResultSet.close();
441         ctrlResultSet.setVoidCallable();
442
443         ctrlStatement = MockControl.createControl(Statement .class);
444         mockStatement = (Statement ) ctrlStatement.getMock();
445         mockStatement.executeQuery(sql);
446         ctrlStatement.setReturnValue(mockResultSet);
447         mockStatement.getWarnings();
448         ctrlStatement.setReturnValue(null);
449         mockStatement.close();
450         ctrlStatement.setVoidCallable();
451
452         mockConnection.createStatement();
453         ctrlConnection.setReturnValue(mockStatement);
454
455         ctrlResultSetMetaData.replay();
456         ctrlResultSet.replay();
457         ctrlStatement.replay();
458         replay();
459
460         JdbcTemplate template = new JdbcTemplate(mockDataSource);
461         int i = template.queryForInt(sql);
462         assertEquals("Return of an int", 22, i);
463
464         ctrlResultSetMetaData.verify();
465         ctrlResultSet.verify();
466         ctrlStatement.verify();
467     }
468
469     public void testQueryForIntValueNotFound() throws Exception {
470         String sql = "SELECT AGE FROM CUSTMR WHERE ID = -3";
471
472         MockControl ctrlResultSetMetaData;
473         ResultSetMetaData mockResultSetMetaData;
474         MockControl ctrlResultSet;
475         ResultSet mockResultSet;
476         MockControl ctrlStatement;
477         Statement mockStatement;
478
479         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
480         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
481         mockResultSetMetaData.getColumnCount();
482         ctrlResultSetMetaData.setReturnValue(1);
483
484         ctrlResultSet = MockControl.createControl(ResultSet .class);
485         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
486         mockResultSet.getMetaData();
487         ctrlResultSet.setReturnValue(mockResultSetMetaData);
488         mockResultSet.next();
489         ctrlResultSet.setReturnValue(true);
490         mockResultSet.getObject(1);
491         ctrlResultSet.setReturnValue(null);
492         mockResultSet.next();
493         ctrlResultSet.setReturnValue(false);
494         mockResultSet.close();
495         ctrlResultSet.setVoidCallable();
496
497         ctrlStatement = MockControl.createControl(Statement .class);
498         mockStatement = (Statement ) ctrlStatement.getMock();
499         mockStatement.executeQuery(sql);
500         ctrlStatement.setReturnValue(mockResultSet);
501         mockStatement.getWarnings();
502         ctrlStatement.setReturnValue(null);
503         mockStatement.close();
504         ctrlStatement.setVoidCallable();
505
506         mockConnection.createStatement();
507         ctrlConnection.setReturnValue(mockStatement);
508
509         ctrlResultSetMetaData.replay();
510         ctrlResultSet.replay();
511         ctrlStatement.replay();
512         replay();
513
514         JdbcTemplate template = new JdbcTemplate(mockDataSource);
515         int i = template.queryForInt(sql);
516         assertEquals("Return of an int", 0, i);
517
518         ctrlResultSetMetaData.verify();
519         ctrlResultSet.verify();
520         ctrlStatement.verify();
521     }
522
523     public void testQueryForLong() throws Exception {
524         String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";
525
526         MockControl ctrlResultSetMetaData;
527         ResultSetMetaData mockResultSetMetaData;
528         MockControl ctrlResultSet;
529         ResultSet mockResultSet;
530         MockControl ctrlStatement;
531         Statement mockStatement;
532
533         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
534         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
535         mockResultSetMetaData.getColumnCount();
536         ctrlResultSetMetaData.setReturnValue(1);
537
538         ctrlResultSet = MockControl.createControl(ResultSet .class);
539         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
540         mockResultSet.getMetaData();
541         ctrlResultSet.setReturnValue(mockResultSetMetaData);
542         mockResultSet.next();
543         ctrlResultSet.setReturnValue(true);
544         mockResultSet.getObject(1);
545         ctrlResultSet.setReturnValue(new BigDecimal (87.0d));
546         mockResultSet.next();
547         ctrlResultSet.setReturnValue(false);
548         mockResultSet.close();
549         ctrlResultSet.setVoidCallable();
550
551         ctrlStatement = MockControl.createControl(Statement .class);
552         mockStatement = (Statement ) ctrlStatement.getMock();
553         mockStatement.executeQuery(sql);
554         ctrlStatement.setReturnValue(mockResultSet);
555         mockStatement.getWarnings();
556         ctrlStatement.setReturnValue(null);
557         mockStatement.close();
558         ctrlStatement.setVoidCallable();
559
560         mockConnection.createStatement();
561         ctrlConnection.setReturnValue(mockStatement);
562
563         ctrlResultSetMetaData.replay();
564         ctrlResultSet.replay();
565         ctrlStatement.replay();
566         replay();
567
568         JdbcTemplate template = new JdbcTemplate(mockDataSource);
569         long l = template.queryForLong(sql);
570         assertEquals("Return of a long", 87, l);
571
572         ctrlResultSetMetaData.verify();
573         ctrlResultSet.verify();
574         ctrlStatement.verify();
575     }
576
577     public void testQueryForListWithArgs() throws Exception {
578         String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?";
579
580         MockControl ctrlResultSetMetaData;
581         ResultSetMetaData mockResultSetMetaData;
582         MockControl ctrlResultSet;
583         ResultSet mockResultSet;
584         MockControl ctrlStatement;
585         PreparedStatement mockStatement;
586
587         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
588         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
589         mockResultSetMetaData.getColumnCount();
590         ctrlResultSetMetaData.setReturnValue(1, 2);
591         mockResultSetMetaData.getColumnName(1);
592         ctrlResultSetMetaData.setReturnValue("age", 2);
593
594         ctrlResultSet = MockControl.createControl(ResultSet .class);
595         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
596         mockResultSet.getMetaData();
597         ctrlResultSet.setReturnValue(mockResultSetMetaData, 2);
598         mockResultSet.next();
599         ctrlResultSet.setReturnValue(true);
600         mockResultSet.getObject(1);
601         ctrlResultSet.setReturnValue(new Integer (11));
602         mockResultSet.next();
603         ctrlResultSet.setReturnValue(true);
604         mockResultSet.getObject(1);
605         ctrlResultSet.setReturnValue(new Integer (12));
606         mockResultSet.next();
607         ctrlResultSet.setReturnValue(false);
608         mockResultSet.close();
609         ctrlResultSet.setVoidCallable();
610
611         ctrlStatement = MockControl.createControl(PreparedStatement .class);
612         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
613         mockStatement.setObject(1, new Integer (3));
614         ctrlStatement.setVoidCallable();
615         mockStatement.executeQuery();
616         ctrlStatement.setReturnValue(mockResultSet);
617         mockStatement.getWarnings();
618         ctrlStatement.setReturnValue(null);
619         mockStatement.close();
620         ctrlStatement.setVoidCallable();
621
622         mockConnection.prepareStatement(sql);
623         ctrlConnection.setReturnValue(mockStatement);
624
625         ctrlResultSetMetaData.replay();
626         ctrlResultSet.replay();
627         ctrlStatement.replay();
628         replay();
629
630         JdbcTemplate template = new JdbcTemplate(mockDataSource);
631
632         List li = template.queryForList(sql, new Object [] {new Integer (3)});
633         assertEquals("All rows returned", 2, li.size());
634         assertEquals("First row is Integer", 11, ((Integer )((Map )li.get(0)).get("age")).intValue());
635         assertEquals("Second row is Integer", 12, ((Integer )((Map )li.get(1)).get("age")).intValue());
636
637         ctrlResultSetMetaData.verify();
638         ctrlResultSet.verify();
639         ctrlStatement.verify();
640     }
641
642     public void testQueryForListWithArgsAndEmptyResult() throws Exception {
643         String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?";
644
645         MockControl ctrlResultSet;
646         ResultSet mockResultSet;
647         MockControl ctrlStatement;
648         PreparedStatement mockStatement;
649
650         ctrlResultSet = MockControl.createControl(ResultSet .class);
651         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
652         mockResultSet.next();
653         ctrlResultSet.setReturnValue(false);
654         mockResultSet.close();
655         ctrlResultSet.setVoidCallable();
656
657         ctrlStatement = MockControl.createControl(PreparedStatement .class);
658         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
659         mockStatement.setObject(1, new Integer (3));
660         ctrlStatement.setVoidCallable();
661         mockStatement.executeQuery();
662         ctrlStatement.setReturnValue(mockResultSet);
663         mockStatement.getWarnings();
664         ctrlStatement.setReturnValue(null);
665         mockStatement.close();
666         ctrlStatement.setVoidCallable();
667
668         mockConnection.prepareStatement(sql);
669         ctrlConnection.setReturnValue(mockStatement);
670
671         ctrlResultSet.replay();
672         ctrlStatement.replay();
673         replay();
674
675         JdbcTemplate template = new JdbcTemplate(mockDataSource);
676
677         List li = template.queryForList(sql, new Object [] {new Integer (3)});
678         assertEquals("All rows returned", 0, li.size());
679
680         ctrlResultSet.verify();
681         ctrlStatement.verify();
682     }
683
684     public void testQueryForListWithArgsAndSingleRowAndColumn() throws Exception {
685         String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?";
686
687         MockControl ctrlResultSetMetaData;
688         ResultSetMetaData mockResultSetMetaData;
689         MockControl ctrlResultSet;
690         ResultSet mockResultSet;
691         MockControl ctrlStatement;
692         PreparedStatement mockStatement;
693
694         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
695         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
696         mockResultSetMetaData.getColumnCount();
697         ctrlResultSetMetaData.setReturnValue(1);
698         mockResultSetMetaData.getColumnName(1);
699         ctrlResultSetMetaData.setReturnValue("age", 1);
700
701         ctrlResultSet = MockControl.createControl(ResultSet .class);
702         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
703         mockResultSet.getMetaData();
704         ctrlResultSet.setReturnValue(mockResultSetMetaData);
705         mockResultSet.next();
706         ctrlResultSet.setReturnValue(true);
707         mockResultSet.getObject(1);
708         ctrlResultSet.setReturnValue(new Integer (11));
709         mockResultSet.next();
710         ctrlResultSet.setReturnValue(false);
711         mockResultSet.close();
712         ctrlResultSet.setVoidCallable();
713
714         ctrlStatement = MockControl.createControl(PreparedStatement .class);
715         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
716         mockStatement.setObject(1, new Integer (3));
717         ctrlStatement.setVoidCallable();
718         mockStatement.executeQuery();
719         ctrlStatement.setReturnValue(mockResultSet);
720         mockStatement.getWarnings();
721         ctrlStatement.setReturnValue(null);
722         mockStatement.close();
723         ctrlStatement.setVoidCallable();
724
725         mockConnection.prepareStatement(sql);
726         ctrlConnection.setReturnValue(mockStatement);
727
728         ctrlResultSetMetaData.replay();
729         ctrlResultSet.replay();
730         ctrlStatement.replay();
731         replay();
732
733         JdbcTemplate template = new JdbcTemplate(mockDataSource);
734
735         List li = template.queryForList(sql, new Object [] {new Integer (3)});
736         assertEquals("All rows returned", 1, li.size());
737         assertEquals("First row is Integer", 11, ((Integer )((Map )li.get(0)).get("age")).intValue());
738
739         ctrlResultSetMetaData.verify();
740         ctrlResultSet.verify();
741         ctrlStatement.verify();
742     }
743
744     public void testQueryForListWithArgsAndIntegerElementAndSingleRowAndColumn() throws Exception {
745         String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?";
746
747         MockControl ctrlResultSetMetaData;
748         ResultSetMetaData mockResultSetMetaData;
749         MockControl ctrlResultSet;
750         ResultSet mockResultSet;
751         MockControl ctrlStatement;
752         PreparedStatement mockStatement;
753
754         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
755         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
756         mockResultSetMetaData.getColumnCount();
757         ctrlResultSetMetaData.setReturnValue(1);
758
759         ctrlResultSet = MockControl.createControl(ResultSet .class);
760         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
761         mockResultSet.getMetaData();
762         ctrlResultSet.setReturnValue(mockResultSetMetaData);
763         mockResultSet.next();
764         ctrlResultSet.setReturnValue(true);
765         mockResultSet.getObject(1);
766         ctrlResultSet.setReturnValue(new Integer (11));
767         mockResultSet.next();
768         ctrlResultSet.setReturnValue(false);
769         mockResultSet.close();
770         ctrlResultSet.setVoidCallable();
771
772         ctrlStatement = MockControl.createControl(PreparedStatement .class);
773         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
774         mockStatement.setObject(1, new Integer (3));
775         ctrlStatement.setVoidCallable();
776         mockStatement.executeQuery();
777         ctrlStatement.setReturnValue(mockResultSet);
778         mockStatement.getWarnings();
779         ctrlStatement.setReturnValue(null);
780         mockStatement.close();
781         ctrlStatement.setVoidCallable();
782
783         mockConnection.prepareStatement(sql);
784         ctrlConnection.setReturnValue(mockStatement);
785
786         ctrlResultSetMetaData.replay();
787         ctrlResultSet.replay();
788         ctrlStatement.replay();
789         replay();
790
791         JdbcTemplate template = new JdbcTemplate(mockDataSource);
792
793         List li = template.queryForList(sql, new Object [] {new Integer (3)}, Integer .class);
794         assertEquals("All rows returned", 1, li.size());
795         assertEquals("First row is Integer", 11, ((Integer ) li.get(0)).intValue());
796
797         ctrlResultSetMetaData.verify();
798         ctrlResultSet.verify();
799         ctrlStatement.verify();
800     }
801
802     public void testQueryForMapWithArgsAndSingleRowAndColumn() throws Exception {
803         String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?";
804
805         MockControl ctrlResultSetMetaData;
806         ResultSetMetaData mockResultSetMetaData;
807         MockControl ctrlResultSet;
808         ResultSet mockResultSet;
809         MockControl ctrlStatement;
810         PreparedStatement mockStatement;
811
812         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
813         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
814         mockResultSetMetaData.getColumnCount();
815         ctrlResultSetMetaData.setReturnValue(1);
816         mockResultSetMetaData.getColumnName(1);
817         ctrlResultSetMetaData.setReturnValue("age", 1);
818
819         ctrlResultSet = MockControl.createControl(ResultSet .class);
820         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
821         mockResultSet.getMetaData();
822         ctrlResultSet.setReturnValue(mockResultSetMetaData);
823         mockResultSet.next();
824         ctrlResultSet.setReturnValue(true);
825         mockResultSet.getObject(1);
826         ctrlResultSet.setReturnValue(new Integer (11));
827         mockResultSet.next();
828         ctrlResultSet.setReturnValue(false);
829         mockResultSet.close();
830         ctrlResultSet.setVoidCallable();
831
832         ctrlStatement = MockControl.createControl(PreparedStatement .class);
833         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
834         mockStatement.setObject(1, new Integer (3));
835         ctrlStatement.setVoidCallable();
836         mockStatement.executeQuery();
837         ctrlStatement.setReturnValue(mockResultSet);
838         mockStatement.getWarnings();
839         ctrlStatement.setReturnValue(null);
840         mockStatement.close();
841         ctrlStatement.setVoidCallable();
842
843         mockConnection.prepareStatement(sql);
844         ctrlConnection.setReturnValue(mockStatement);
845
846         ctrlResultSetMetaData.replay();
847         ctrlResultSet.replay();
848         ctrlStatement.replay();
849         replay();
850
851         JdbcTemplate template = new JdbcTemplate(mockDataSource);
852
853         Map map = template.queryForMap(sql, new Object [] {new Integer (3)});
854         assertEquals("Row is Integer", 11, ((Integer ) map.get("age")).intValue());
855
856         ctrlResultSetMetaData.verify();
857         ctrlResultSet.verify();
858         ctrlStatement.verify();
859     }
860
861     public void testQueryForObjectWithArgsAndRowMapper() throws Exception {
862         String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?";
863
864         MockControl ctrlResultSet;
865         ResultSet mockResultSet;
866         MockControl ctrlStatement;
867         PreparedStatement mockStatement;
868
869         ctrlResultSet = MockControl.createControl(ResultSet .class);
870         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
871         mockResultSet.next();
872         ctrlResultSet.setReturnValue(true);
873         mockResultSet.getInt(1);
874         ctrlResultSet.setReturnValue(22);
875         mockResultSet.next();
876         ctrlResultSet.setReturnValue(false);
877         mockResultSet.close();
878         ctrlResultSet.setVoidCallable();
879
880         ctrlStatement = MockControl.createControl(PreparedStatement .class);
881         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
882         mockStatement.setObject(1, new Integer (3));
883         ctrlStatement.setVoidCallable();
884         mockStatement.executeQuery();
885         ctrlStatement.setReturnValue(mockResultSet);
886         mockStatement.getWarnings();
887         ctrlStatement.setReturnValue(null);
888         mockStatement.close();
889         ctrlStatement.setVoidCallable();
890
891         mockConnection.prepareStatement(sql);
892         ctrlConnection.setReturnValue(mockStatement);
893
894         ctrlResultSet.replay();
895         ctrlStatement.replay();
896         replay();
897
898         JdbcTemplate template = new JdbcTemplate(mockDataSource);
899
900         Object o = template.queryForObject(sql, new Object [] {new Integer (3)}, new RowMapper() {
901             public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
902                 return new Integer (rs.getInt(1));
903             }
904         });
905         assertTrue("Correct result type", o instanceof Integer );
906
907         ctrlResultSet.verify();
908         ctrlStatement.verify();
909     }
910
911     public void testQueryForObjectWithArgsAndInteger() throws Exception {
912         String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?";
913
914         MockControl ctrlResultSetMetaData;
915         ResultSetMetaData mockResultSetMetaData;
916         MockControl ctrlResultSet;
917         ResultSet mockResultSet;
918         MockControl ctrlStatement;
919         PreparedStatement mockStatement;
920
921         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
922         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
923         mockResultSetMetaData.getColumnCount();
924         ctrlResultSetMetaData.setReturnValue(1);
925
926         ctrlResultSet = MockControl.createControl(ResultSet .class);
927         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
928         mockResultSet.getMetaData();
929         ctrlResultSet.setReturnValue(mockResultSetMetaData);
930         mockResultSet.next();
931         ctrlResultSet.setReturnValue(true);
932         mockResultSet.getObject(1);
933         ctrlResultSet.setReturnValue(new Integer (22));
934         mockResultSet.next();
935         ctrlResultSet.setReturnValue(false);
936         mockResultSet.close();
937         ctrlResultSet.setVoidCallable();
938
939         ctrlStatement = MockControl.createControl(PreparedStatement .class);
940         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
941         mockStatement.setObject(1, new Integer (3));
942         ctrlStatement.setVoidCallable();
943         mockStatement.executeQuery();
944         ctrlStatement.setReturnValue(mockResultSet);
945         mockStatement.getWarnings();
946         ctrlStatement.setReturnValue(null);
947         mockStatement.close();
948         ctrlStatement.setVoidCallable();
949
950         mockConnection.prepareStatement(sql);
951         ctrlConnection.setReturnValue(mockStatement);
952
953         ctrlResultSetMetaData.replay();
954         ctrlResultSet.replay();
955         ctrlStatement.replay();
956         replay();
957
958         JdbcTemplate template = new JdbcTemplate(mockDataSource);
959
960         Object o = template.queryForObject(sql, new Object [] {new Integer (3)}, Integer .class);
961         assertTrue("Correct result type", o instanceof Integer );
962
963         ctrlResultSetMetaData.verify();
964         ctrlResultSet.verify();
965         ctrlStatement.verify();
966     }
967
968     public void testQueryForIntWithArgs() throws Exception {
969         String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?";
970
971         MockControl ctrlResultSetMetaData;
972         ResultSetMetaData mockResultSetMetaData;
973         MockControl ctrlResultSet;
974         ResultSet mockResultSet;
975         MockControl ctrlStatement;
976         PreparedStatement mockStatement;
977
978         ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
979         mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
980         mockResultSetMetaData.getColumnCount();
981         ctrlResultSetMetaData.setReturnValue(1);
982
983         ctrlResultSet = MockControl.createControl(ResultSet .class);
984         mockResultSet = (ResultSet ) ctrlResultSet.getMock();
985         mockResultSet.getMetaData();
986         ctrlResultSet.setReturnValue(mockResultSetMetaData);
987         mockResultSet.next();
988         ctrlResultSet.setReturnValue(true);
989         mockResultSet.getObject(1);
990         ctrlResultSet.setReturnValue(new Integer (22));
991         mockResultSet.next();
992         ctrlResultSet.setReturnValue(false);
993         mockResultSet.close();
994         ctrlResultSet.setVoidCallable();
995
996         ctrlStatement = MockControl.createControl(PreparedStatement .class);
997         mockStatement = (PreparedStatement ) ctrlStatement.getMock();
998         mockStatement.setObject(1, new Integer (3));
999         ctrlStatement.setVoidCallable();
1000        mockStatement.executeQuery();
1001        ctrlStatement.setReturnValue(mockResultSet);
1002        mockStatement.getWarnings();
1003        ctrlStatement.setReturnValue(null);
1004        mockStatement.close();
1005        ctrlStatement.setVoidCallable();
1006
1007        mockConnection.prepareStatement(sql);
1008        ctrlConnection.setReturnValue(mockStatement);
1009
1010        ctrlResultSetMetaData.replay();
1011        ctrlResultSet.replay();
1012        ctrlStatement.replay();
1013        replay();
1014
1015        JdbcTemplate template = new JdbcTemplate(mockDataSource);
1016        int i = template.queryForInt(sql, new Object [] {new Integer (3)});
1017        assertEquals("Return of an int", 22, i);
1018
1019        ctrlResultSetMetaData.verify();
1020        ctrlResultSet.verify();
1021        ctrlStatement.verify();
1022    }
1023
1024    public void testQueryForIntWithArgsAndValueNotFound() throws Exception {
1025        String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?";
1026
1027        MockControl ctrlResultSetMetaData;
1028        ResultSetMetaData mockResultSetMetaData;
1029        MockControl ctrlResultSet;
1030        ResultSet mockResultSet;
1031        MockControl ctrlStatement;
1032        PreparedStatement mockStatement;
1033
1034        ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
1035        mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
1036        mockResultSetMetaData.getColumnCount();
1037        ctrlResultSetMetaData.setReturnValue(1);
1038
1039        ctrlResultSet = MockControl.createControl(ResultSet .class);
1040        mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1041        mockResultSet.getMetaData();
1042        ctrlResultSet.setReturnValue(mockResultSetMetaData);
1043        mockResultSet.next();
1044        ctrlResultSet.setReturnValue(true);
1045        mockResultSet.getObject(1);
1046        ctrlResultSet.setReturnValue(null);
1047        mockResultSet.next();
1048        ctrlResultSet.setReturnValue(false);
1049        mockResultSet.close();
1050        ctrlResultSet.setVoidCallable();
1051
1052        ctrlStatement = MockControl.createControl(PreparedStatement .class);
1053        mockStatement = (PreparedStatement ) ctrlStatement.getMock();
1054        mockStatement.setObject(1, new Integer (-3));
1055        ctrlStatement.setVoidCallable();
1056        mockStatement.executeQuery();
1057        ctrlStatement.setReturnValue(mockResultSet);
1058        mockStatement.getWarnings();
1059        ctrlStatement.setReturnValue(null);
1060        mockStatement.close();
1061        ctrlStatement.setVoidCallable();
1062
1063        mockConnection.prepareStatement(sql);
1064        ctrlConnection.setReturnValue(mockStatement);
1065
1066        ctrlResultSetMetaData.replay();
1067        ctrlResultSet.replay();
1068        ctrlStatement.replay();
1069        replay();
1070
1071        JdbcTemplate template = new JdbcTemplate(mockDataSource);
1072        int i = template.queryForInt(sql, new Object [] {new Integer (-3)});
1073        assertEquals("Return of an int", 0, i);
1074
1075        ctrlResultSetMetaData.verify();
1076        ctrlResultSet.verify();
1077        ctrlStatement.verify();
1078    }
1079
1080    public void testQueryForLongWithArgs() throws Exception {
1081        String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?";
1082
1083        MockControl ctrlResultSetMetaData;
1084        ResultSetMetaData mockResultSetMetaData;
1085        MockControl ctrlResultSet;
1086        ResultSet mockResultSet;
1087        MockControl ctrlStatement;
1088        PreparedStatement mockStatement;
1089
1090        ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
1091        mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
1092        mockResultSetMetaData.getColumnCount();
1093        ctrlResultSetMetaData.setReturnValue(1);
1094
1095        ctrlResultSet = MockControl.createControl(ResultSet .class);
1096        mockResultSet = (ResultSet ) ctrlResultSet.getMock();
1097        mockResultSet.getMetaData();
1098        ctrlResultSet.setReturnValue(mockResultSetMetaData);
1099        mockResultSet.next();
1100        ctrlResultSet.setReturnValue(true);
1101        mockResultSet.getObject(1);
1102        ctrlResultSet.setReturnValue(new BigDecimal (87.0d));
1103        mockResultSet.next();
1104        ctrlResultSet.setReturnValue(false);
1105        mockResultSet.close();
1106        ctrlResultSet.setVoidCallable();
1107
1108        ctrlStatement = MockControl.createControl(PreparedStatement .class);
1109        mockStatement = (PreparedStatement ) ctrlStatement.getMock();
1110        mockStatement.setObject(1, new Integer (3));
1111        ctrlStatement.setVoidCallable();
1112        mockStatement.executeQuery();
1113        ctrlStatement.setReturnValue(mockResultSet);
1114        mockStatement.getWarnings();
1115        ctrlStatement.setReturnValue(null);
1116        mockStatement.close();
1117        ctrlStatement.setVoidCallable();
1118
1119        mockConnection.prepareStatement(sql);
1120        ctrlConnection.setReturnValue(mockStatement);
1121
1122        ctrlResultSetMetaData.replay();
1123        ctrlResultSet.replay();
1124        ctrlStatement.replay();
1125        replay();
1126
1127        JdbcTemplate template = new JdbcTemplate(mockDataSource);
1128        long l = template.queryForLong(sql, new Object [] {new Integer (3)});
1129        assertEquals("Return of a long", 87, l);
1130
1131        ctrlResultSetMetaData.verify();
1132        ctrlResultSet.verify();
1133        ctrlStatement.verify();
1134    }
1135
1136}
1137

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags