|
1
16
17 package org.springframework.jdbc.object;
18
19 import java.sql.PreparedStatement  ;
20 import java.sql.ResultSet ;
21 import java.sql.ResultSetMetaData ;
22 import java.sql.SQLException ;
23 import java.sql.Types ;
24 import java.util.HashMap ;
25 import java.util.Iterator ;
26 import java.util.List ;
27 import java.util.Map ;
28
29 import javax.sql.DataSource ;
30
31 import org.easymock.MockControl;
32
33 import org.springframework.dao.IncorrectResultSizeDataAccessException;
34 import org.springframework.dao.InvalidDataAccessApiUsageException;
35 import org.springframework.jdbc.AbstractJdbcTests;
36 import org.springframework.jdbc.core.JdbcTemplate;
37 import org.springframework.jdbc.core.SqlParameter;
38
39
43 public class SqlQueryTests extends AbstractJdbcTests {
44
45 private static final String SELECT_ID = "select id from custmr";
46 private static final String SELECT_ID_WHERE =
47 "select id from custmr where forename = ? and id = ?";
48 private static final String SELECT_FORENAME = "select forename from custmr";
49 private static final String SELECT_FORENAME_EMPTY =
50 "select forename from custmr WHERE 1 = 2";
51 private static final String SELECT_ID_FORENAME_WHERE =
52 "select id, forename from custmr where forename = ?";
53 private static final String SELECT_ID_FORENAME_WHERE_ID =
54 "select id, forename from custmr where id <= ?";
55
56 private static final String [] COLUMN_NAMES = new String [] { "id", "forename" };
57 private static final int[] COLUMN_TYPES = new int[] { Types.INTEGER, Types.VARCHAR };
58
59 private MockControl ctrlPreparedStatement;
60 private PreparedStatement mockPreparedStatement;
61 private MockControl ctrlResultSet;
62 private ResultSet mockResultSet;
63
64 protected void setUp() throws Exception {
65 super.setUp();
66 ctrlPreparedStatement = MockControl.createControl(PreparedStatement .class);
67 mockPreparedStatement = (PreparedStatement ) ctrlPreparedStatement.getMock();
68 ctrlResultSet = MockControl.createControl(ResultSet .class);
69 mockResultSet = (ResultSet ) ctrlResultSet.getMock();
70 }
71
72 protected void tearDown() throws Exception {
73 super.tearDown();
74 ctrlPreparedStatement.verify();
75 ctrlResultSet.verify();
76 }
77
78 protected void replay() {
79 super.replay();
80 ctrlPreparedStatement.replay();
81 ctrlResultSet.replay();
82 }
83
84 public void testQueryWithoutParams() throws SQLException {
85 mockResultSet.next();
86 ctrlResultSet.setReturnValue(true);
87 mockResultSet.getInt(1);
88 ctrlResultSet.setReturnValue(1);
89 mockResultSet.next();
90 ctrlResultSet.setReturnValue(false);
91 mockResultSet.close();
92 ctrlResultSet.setVoidCallable();
93
94 mockPreparedStatement.executeQuery();
95 ctrlPreparedStatement.setReturnValue(mockResultSet);
96 mockPreparedStatement.getWarnings();
97 ctrlPreparedStatement.setReturnValue(null);
98 mockPreparedStatement.close();
99 ctrlPreparedStatement.setVoidCallable();
100
101 mockConnection.prepareStatement(SELECT_ID);
102 ctrlConnection.setReturnValue(mockPreparedStatement);
103
104 replay();
105
106 SqlQuery query = new MappingSqlQueryWithParameters() {
107 protected Object mapRow(
108 ResultSet rs,
109 int rownum,
110 Object [] params,
111 Map context)
112 throws SQLException {
113 assertTrue("params were null", params == null);
114 assertTrue("context was null", context == null);
115 return new Integer (rs.getInt(1));
116 }
117 };
118
119 query.setDataSource(mockDataSource);
120 query.setSql(SELECT_ID);
121 query.compile();
122 List list = query.execute();
123 assertTrue("Found customers", list.size() != 0);
124 for (Iterator itr = list.iterator(); itr.hasNext();) {
125 Integer id = (Integer ) itr.next();
126 assertTrue(
127 "Customer id was assigned correctly",
128 id.intValue() == 1);
129 }
130 }
131
132 public void testQueryWithoutEnoughParams() {
133 replay();
134
135 MappingSqlQuery query = new MappingSqlQuery() {
136 protected Object mapRow(ResultSet rs, int rownum)
137 throws SQLException {
138 return new Integer (rs.getInt(1));
139 }
140
141 };
142 query.setDataSource(mockDataSource);
143 query.setSql(SELECT_ID_WHERE);
144 query.declareParameter(
145 new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
146 query.declareParameter(
147 new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
148 query.compile();
149
150 try {
151 List list = query.execute();
152 fail("Shouldn't succeed in running query without enough params");
153 }
154 catch (InvalidDataAccessApiUsageException ex) {
155 }
157 }
158
159 public void testBindVariableCountWrong() {
160 replay();
161
162 MappingSqlQuery query = new MappingSqlQuery() {
163 protected Object mapRow(ResultSet rs, int rownum)
164 throws SQLException {
165 return new Integer (rs.getInt(1));
166 }
167 };
168 query.setDataSource(mockDataSource);
169 query.setSql(SELECT_ID_WHERE);
170 query.declareParameter(
171 new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
172 query.declareParameter(
173 new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
174 query.declareParameter(new SqlParameter("NONEXISTENT", Types.VARCHAR));
175 try {
176 query.compile();
177 fail("Shouldn't succeed in compiling query with bind var mismatch");
178 }
179 catch (InvalidDataAccessApiUsageException ex) {
180 }
182 }
183
184 public void testStringQueryWithResults() throws Exception {
185 String [] dbResults = new String [] { "alpha", "beta", "charlie" };
186
187 MockControl[] ctrlCountResultSetMetaData = new MockControl[3];
188 ResultSetMetaData [] mockCountResultSetMetaData = new ResultSetMetaData [3];
189 MockControl[] ctrlCountResultSet = new MockControl[3];
190 ResultSet [] mockCountResultSet = new ResultSet [3];
191 MockControl[] ctrlCountPreparedStatement = new MockControl[3];
192 PreparedStatement [] mockCountPreparedStatement = new PreparedStatement [3];
193
194 mockResultSet.next();
195 ctrlResultSet.setReturnValue(true);
196 mockResultSet.getString(1);
197 ctrlResultSet.setReturnValue(dbResults[0]);
198 mockResultSet.next();
199 ctrlResultSet.setReturnValue(true);
200 mockResultSet.getString(1);
201 ctrlResultSet.setReturnValue(dbResults[1]);
202 mockResultSet.next();
203 ctrlResultSet.setReturnValue(true);
204 mockResultSet.getString(1);
205 ctrlResultSet.setReturnValue(dbResults[2]);
206 mockResultSet.next();
207 ctrlResultSet.setReturnValue(false);
208 mockResultSet.close();
209 ctrlResultSet.setVoidCallable();
210
211 mockPreparedStatement.executeQuery();
212 ctrlPreparedStatement.setReturnValue(mockResultSet);
213 mockPreparedStatement.getWarnings();
214 ctrlPreparedStatement.setReturnValue(null);
215 mockPreparedStatement.close();
216 ctrlPreparedStatement.setVoidCallable();
217
218 mockConnection.prepareStatement(SELECT_FORENAME);
219 ctrlConnection.setReturnValue(mockPreparedStatement);
220
221 for (int i = 0; i < dbResults.length; i++) {
222 ctrlCountResultSetMetaData[i] = MockControl.createControl(ResultSetMetaData .class);
223 mockCountResultSetMetaData[i] = (ResultSetMetaData ) ctrlCountResultSetMetaData[i].getMock();
224 mockCountResultSetMetaData[i].getColumnCount();
225 ctrlCountResultSetMetaData[i].setReturnValue(1);
226
227 ctrlCountResultSet[i] = MockControl.createControl(ResultSet .class);
228 mockCountResultSet[i] = (ResultSet ) ctrlCountResultSet[i].getMock();
229 mockCountResultSet[i].getMetaData();
230 ctrlCountResultSet[i].setReturnValue(mockCountResultSetMetaData[i]);
231 mockCountResultSet[i].next();
232 ctrlCountResultSet[i].setReturnValue(true);
233 mockCountResultSet[i].getObject(1);
234 ctrlCountResultSet[i].setReturnValue(new Integer (1));
235 mockCountResultSet[i].next();
236 ctrlCountResultSet[i].setReturnValue(false);
237 mockCountResultSet[i].close();
238 ctrlCountResultSet[i].setVoidCallable();
239
240 ctrlCountPreparedStatement[i] = MockControl.createControl(PreparedStatement .class);
241 mockCountPreparedStatement[i] = (PreparedStatement ) ctrlCountPreparedStatement[i].getMock();
242 mockCountPreparedStatement[i].executeQuery();
243 ctrlCountPreparedStatement[i].setReturnValue(mockCountResultSet[i]);
244 mockCountPreparedStatement[i].getWarnings();
245 ctrlCountPreparedStatement[i].setReturnValue(null);
246 mockCountPreparedStatement[i].close();
247 ctrlCountPreparedStatement[i].setVoidCallable();
248
249 mockConnection.prepareStatement(
250 "SELECT COUNT(FORENAME) FROM CUSTMR WHERE FORENAME='" + dbResults[i] + "'");
251 ctrlConnection.setReturnValue(mockCountPreparedStatement[i]);
252
253 ctrlCountResultSetMetaData[i].replay();
254 ctrlCountResultSet[i].replay();
255 ctrlCountPreparedStatement[i].replay();
256 }
257
258 replay();
259
260 StringQuery query = new StringQuery(mockDataSource, SELECT_FORENAME);
261 query.setRowsExpected(3);
262 String [] results = query.run();
263 assertTrue("Array is non null", results != null);
264 assertTrue("Found results", results.length > 0);
265 assertTrue(
266 "Found expected number of results",
267 query.getRowsExpected() == 3);
268
269 JdbcTemplate helper = new JdbcTemplate(mockDataSource);
270 for (int i = 0; i < results.length; i++) {
271 int dbCount = helper.queryForInt(
273 "SELECT COUNT(FORENAME) FROM CUSTMR WHERE FORENAME='" + results[i] + "'", null);
274 assertTrue("found in db", dbCount == 1);
275 }
276
277 for (int i = 0; i < dbResults.length; i++) {
278 ctrlCountResultSetMetaData[i].verify();
279 ctrlCountResultSet[i].verify();
280 ctrlCountPreparedStatement[i].verify();
281 }
282 }
283
284 public void testStringQueryWithoutResults() throws SQLException {
285 mockResultSet.next();
286 ctrlResultSet.setReturnValue(false);
287 mockResultSet.close();
288 ctrlResultSet.setVoidCallable();
289
290 mockPreparedStatement.executeQuery();
291 ctrlPreparedStatement.setReturnValue(mockResultSet);
292 mockPreparedStatement.getWarnings();
293 ctrlPreparedStatement.setReturnValue(null);
294 mockPreparedStatement.close();
295 ctrlPreparedStatement.setVoidCallable();
296
297 mockConnection.prepareStatement(SELECT_FORENAME_EMPTY);
298 ctrlConnection.setReturnValue(mockPreparedStatement);
299
300 replay();
301
302 StringQuery query = new StringQuery(mockDataSource, SELECT_FORENAME_EMPTY);
303 String [] results = query.run();
304 assertTrue("Array is non null", results != null);
305 assertTrue("Found 0 results", results.length == 0);
306 }
307
308 public void XtestAnonCustomerQuery() {
309
361 }
362
363 public void testFindCustomerIntInt() throws SQLException {
364 mockResultSet.next();
365 ctrlResultSet.setReturnValue(true);
366 mockResultSet.getInt("id");
367 ctrlResultSet.setReturnValue(1);
368 mockResultSet.getString("forename");
369 ctrlResultSet.setReturnValue("rod");
370 mockResultSet.next();
371 ctrlResultSet.setReturnValue(false);
372 mockResultSet.close();
373 ctrlResultSet.setVoidCallable();
374
375 mockPreparedStatement.setObject(1, new Integer (1), Types.NUMERIC);
376 ctrlPreparedStatement.setVoidCallable();
377 mockPreparedStatement.setObject(2, new Integer (1), Types.NUMERIC);
378 ctrlPreparedStatement.setVoidCallable();
379 mockPreparedStatement.executeQuery();
380 ctrlPreparedStatement.setReturnValue(mockResultSet);
381 mockPreparedStatement.getWarnings();
382 ctrlPreparedStatement.setReturnValue(null);
383 mockPreparedStatement.close();
384 ctrlPreparedStatement.setVoidCallable();
385
386 mockConnection.prepareStatement(SELECT_ID_WHERE);
387 ctrlConnection.setReturnValue(mockPreparedStatement);
388
389 replay();
390
391 class CustomerQuery extends MappingSqlQuery {
392
393 public CustomerQuery(DataSource ds) {
394 super(ds, SELECT_ID_WHERE);
395 declareParameter(new SqlParameter(Types.NUMERIC));
396 declareParameter(new SqlParameter(Types.NUMERIC));
397 compile();
398 }
399
400 protected Object mapRow(ResultSet rs, int rownum)
401 throws SQLException {
402 Customer cust = new Customer();
403 cust.setId(rs.getInt(COLUMN_NAMES[0]));
404 cust.setForename(rs.getString(COLUMN_NAMES[1]));
405 return cust;
406 }
407
408 public Customer findCustomer(int id, int otherNum) {
409 return (Customer) findObject(id, otherNum);
410 }
411 }
412 CustomerQuery query = new CustomerQuery(mockDataSource);
413 Customer cust = query.findCustomer(1, 1);
414
415 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
416 assertTrue(
417 "Customer forename was assigned correctly",
418 cust.getForename().equals("rod"));
419 }
420
421 public void testFindCustomerString() throws SQLException {
422 mockResultSet.next();
423 ctrlResultSet.setReturnValue(true);
424 mockResultSet.getInt("id");
425 ctrlResultSet.setReturnValue(1);
426 mockResultSet.getString("forename");
427 ctrlResultSet.setReturnValue("rod");
428 mockResultSet.next();
429 ctrlResultSet.setReturnValue(false);
430 mockResultSet.close();
431 ctrlResultSet.setVoidCallable();
432
433 mockPreparedStatement.setString(1, "rod");
434 ctrlPreparedStatement.setVoidCallable();
435 mockPreparedStatement.executeQuery();
436 ctrlPreparedStatement.setReturnValue(mockResultSet);
437 mockPreparedStatement.getWarnings();
438 ctrlPreparedStatement.setReturnValue(null);
439 mockPreparedStatement.close();
440 ctrlPreparedStatement.setVoidCallable();
441
442 mockConnection.prepareStatement(SELECT_ID_FORENAME_WHERE);
443 ctrlConnection.setReturnValue(mockPreparedStatement);
444
445 replay();
446
447 class CustomerQuery extends MappingSqlQuery {
448
449 public CustomerQuery(DataSource ds) {
450 super(ds, SELECT_ID_FORENAME_WHERE);
451 declareParameter(new SqlParameter(Types.VARCHAR));
452 compile();
453 }
454
455 protected Object mapRow(ResultSet rs, int rownum)
456 throws SQLException {
457 Customer cust = new Customer();
458 cust.setId(rs.getInt(COLUMN_NAMES[0]));
459 cust.setForename(rs.getString(COLUMN_NAMES[1]));
460 return cust;
461 }
462
463 public Customer findCustomer(String id) {
464 return (Customer) findObject(id);
465 }
466 }
467 CustomerQuery query = new CustomerQuery(mockDataSource);
468 Customer cust = query.findCustomer("rod");
469
470 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
471 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
472 }
473
474 public void testFindCustomerMixed() throws SQLException {
475 MockControl ctrlResultSet2;
476 ResultSet mockResultSet2;
477 MockControl ctrlPreparedStatement2;
478 PreparedStatement mockPreparedStatement2;
479
480 mockResultSet.next();
481 ctrlResultSet.setReturnValue(true);
482 mockResultSet.getInt("id");
483 ctrlResultSet.setReturnValue(1);
484 mockResultSet.getString("forename");
485 ctrlResultSet.setReturnValue("rod");
486 mockResultSet.next();
487 ctrlResultSet.setReturnValue(false);
488 mockResultSet.close();
489 ctrlResultSet.setVoidCallable();
490
491 mockPreparedStatement.setObject(1, new Integer (1), Types.INTEGER);
492 ctrlPreparedStatement.setVoidCallable();
493 mockPreparedStatement.setString(2, "rod");
494 ctrlPreparedStatement.setVoidCallable();
495 mockPreparedStatement.executeQuery();
496 ctrlPreparedStatement.setReturnValue(mockResultSet);
497 mockPreparedStatement.getWarnings();
498 ctrlPreparedStatement.setReturnValue(null);
499 mockPreparedStatement.close();
500 ctrlPreparedStatement.setVoidCallable();
501
502 ctrlResultSet2 = MockControl.createControl(ResultSet .class);
503 mockResultSet2 = (ResultSet ) ctrlResultSet2.getMock();
504 mockResultSet2.next();
505 ctrlResultSet2.setReturnValue(false);
506 mockResultSet2.close();
507 ctrlResultSet2.setVoidCallable();
508
509 ctrlPreparedStatement2 = MockControl.createControl(PreparedStatement .class);
510 mockPreparedStatement2 = (PreparedStatement ) ctrlPreparedStatement2.getMock();
511 mockPreparedStatement2.setObject(1, new Integer (1), Types.INTEGER);
512 ctrlPreparedStatement2.setVoidCallable();
513 mockPreparedStatement2.setString(2, "Roger");
514 ctrlPreparedStatement2.setVoidCallable();
515 mockPreparedStatement2.executeQuery();
516 ctrlPreparedStatement2.setReturnValue(mockResultSet2);
517 mockPreparedStatement2.getWarnings();
518 ctrlPreparedStatement2.setReturnValue(null);
519 mockPreparedStatement2.close();
520 ctrlPreparedStatement2.setVoidCallable();
521
522 mockConnection.prepareStatement(SELECT_ID_WHERE);
523 ctrlConnection.setReturnValue(mockPreparedStatement);
524 mockConnection.prepareStatement(SELECT_ID_WHERE);
525 ctrlConnection.setReturnValue(mockPreparedStatement2);
526
527 ctrlResultSet2.replay();
528 ctrlPreparedStatement2.replay();
529 replay();
530
531 class CustomerQuery extends MappingSqlQuery {
532
533 public CustomerQuery(DataSource ds) {
534 super(ds, SELECT_ID_WHERE);
535 declareParameter(
536 new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
537 declareParameter(
538 new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
539 compile();
540 }
541
542 protected Object mapRow(ResultSet rs, int rownum)
543 throws SQLException {
544 Customer cust = new Customer();
545 cust.setId(rs.getInt(COLUMN_NAMES[0]));
546 cust.setForename(rs.getString(COLUMN_NAMES[1]));
547 return cust;
548 }
549
550 public Customer findCustomer(int id, String name) {
551 return (Customer) findObject(
552 new Object [] { new Integer (id), name });
553 }
554 }
555 CustomerQuery query = new CustomerQuery(mockDataSource);
556
557 Customer cust1 = query.findCustomer(1, "rod");
558 assertTrue("Found customer", cust1 != null);
559 assertTrue("Customer id was assigned correctly", cust1.id == 1);
560
561 Customer cust2 = query.findCustomer(1, "Roger");
562 assertTrue("No customer found", cust2 == null);
563 }
564
565 public void testFindTooManyCustomers() throws SQLException {
566 mockResultSet.next();
567 ctrlResultSet.setReturnValue(true);
568 mockResultSet.getInt("id");
569 ctrlResultSet.setReturnValue(1);
570 mockResultSet.getString("forename");
571 ctrlResultSet.setReturnValue("rod");
572 mockResultSet.next();
573 ctrlResultSet.setReturnValue(true);
574 mockResultSet.getInt("id");
575 ctrlResultSet.setReturnValue(2);
576 mockResultSet.getString("forename");
577 ctrlResultSet.setReturnValue("rod");
578 mockResultSet.next();
579 ctrlResultSet.setReturnValue(false);
580 mockResultSet.close();
581 ctrlResultSet.setVoidCallable();
582
583 mockPreparedStatement.setString(1, "rod");
584 ctrlPreparedStatement.setVoidCallable();
585 mockPreparedStatement.executeQuery();
586 ctrlPreparedStatement.setReturnValue(mockResultSet);
587 mockPreparedStatement.getWarnings();
588 ctrlPreparedStatement.setReturnValue(null);
589 mockPreparedStatement.close();
590 ctrlPreparedStatement.setVoidCallable();
591
592 mockConnection.prepareStatement(SELECT_ID_FORENAME_WHERE);
593 ctrlConnection.setReturnValue(mockPreparedStatement);
594
595 replay();
596
597 class CustomerQuery extends MappingSqlQuery {
598
599 public CustomerQuery(DataSource ds) {
600 super(ds, SELECT_ID_FORENAME_WHERE);
601 declareParameter(new SqlParameter(Types.VARCHAR));
602 compile();
603 }
604
605 protected Object mapRow(ResultSet rs, int rownum)
606 throws SQLException {
607 Customer cust = new Customer();
608 cust.setId(rs.getInt(COLUMN_NAMES[0]));
609 cust.setForename(rs.getString(COLUMN_NAMES[1]));
610 return cust;
611 }
612
613 public Customer findCustomer(String id) {
614 return (Customer) findObject(id);
615 }
616 }
617 CustomerQuery query = new CustomerQuery(mockDataSource);
618 try {
619 Customer cust = query.findCustomer("rod");
620 fail("Should fail if more than one row found");
621 }
622 catch (IncorrectResultSizeDataAccessException ex) {
623 }
625 }
626
627 public void testListCustomersIntInt() throws SQLException {
628 mockResultSet.next();
629 ctrlResultSet.setReturnValue(true);
630 mockResultSet.getInt("id");
631 ctrlResultSet.setReturnValue(1);
632 mockResultSet.getString("forename");
633 ctrlResultSet.setReturnValue("rod");
634 mockResultSet.next();
635 ctrlResultSet.setReturnValue(true);
636 mockResultSet.getInt("id");
637 ctrlResultSet.setReturnValue(2);
638 mockResultSet.getString("forename");
639 ctrlResultSet.setReturnValue("dave");
640 mockResultSet.next();
641 ctrlResultSet.setReturnValue(false);
642 mockResultSet.close();
643 ctrlResultSet.setVoidCallable();
644
645 mockPreparedStatement.setObject(1, new Integer (1), Types.NUMERIC);
646 ctrlPreparedStatement.setVoidCallable();
647 mockPreparedStatement.setObject(2, new Integer (1), Types.NUMERIC);
648 ctrlPreparedStatement.setVoidCallable();
649 mockPreparedStatement.executeQuery();
650 ctrlPreparedStatement.setReturnValue(mockResultSet);
651 mockPreparedStatement.getWarnings();
652 ctrlPreparedStatement.setReturnValue(null);
653 mockPreparedStatement.close();
654 ctrlPreparedStatement.setVoidCallable();
655
656 mockConnection.prepareStatement(SELECT_ID_WHERE);
657 ctrlConnection.setReturnValue(mockPreparedStatement);
658
659 replay();
660
661 class CustomerQuery extends MappingSqlQuery {
662
663 public CustomerQuery(DataSource ds) {
664 super(ds, SELECT_ID_WHERE);
665 declareParameter(new SqlParameter(Types.NUMERIC));
666 declareParameter(new SqlParameter(Types.NUMERIC));
667 compile();
668 }
669
670 protected Object mapRow(ResultSet rs, int rownum)
671 throws SQLException {
672 Customer cust = new Customer();
673 cust.setId(rs.getInt(COLUMN_NAMES[0]));
674 cust.setForename(rs.getString(COLUMN_NAMES[1]));
675 return cust;
676 }
677
678 }
679 CustomerQuery query = new CustomerQuery(mockDataSource);
680
681 List list = query.execute(1, 1);
682 assertTrue("2 results in list", list.size() == 2);
683 for (Iterator itr = list.iterator(); itr.hasNext();) {
684 Customer cust = (Customer) itr.next();
685 }
686 }
687
688 public void testListCustomersString() throws SQLException {
689 mockResultSet.next();
690 ctrlResultSet.setReturnValue(true);
691 mockResultSet.getInt("id");
692 ctrlResultSet.setReturnValue(1);
693 mockResultSet.getString("forename");
694 ctrlResultSet.setReturnValue("rod");
695 mockResultSet.next();
696 ctrlResultSet.setReturnValue(true);
697 mockResultSet.getInt("id");
698 ctrlResultSet.setReturnValue(2);
699 mockResultSet.getString("forename");
700 ctrlResultSet.setReturnValue("dave");
701 mockResultSet.next();
702 ctrlResultSet.setReturnValue(false);
703 mockResultSet.close();
704 ctrlResultSet.setVoidCallable();
705
706 mockPreparedStatement.setString(1, "one");
707 ctrlPreparedStatement.setVoidCallable();
708 mockPreparedStatement.executeQuery();
709 ctrlPreparedStatement.setReturnValue(mockResultSet);
710 mockPreparedStatement.getWarnings();
711 ctrlPreparedStatement.setReturnValue(null);
712 mockPreparedStatement.close();
713 ctrlPreparedStatement.setVoidCallable();
714
715 mockConnection.prepareStatement(SELECT_ID_FORENAME_WHERE);
716 ctrlConnection.setReturnValue(mockPreparedStatement);
717
718 replay();
719
720 class CustomerQuery extends MappingSqlQuery {
721
722 public CustomerQuery(DataSource ds) {
723 super(ds, SELECT_ID_FORENAME_WHERE);
724 declareParameter(new SqlParameter(Types.VARCHAR));
725 compile();
726 }
727
728 protected Object mapRow(ResultSet rs, int rownum)
729 throws SQLException {
730 Customer cust = new Customer();
731 cust.setId(rs.getInt(COLUMN_NAMES[0]));
732 cust.setForename(rs.getString(COLUMN_NAMES[1]));
733 return cust;
734 }
735
736 }
737 CustomerQuery query = new CustomerQuery(mockDataSource);
738
739 List list = query.execute("one");
740 assertTrue("2 results in list", list.size() == 2);
741 for (Iterator itr = list.iterator(); itr.hasNext();) {
742 Customer cust = (Customer) itr.next();
743 }
744 }
745
746 public void testFancyCustomerQuery() throws SQLException {
747 mockResultSet.next();
748 ctrlResultSet.setReturnValue(true);
749 mockResultSet.getInt("id");
750 ctrlResultSet.setReturnValue(1);
751 mockResultSet.getString("forename");
752 ctrlResultSet.setReturnValue("rod");
753 mockResultSet.next();
754 ctrlResultSet.setReturnValue(false);
755 mockResultSet.close();
756 ctrlResultSet.setVoidCallable();
757
758 mockPreparedStatement.setObject(1, new Integer (1), Types.NUMERIC);
759 ctrlPreparedStatement.setVoidCallable();
760 mockPreparedStatement.executeQuery();
761 ctrlPreparedStatement.setReturnValue(mockResultSet);
762 mockPreparedStatement.getWarnings();
763 ctrlPreparedStatement.setReturnValue(null);
764 mockPreparedStatement.close();
765 ctrlPreparedStatement.setVoidCallable();
766
767 mockConnection.prepareStatement(
768 SELECT_ID_FORENAME_WHERE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
769 ctrlConnection.setReturnValue(mockPreparedStatement);
770
771 replay();
772
773 class CustomerQuery extends MappingSqlQuery {
774
775 public CustomerQuery(DataSource ds) {
776 super(ds, SELECT_ID_FORENAME_WHERE);
777 setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
778 declareParameter(new SqlParameter(Types.NUMERIC));
779 compile();
780 }
781
782 protected Object mapRow(ResultSet rs, int rownum)
783 throws SQLException {
784 Customer cust = new Customer();
785 cust.setId(rs.getInt(COLUMN_NAMES[0]));
786 cust.setForename(rs.getString(COLUMN_NAMES[1]));
787 return cust;
788 }
789
790 public Customer findCustomer(int id) {
791 return (Customer) findObject(id);
792 }
793 }
794 CustomerQuery query = new CustomerQuery(mockDataSource);
795 Customer cust = query.findCustomer(1);
796
797 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
798 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
799 }
800
801 public void testUpdateCustomers() throws SQLException {
802 mockResultSet.next();
803 ctrlResultSet.setReturnValue(true);
804 mockResultSet.getInt("id");
805 ctrlResultSet.setReturnValue(1);
806 mockResultSet.updateString(2, "Rod");
807 ctrlResultSet.setVoidCallable();
808 mockResultSet.updateRow();
809 ctrlResultSet.setVoidCallable();
810 mockResultSet.next();
811 ctrlResultSet.setReturnValue(true);
812 mockResultSet.getInt("id");
813 ctrlResultSet.setReturnValue(2);
814 mockResultSet.updateString(2, "Thomas");
815 ctrlResultSet.setVoidCallable();
816 mockResultSet.updateRow();
817 ctrlResultSet.setVoidCallable();
818 mockResultSet.next();
819 ctrlResultSet.setReturnValue(false);
820 mockResultSet.close();
821 ctrlResultSet.setVoidCallable();
822
823 mockPreparedStatement.setObject(1, new Integer (2), Types.NUMERIC);
824 ctrlPreparedStatement.setVoidCallable();
825 mockPreparedStatement.executeQuery();
826 ctrlPreparedStatement.setReturnValue(mockResultSet);
827 mockPreparedStatement.getWarnings();
828 ctrlPreparedStatement.setReturnValue(null);
829 mockPreparedStatement.close();
830 ctrlPreparedStatement.setVoidCallable();
831
832 mockConnection.prepareStatement(
833 SELECT_ID_FORENAME_WHERE_ID, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
834 ctrlConnection.setReturnValue(mockPreparedStatement);
835
836 replay();
837
838 class CustomerUpdateQuery extends UpdatableSqlQuery {
839
840 public CustomerUpdateQuery(DataSource ds) {
841 super(ds, SELECT_ID_FORENAME_WHERE_ID);
842 declareParameter(new SqlParameter(Types.NUMERIC));
843 compile();
844 }
845
846 protected Object updateRow(ResultSet rs, int rownum, Map context)
847 throws SQLException {
848 rs.updateString(2, "" + context.get(new Integer (rs.getInt(COLUMN_NAMES[0]))));
849 return null;
850 }
851 }
852 CustomerUpdateQuery query = new CustomerUpdateQuery(mockDataSource);
853 Map values = new HashMap (2);
854 values.put(new Integer (1), "Rod");
855 values.put(new Integer (2), "Thomas");
856 List customers = query.execute(2, values);
857 }
858
859
860 private static class StringQuery extends MappingSqlQuery {
861
862 public StringQuery(DataSource ds, String sql) {
863 super(ds, sql);
864 compile();
865 }
866
867 protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
868 return rs.getString(1);
869 }
870
871 public String [] run() {
872 List list = execute();
873 String [] results = (String []) list.toArray(new String [list.size()]);
874 return results;
875 }
876 }
877
878
879 private static class Customer {
880
881 private int id;
882 private String forename;
883
884 public int getId() {
885 return id;
886 }
887
888 public void setId(int id) {
889 this.id = id;
890 }
891
892 public String getForename() {
893 return forename;
894 }
895
896 public void setForename(String forename) {
897 this.forename = forename;
898 }
899
900 public String toString() {
901 return "Customer: id=" + id + "; forename=" + forename;
902 }
903 }
904
905 }
906 |
Popular Tags |
Java API By Example, From Geeks To Geeks.