1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.springframework.jdbc.object;
18
19 import java.sql.Connection;
20 import java.sql.PreparedStatement;
21 import java.sql.ResultSet;
22 import java.sql.SQLException;
23 import java.sql.Types;
24 import java.util.ArrayList;
25 import java.util.Arrays;
26 import java.util.Collections;
27 import java.util.HashMap;
28 import java.util.List;
29 import java.util.Map;
30 import javax.sql.DataSource;
31
32 import org.junit.Before;
33 import org.junit.Rule;
34 import org.junit.Test;
35 import org.junit.rules.ExpectedException;
36
37 import org.springframework.dao.IncorrectResultSizeDataAccessException;
38 import org.springframework.dao.InvalidDataAccessApiUsageException;
39 import org.springframework.jdbc.Customer;
40 import org.springframework.jdbc.core.SqlParameter;
41
42 import static org.hamcrest.Matchers.*;
43 import static org.junit.Assert.*;
44 import static org.mockito.BDDMockito.*;
45
46
47
48
49
50
51 public class SqlQueryTests {
52
53
54 private static final String SELECT_ID =
55 "select id from custmr";
56 private static final String SELECT_ID_WHERE =
57 "select id from custmr where forename = ? and id = ?";
58 private static final String SELECT_FORENAME =
59 "select forename from custmr";
60 private static final String SELECT_FORENAME_EMPTY =
61 "select forename from custmr WHERE 1 = 2";
62 private static final String SELECT_ID_FORENAME_WHERE =
63 "select id, forename from prefix:custmr where forename = ?";
64 private static final String SELECT_ID_FORENAME_NAMED_PARAMETERS =
65 "select id, forename from custmr where id = :id and country = :country";
66 private static final String SELECT_ID_FORENAME_NAMED_PARAMETERS_PARSED =
67 "select id, forename from custmr where id = ? and country = ?";
68 private static final String SELECT_ID_FORENAME_WHERE_ID_IN_LIST_1 =
69 "select id, forename from custmr where id in (?, ?)";
70 private static final String SELECT_ID_FORENAME_WHERE_ID_IN_LIST_2 =
71 "select id, forename from custmr where id in (:ids)";
72 private static final String SELECT_ID_FORENAME_WHERE_ID_REUSED_1 =
73 "select id, forename from custmr where id = ? or id = ?)";
74 private static final String SELECT_ID_FORENAME_WHERE_ID_REUSED_2 =
75 "select id, forename from custmr where id = :id1 or id = :id1)";
76 private static final String SELECT_ID_FORENAME_WHERE_ID =
77 "select id, forename from custmr where id <= ?";
78
79 private static final String[] COLUMN_NAMES = new String[] {"id", "forename"};
80 private static final int[] COLUMN_TYPES = new int[] {Types.INTEGER, Types.VARCHAR};
81
82 @Rule
83 public ExpectedException thrown = ExpectedException.none();
84
85 private Connection connection;
86 private DataSource dataSource;
87 private PreparedStatement preparedStatement;
88 private ResultSet resultSet;
89
90
91 @Before
92 public void setUp() throws Exception {
93 this.connection = mock(Connection.class);
94 this.dataSource = mock(DataSource.class);
95 this.preparedStatement = mock(PreparedStatement.class);
96 this.resultSet = mock(ResultSet.class);
97 given(this.dataSource.getConnection()).willReturn(this.connection);
98 given(this.connection.prepareStatement(anyString())).willReturn(this.preparedStatement);
99 given(preparedStatement.executeQuery()).willReturn(resultSet);
100 }
101
102 @Test
103 public void testQueryWithoutParams() throws SQLException {
104 given(resultSet.next()).willReturn(true, false);
105 given(resultSet.getInt(1)).willReturn(1);
106
107 SqlQuery<Integer> query = new MappingSqlQueryWithParameters<Integer>() {
108 @Override
109 protected Integer mapRow(ResultSet rs, int rownum, Object[] params, Map<? ,?> context)
110 throws SQLException {
111 assertTrue("params were null", params == null);
112 assertTrue("context was null", context == null);
113 return rs.getInt(1);
114 }
115 };
116 query.setDataSource(dataSource);
117 query.setSql(SELECT_ID);
118 query.compile();
119 List<Integer> list = query.execute();
120
121 assertThat(list, is(equalTo(Arrays.asList(1))));
122 verify(connection).prepareStatement(SELECT_ID);
123 verify(resultSet).close();
124 verify(preparedStatement).close();
125 }
126
127 @Test
128 public void testQueryWithoutEnoughParams() {
129 MappingSqlQuery<Integer> query = new MappingSqlQuery<Integer>() {
130 @Override
131 protected Integer mapRow(ResultSet rs, int rownum) throws SQLException {
132 return rs.getInt(1);
133 }
134 };
135 query.setDataSource(dataSource);
136 query.setSql(SELECT_ID_WHERE);
137 query.declareParameter(new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
138 query.declareParameter(new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
139 query.compile();
140
141 thrown.expect(InvalidDataAccessApiUsageException.class);
142 query.execute();
143 }
144
145 @Test
146 public void testQueryWithMissingMapParams() {
147 MappingSqlQuery<Integer> query = new MappingSqlQuery<Integer>() {
148 @Override
149 protected Integer mapRow(ResultSet rs, int rownum) throws SQLException {
150 return rs.getInt(1);
151 }
152 };
153 query.setDataSource(dataSource);
154 query.setSql(SELECT_ID_WHERE);
155 query.declareParameter(new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
156 query.declareParameter(new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
157 query.compile();
158
159 thrown.expect(InvalidDataAccessApiUsageException.class);
160 query.executeByNamedParam(Collections.singletonMap(COLUMN_NAMES[0], "value"));
161 }
162
163 @Test
164 public void testStringQueryWithResults() throws Exception {
165 String[] dbResults = new String[] { "alpha", "beta", "charlie" };
166 given(resultSet.next()).willReturn(true, true, true, false);
167 given(resultSet.getString(1)).willReturn(dbResults[0], dbResults[1], dbResults[2]);
168 StringQuery query = new StringQuery(dataSource, SELECT_FORENAME);
169 query.setRowsExpected(3);
170 String[] results = query.run();
171 assertThat(results, is(equalTo(dbResults)));
172 verify(connection).prepareStatement(SELECT_FORENAME);
173 verify(resultSet).close();
174 verify(preparedStatement).close();
175 verify(connection).close();
176 }
177
178 @Test
179 public void testStringQueryWithoutResults() throws SQLException {
180 given(resultSet.next()).willReturn(false);
181 StringQuery query = new StringQuery(dataSource, SELECT_FORENAME_EMPTY);
182 String[] results = query.run();
183 assertThat(results, is(equalTo(new String[0])));
184 verify(connection).prepareStatement(SELECT_FORENAME_EMPTY);
185 verify(resultSet).close();
186 verify(preparedStatement).close();
187 verify(connection).close();
188 }
189
190 @Test
191 public void testFindCustomerIntInt() throws SQLException {
192 given(resultSet.next()).willReturn(true, false);
193 given(resultSet.getInt("id")).willReturn(1);
194 given(resultSet.getString("forename")).willReturn("rod");
195
196 class CustomerQuery extends MappingSqlQuery<Customer> {
197
198 public CustomerQuery(DataSource ds) {
199 super(ds, SELECT_ID_WHERE);
200 declareParameter(new SqlParameter(Types.NUMERIC));
201 declareParameter(new SqlParameter(Types.NUMERIC));
202 compile();
203 }
204
205 @Override
206 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
207 Customer cust = new Customer();
208 cust.setId(rs.getInt(COLUMN_NAMES[0]));
209 cust.setForename(rs.getString(COLUMN_NAMES[1]));
210 return cust;
211 }
212
213 public Customer findCustomer(int id, int otherNum) {
214 return findObject(id, otherNum);
215 }
216 }
217
218 CustomerQuery query = new CustomerQuery(dataSource);
219 Customer cust = query.findCustomer(1, 1);
220
221 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
222 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
223 verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
224 verify(preparedStatement).setObject(2, 1, Types.NUMERIC);
225 verify(connection).prepareStatement(SELECT_ID_WHERE);
226 verify(resultSet).close();
227 verify(preparedStatement).close();
228 verify(connection).close();
229 }
230
231 @Test
232 public void testFindCustomerString() throws SQLException {
233 given(resultSet.next()).willReturn(true, false);
234 given(resultSet.getInt("id")).willReturn(1);
235 given(resultSet.getString("forename")).willReturn("rod");
236
237 class CustomerQuery extends MappingSqlQuery<Customer> {
238
239 public CustomerQuery(DataSource ds) {
240 super(ds, SELECT_ID_FORENAME_WHERE);
241 declareParameter(new SqlParameter(Types.VARCHAR));
242 compile();
243 }
244
245 @Override
246 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
247 Customer cust = new Customer();
248 cust.setId(rs.getInt(COLUMN_NAMES[0]));
249 cust.setForename(rs.getString(COLUMN_NAMES[1]));
250 return cust;
251 }
252
253 public Customer findCustomer(String id) {
254 return findObject(id);
255 }
256 }
257
258 CustomerQuery query = new CustomerQuery(dataSource);
259 Customer cust = query.findCustomer("rod");
260
261 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
262 assertTrue("Customer forename was assigned correctly",
263 cust.getForename().equals("rod"));
264 verify(preparedStatement).setString(1, "rod");
265 verify(connection).prepareStatement(SELECT_ID_FORENAME_WHERE);
266 verify(resultSet).close();
267 verify(preparedStatement).close();
268 verify(connection).close();
269 }
270
271 @Test
272 public void testFindCustomerMixed() throws SQLException {
273 reset(connection);
274 PreparedStatement preparedStatement2 = mock(PreparedStatement.class);
275 ResultSet resultSet2 = mock(ResultSet.class);
276 given(preparedStatement2.executeQuery()).willReturn(resultSet2);
277 given(resultSet.next()).willReturn(true, false);
278 given(resultSet.getInt("id")).willReturn(1);
279 given(resultSet.getString("forename")).willReturn("rod");
280 given(resultSet2.next()).willReturn(false);
281 given(connection.prepareStatement(SELECT_ID_WHERE)).willReturn(preparedStatement, preparedStatement2);
282
283 class CustomerQuery extends MappingSqlQuery<Customer> {
284
285 public CustomerQuery(DataSource ds) {
286 super(ds, SELECT_ID_WHERE);
287 declareParameter(new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
288 declareParameter(new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
289 compile();
290 }
291
292 @Override
293 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
294 Customer cust = new Customer();
295 cust.setId(rs.getInt(COLUMN_NAMES[0]));
296 cust.setForename(rs.getString(COLUMN_NAMES[1]));
297 return cust;
298 }
299
300 public Customer findCustomer(int id, String name) {
301 return findObject(new Object[] { id, name });
302 }
303 }
304
305 CustomerQuery query = new CustomerQuery(dataSource);
306
307 Customer cust1 = query.findCustomer(1, "rod");
308 assertTrue("Found customer", cust1 != null);
309 assertTrue("Customer id was assigned correctly", cust1.getId() == 1);
310
311 Customer cust2 = query.findCustomer(1, "Roger");
312 assertTrue("No customer found", cust2 == null);
313
314 verify(preparedStatement).setObject(1, 1, Types.INTEGER);
315 verify(preparedStatement).setString(2, "rod");
316 verify(preparedStatement2).setObject(1, 1, Types.INTEGER);
317 verify(preparedStatement2).setString(2, "Roger");
318 verify(resultSet).close();
319 verify(resultSet2).close();
320 verify(preparedStatement).close();
321 verify(preparedStatement2).close();
322 verify(connection, times(2)).close();
323 }
324
325 @Test
326 public void testFindTooManyCustomers() throws SQLException {
327 given(resultSet.next()).willReturn(true, true, false);
328 given(resultSet.getInt("id")).willReturn(1, 2);
329 given(resultSet.getString("forename")).willReturn("rod", "rod");
330
331 class CustomerQuery extends MappingSqlQuery<Customer> {
332
333 public CustomerQuery(DataSource ds) {
334 super(ds, SELECT_ID_FORENAME_WHERE);
335 declareParameter(new SqlParameter(Types.VARCHAR));
336 compile();
337 }
338
339 @Override
340 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
341 Customer cust = new Customer();
342 cust.setId(rs.getInt(COLUMN_NAMES[0]));
343 cust.setForename(rs.getString(COLUMN_NAMES[1]));
344 return cust;
345 }
346
347 public Customer findCustomer(String id) {
348 return findObject(id);
349 }
350 }
351
352 CustomerQuery query = new CustomerQuery(dataSource);
353 thrown.expect(IncorrectResultSizeDataAccessException.class);
354 try {
355 query.findCustomer("rod");
356 }
357 finally {
358 verify(preparedStatement).setString(1, "rod");
359 verify(connection).prepareStatement(SELECT_ID_FORENAME_WHERE);
360 verify(resultSet).close();
361 verify(preparedStatement).close();
362 verify(connection).close();
363 }
364 }
365
366 @Test
367 public void testListCustomersIntInt() throws SQLException {
368 given(resultSet.next()).willReturn(true, true, false);
369 given(resultSet.getInt("id")).willReturn(1, 2);
370 given(resultSet.getString("forename")).willReturn("rod", "dave");
371
372 class CustomerQuery extends MappingSqlQuery<Customer> {
373
374 public CustomerQuery(DataSource ds) {
375 super(ds, SELECT_ID_WHERE);
376 declareParameter(new SqlParameter(Types.NUMERIC));
377 declareParameter(new SqlParameter(Types.NUMERIC));
378 compile();
379 }
380
381 @Override
382 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
383 Customer cust = new Customer();
384 cust.setId(rs.getInt(COLUMN_NAMES[0]));
385 cust.setForename(rs.getString(COLUMN_NAMES[1]));
386 return cust;
387 }
388 }
389
390 CustomerQuery query = new CustomerQuery(dataSource);
391 List<Customer> list = query.execute(1, 1);
392 assertTrue("2 results in list", list.size() == 2);
393 assertThat(list.get(0).getForename(), is("rod"));
394 assertThat(list.get(1).getForename(), is("dave"));
395 verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
396 verify(preparedStatement).setObject(2, 1, Types.NUMERIC);
397 verify(connection).prepareStatement(SELECT_ID_WHERE);
398 verify(resultSet).close();
399 verify(preparedStatement).close();
400 verify(connection).close();
401 }
402
403 @Test
404 public void testListCustomersString() throws SQLException {
405 given(resultSet.next()).willReturn(true, true, false);
406 given(resultSet.getInt("id")).willReturn(1, 2);
407 given(resultSet.getString("forename")).willReturn("rod", "dave");
408
409 class CustomerQuery extends MappingSqlQuery<Customer> {
410
411 public CustomerQuery(DataSource ds) {
412 super(ds, SELECT_ID_FORENAME_WHERE);
413 declareParameter(new SqlParameter(Types.VARCHAR));
414 compile();
415 }
416
417 @Override
418 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
419 Customer cust = new Customer();
420 cust.setId(rs.getInt(COLUMN_NAMES[0]));
421 cust.setForename(rs.getString(COLUMN_NAMES[1]));
422 return cust;
423 }
424 }
425
426 CustomerQuery query = new CustomerQuery(dataSource);
427 List<Customer> list = query.execute("one");
428 assertTrue("2 results in list", list.size() == 2);
429 assertThat(list.get(0).getForename(), is("rod"));
430 assertThat(list.get(1).getForename(), is("dave"));
431 verify(preparedStatement).setString(1, "one");
432 verify(connection).prepareStatement(SELECT_ID_FORENAME_WHERE);
433 verify(resultSet).close();
434 verify(preparedStatement).close();
435 verify(connection).close();
436 }
437
438 @Test
439 public void testFancyCustomerQuery() throws SQLException {
440 given(resultSet.next()).willReturn(true, false);
441 given(resultSet.getInt("id")).willReturn(1);
442 given(resultSet.getString("forename")).willReturn("rod");
443
444 given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE,
445 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)
446 ).willReturn(preparedStatement);
447
448 class CustomerQuery extends MappingSqlQuery<Customer> {
449
450 public CustomerQuery(DataSource ds) {
451 super(ds, SELECT_ID_FORENAME_WHERE);
452 setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
453 declareParameter(new SqlParameter(Types.NUMERIC));
454 compile();
455 }
456
457 @Override
458 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
459 Customer cust = new Customer();
460 cust.setId(rs.getInt(COLUMN_NAMES[0]));
461 cust.setForename(rs.getString(COLUMN_NAMES[1]));
462 return cust;
463 }
464
465 public Customer findCustomer(int id) {
466 return findObject(id);
467 }
468 }
469
470 CustomerQuery query = new CustomerQuery(dataSource);
471 Customer cust = query.findCustomer(1);
472 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
473 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
474 verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
475 verify(resultSet).close();
476 verify(preparedStatement).close();
477 verify(connection).close();
478 }
479
480 @Test
481 public void testUnnamedParameterDeclarationWithNamedParameterQuery()
482 throws SQLException {
483 class CustomerQuery extends MappingSqlQuery<Customer> {
484
485 public CustomerQuery(DataSource ds) {
486 super(ds, SELECT_ID_FORENAME_WHERE);
487 setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
488 declareParameter(new SqlParameter(Types.NUMERIC));
489 compile();
490 }
491
492 @Override
493 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
494 Customer cust = new Customer();
495 cust.setId(rs.getInt(COLUMN_NAMES[0]));
496 cust.setForename(rs.getString(COLUMN_NAMES[1]));
497 return cust;
498 }
499
500 public Customer findCustomer(int id) {
501 Map<String, Integer> params = new HashMap<String, Integer>();
502 params.put("id", id);
503 return executeByNamedParam(params).get(0);
504 }
505 }
506
507
508 CustomerQuery query = new CustomerQuery(dataSource);
509 thrown.expect(InvalidDataAccessApiUsageException.class);
510 query.findCustomer(1);
511 }
512
513 @Test
514 public void testNamedParameterCustomerQueryWithUnnamedDeclarations()
515 throws SQLException {
516 doTestNamedParameterCustomerQuery(false);
517 }
518
519 @Test
520 public void testNamedParameterCustomerQueryWithNamedDeclarations()
521 throws SQLException {
522 doTestNamedParameterCustomerQuery(true);
523 }
524
525 private void doTestNamedParameterCustomerQuery(final boolean namedDeclarations)
526 throws SQLException {
527 given(resultSet.next()).willReturn(true, false);
528 given(resultSet.getInt("id")).willReturn(1);
529 given(resultSet.getString("forename")).willReturn("rod");
530 given(connection.prepareStatement(SELECT_ID_FORENAME_NAMED_PARAMETERS_PARSED,
531 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)
532 ).willReturn(preparedStatement);
533
534 class CustomerQuery extends MappingSqlQuery<Customer> {
535
536 public CustomerQuery(DataSource ds) {
537 super(ds, SELECT_ID_FORENAME_NAMED_PARAMETERS);
538 setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
539 if (namedDeclarations) {
540 declareParameter(new SqlParameter("country", Types.VARCHAR));
541 declareParameter(new SqlParameter("id", Types.NUMERIC));
542 }
543 else {
544 declareParameter(new SqlParameter(Types.NUMERIC));
545 declareParameter(new SqlParameter(Types.VARCHAR));
546 }
547 compile();
548 }
549
550 @Override
551 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
552 Customer cust = new Customer();
553 cust.setId(rs.getInt(COLUMN_NAMES[0]));
554 cust.setForename(rs.getString(COLUMN_NAMES[1]));
555 return cust;
556 }
557
558 public Customer findCustomer(int id, String country) {
559 Map<String, Object> params = new HashMap<String, Object>();
560 params.put("id", id);
561 params.put("country", country);
562 return executeByNamedParam(params).get(0);
563 }
564 }
565
566 CustomerQuery query = new CustomerQuery(dataSource);
567 Customer cust = query.findCustomer(1, "UK");
568 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
569 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
570 verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
571 verify(preparedStatement).setString(2, "UK");
572 verify(resultSet).close();
573 verify(preparedStatement).close();
574 verify(connection).close();
575 }
576
577 @Test
578 public void testNamedParameterInListQuery() throws SQLException {
579 given(resultSet.next()).willReturn(true, true, false);
580 given(resultSet.getInt("id")).willReturn(1, 2);
581 given(resultSet.getString("forename")).willReturn("rod", "juergen");
582
583 given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_IN_LIST_1,
584 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)
585 ).willReturn(preparedStatement);
586
587 class CustomerQuery extends MappingSqlQuery<Customer> {
588
589 public CustomerQuery(DataSource ds) {
590 super(ds, SELECT_ID_FORENAME_WHERE_ID_IN_LIST_2);
591 setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
592 declareParameter(new SqlParameter("ids", Types.NUMERIC));
593 compile();
594 }
595
596 @Override
597 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
598 Customer cust = new Customer();
599 cust.setId(rs.getInt(COLUMN_NAMES[0]));
600 cust.setForename(rs.getString(COLUMN_NAMES[1]));
601 return cust;
602 }
603
604 public List<Customer> findCustomers(List<Integer> ids) {
605 Map<String, Object> params = new HashMap<String, Object>();
606 params.put("ids", ids);
607 return executeByNamedParam(params);
608 }
609 }
610
611 CustomerQuery query = new CustomerQuery(dataSource);
612 List<Integer> ids = new ArrayList<Integer>();
613 ids.add(1);
614 ids.add(2);
615 List<Customer> cust = query.findCustomers(ids);
616
617 assertEquals("We got two customers back", cust.size(), 2);
618 assertEquals("First customer id was assigned correctly", cust.get(0).getId(), 1);
619 assertEquals("First customer forename was assigned correctly", cust.get(0).getForename(), "rod");
620 assertEquals("Second customer id was assigned correctly", cust.get(1).getId(), 2);
621 assertEquals("Second customer forename was assigned correctly", cust.get(1).getForename(), "juergen");
622 verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
623 verify(preparedStatement).setObject(2, 2, Types.NUMERIC);
624 verify(resultSet).close();
625 verify(preparedStatement).close();
626 verify(connection).close();
627 }
628
629 @Test
630 public void testNamedParameterQueryReusingParameter() throws SQLException {
631 given(resultSet.next()).willReturn(true, true, false);
632 given(resultSet.getInt("id")).willReturn(1, 2);
633 given(resultSet.getString("forename")).willReturn("rod", "juergen");
634
635 given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_REUSED_1,
636 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)).willReturn(preparedStatement)
637 ;
638
639 class CustomerQuery extends MappingSqlQuery<Customer> {
640
641 public CustomerQuery(DataSource ds) {
642 super(ds, SELECT_ID_FORENAME_WHERE_ID_REUSED_2);
643 setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
644 declareParameter(new SqlParameter("id1", Types.NUMERIC));
645 compile();
646 }
647
648 @Override
649 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
650 Customer cust = new Customer();
651 cust.setId(rs.getInt(COLUMN_NAMES[0]));
652 cust.setForename(rs.getString(COLUMN_NAMES[1]));
653 return cust;
654 }
655
656 public List<Customer> findCustomers(Integer id) {
657 Map<String, Object> params = new HashMap<String, Object>();
658 params.put("id1", id);
659 return executeByNamedParam(params);
660 }
661 }
662
663 CustomerQuery query = new CustomerQuery(dataSource);
664 List<Customer> cust = query.findCustomers(1);
665
666 assertEquals("We got two customers back", cust.size(), 2);
667 assertEquals("First customer id was assigned correctly", cust.get(0).getId(), 1);
668 assertEquals("First customer forename was assigned correctly", cust.get(0).getForename(), "rod");
669 assertEquals("Second customer id was assigned correctly", cust.get(1).getId(), 2);
670 assertEquals("Second customer forename was assigned correctly", cust.get(1).getForename(), "juergen");
671
672 verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
673 verify(preparedStatement).setObject(2, 1, Types.NUMERIC);
674 verify(resultSet).close();
675 verify(preparedStatement).close();
676 verify(connection).close();
677 }
678
679 @Test
680 public void testNamedParameterUsingInvalidQuestionMarkPlaceHolders()
681 throws SQLException {
682 given(
683 connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_REUSED_1,
684 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)).willReturn(preparedStatement);
685
686 class CustomerQuery extends MappingSqlQuery<Customer> {
687
688 public CustomerQuery(DataSource ds) {
689 super(ds, SELECT_ID_FORENAME_WHERE_ID_REUSED_1);
690 setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
691 declareParameter(new SqlParameter("id1", Types.NUMERIC));
692 compile();
693 }
694
695 @Override
696 protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
697 Customer cust = new Customer();
698 cust.setId(rs.getInt(COLUMN_NAMES[0]));
699 cust.setForename(rs.getString(COLUMN_NAMES[1]));
700 return cust;
701 }
702
703 public List<Customer> findCustomers(Integer id1) {
704 Map<String, Integer> params = new HashMap<String, Integer>();
705 params.put("id1", id1);
706 return executeByNamedParam(params);
707 }
708 }
709
710 CustomerQuery query = new CustomerQuery(dataSource);
711 thrown.expect(InvalidDataAccessApiUsageException.class);
712 query.findCustomers(1);
713 }
714
715 @Test
716 public void testUpdateCustomers() throws SQLException {
717 given(resultSet.next()).willReturn(true, true, false);
718 given(resultSet.getInt("id")).willReturn(1, 2);
719 given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID,
720 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
721 ).willReturn(preparedStatement);
722
723 class CustomerUpdateQuery extends UpdatableSqlQuery<Customer> {
724
725 public CustomerUpdateQuery(DataSource ds) {
726 super(ds, SELECT_ID_FORENAME_WHERE_ID);
727 declareParameter(new SqlParameter(Types.NUMERIC));
728 compile();
729 }
730
731 @Override
732 protected Customer updateRow(ResultSet rs, int rownum, Map<? ,?> context)
733 throws SQLException {
734 rs.updateString(2, "" + context.get(rs.getInt(COLUMN_NAMES[0])));
735 return null;
736 }
737 }
738
739 CustomerUpdateQuery query = new CustomerUpdateQuery(dataSource);
740 Map<Integer, String> values = new HashMap<Integer, String>(2);
741 values.put(1, "Rod");
742 values.put(2, "Thomas");
743 query.execute(2, values);
744 verify(resultSet).updateString(2, "Rod");
745 verify(resultSet).updateString(2, "Thomas");
746 verify(resultSet, times(2)).updateRow();
747 verify(preparedStatement).setObject(1, 2, Types.NUMERIC);
748 verify(resultSet).close();
749 verify(preparedStatement).close();
750 verify(connection).close();
751 }
752
753 private static class StringQuery extends MappingSqlQuery<String> {
754
755 public StringQuery(DataSource ds, String sql) {
756 super(ds, sql);
757 compile();
758 }
759
760 @Override
761 protected String mapRow(ResultSet rs, int rownum) throws SQLException {
762 return rs.getString(1);
763 }
764
765 public String[] run() {
766 List<String> list = execute();
767 String[] results = list.toArray(new String[list.size()]);
768 return results;
769 }
770 }
771
772 }