1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.springframework.jdbc.core.namedparam;
18
19 import java.sql.Connection;
20 import java.sql.DatabaseMetaData;
21 import java.sql.PreparedStatement;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.sql.Types;
25 import java.util.Collections;
26 import java.util.HashMap;
27 import java.util.LinkedList;
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.DataAccessException;
38 import org.springframework.jdbc.Customer;
39 import org.springframework.jdbc.core.JdbcOperations;
40 import org.springframework.jdbc.core.JdbcTemplate;
41 import org.springframework.jdbc.core.PreparedStatementCallback;
42 import org.springframework.jdbc.core.ResultSetExtractor;
43 import org.springframework.jdbc.core.RowCallbackHandler;
44 import org.springframework.jdbc.core.RowMapper;
45 import org.springframework.jdbc.core.SqlParameterValue;
46
47 import static org.junit.Assert.*;
48 import static org.mockito.BDDMockito.*;
49
50
51
52
53
54
55 public class NamedParameterJdbcTemplateTests {
56
57 private static final String SELECT_NAMED_PARAMETERS =
58 "select id, forename from custmr where id = :id and country = :country";
59 private static final String SELECT_NAMED_PARAMETERS_PARSED =
60 "select id, forename from custmr where id = ? and country = ?";
61 private static final String SELECT_NO_PARAMETERS =
62 "select id, forename from custmr";
63
64 private static final String UPDATE_NAMED_PARAMETERS =
65 "update seat_status set booking_id = null where performance_id = :perfId and price_band_id = :priceId";
66 private static final String UPDATE_NAMED_PARAMETERS_PARSED =
67 "update seat_status set booking_id = null where performance_id = ? and price_band_id = ?";
68
69 private static final String[] COLUMN_NAMES = new String[] {"id", "forename"};
70
71 @Rule
72 public ExpectedException thrown = ExpectedException.none();
73
74 private Connection connection;
75 private DataSource dataSource;
76 private PreparedStatement preparedStatement;
77 private ResultSet resultSet;
78 private DatabaseMetaData databaseMetaData;
79 private Map<String, Object> params = new HashMap<String, Object>();
80 private NamedParameterJdbcTemplate namedParameterTemplate;
81
82 @Before
83 public void setUp() throws Exception {
84 connection = mock(Connection.class);
85 dataSource = mock(DataSource.class);
86 preparedStatement = mock(PreparedStatement.class);
87 resultSet = mock(ResultSet.class);
88 namedParameterTemplate = new NamedParameterJdbcTemplate(dataSource);
89 databaseMetaData = mock(DatabaseMetaData.class);
90 given(dataSource.getConnection()).willReturn(connection);
91 given(connection.prepareStatement(anyString())).willReturn(preparedStatement);
92 given(preparedStatement.getConnection()).willReturn(connection);
93 given(preparedStatement.executeQuery()).willReturn(resultSet);
94 given(databaseMetaData.getDatabaseProductName()).willReturn("MySQL");
95 given(databaseMetaData.supportsBatchUpdates()).willReturn(true);
96 }
97
98 @Test
99 public void testNullDataSourceProvidedToCtor() throws Exception {
100 thrown.expect(IllegalArgumentException.class);
101 new NamedParameterJdbcTemplate((DataSource) null);
102 }
103
104 @Test
105 public void testNullJdbcTemplateProvidedToCtor() throws Exception {
106 thrown.expect(IllegalArgumentException.class);
107 new NamedParameterJdbcTemplate((JdbcOperations) null);
108 }
109
110 @Test
111 public void testExecute() throws SQLException {
112 given(preparedStatement.executeUpdate()).willReturn(1);
113
114 params.put("perfId", 1);
115 params.put("priceId", 1);
116 Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
117 new PreparedStatementCallback<Object>() {
118 @Override
119 public Object doInPreparedStatement(PreparedStatement ps)
120 throws SQLException {
121 assertEquals(preparedStatement, ps);
122 ps.executeUpdate();
123 return "result";
124 }
125 });
126
127 assertEquals("result", result);
128 verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
129 verify(preparedStatement).setObject(1, 1);
130 verify(preparedStatement).setObject(2, 1);
131 verify(preparedStatement).close();
132 verify(connection).close();
133 }
134
135 @Test
136 public void testExecuteWithTypedParameters() throws SQLException {
137 given(preparedStatement.executeUpdate()).willReturn(1);
138
139 params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
140 params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
141 Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
142 new PreparedStatementCallback<Object>() {
143 @Override
144 public Object doInPreparedStatement(PreparedStatement ps)
145 throws SQLException {
146 assertEquals(preparedStatement, ps);
147 ps.executeUpdate();
148 return "result";
149 }
150 });
151
152 assertEquals("result", result);
153 verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
154 verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
155 verify(preparedStatement).setObject(2, 1, Types.INTEGER);
156 verify(preparedStatement).close();
157 verify(connection).close();
158 }
159
160 @Test
161 public void testExecuteNoParameters() throws SQLException {
162 given(preparedStatement.executeUpdate()).willReturn(1);
163
164 Object result = namedParameterTemplate.execute(SELECT_NO_PARAMETERS,
165 new PreparedStatementCallback<Object>() {
166 @Override
167 public Object doInPreparedStatement(PreparedStatement ps)
168 throws SQLException {
169 assertEquals(preparedStatement, ps);
170 ps.executeQuery();
171 return "result";
172 }
173 });
174
175 assertEquals("result", result);
176 verify(connection).prepareStatement(SELECT_NO_PARAMETERS);
177 verify(preparedStatement).close();
178 verify(connection).close();
179 }
180
181 @Test
182 public void testQueryWithResultSetExtractor() throws SQLException {
183 given(resultSet.next()).willReturn(true);
184 given(resultSet.getInt("id")).willReturn(1);
185 given(resultSet.getString("forename")).willReturn("rod");
186
187 params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
188 params.put("country", "UK");
189 Customer cust = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params,
190 new ResultSetExtractor<Customer>() {
191 @Override
192 public Customer extractData(ResultSet rs) throws SQLException,
193 DataAccessException {
194 rs.next();
195 Customer cust = new Customer();
196 cust.setId(rs.getInt(COLUMN_NAMES[0]));
197 cust.setForename(rs.getString(COLUMN_NAMES[1]));
198 return cust;
199 }
200 });
201
202 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
203 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
204 verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
205 verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
206 verify(preparedStatement).setString(2, "UK");
207 verify(preparedStatement).close();
208 verify(connection).close();
209 }
210
211 @Test
212 public void testQueryWithResultSetExtractorNoParameters() throws SQLException {
213 given(resultSet.next()).willReturn(true);
214 given(resultSet.getInt("id")).willReturn(1);
215 given(resultSet.getString("forename")).willReturn("rod");
216
217 Customer cust = namedParameterTemplate.query(SELECT_NO_PARAMETERS,
218 new ResultSetExtractor<Customer>() {
219 @Override
220 public Customer extractData(ResultSet rs) throws SQLException,
221 DataAccessException {
222 rs.next();
223 Customer cust = new Customer();
224 cust.setId(rs.getInt(COLUMN_NAMES[0]));
225 cust.setForename(rs.getString(COLUMN_NAMES[1]));
226 return cust;
227 }
228 });
229
230 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
231 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
232 verify(connection).prepareStatement(SELECT_NO_PARAMETERS);
233 verify(preparedStatement).close();
234 verify(connection).close();
235 }
236
237 @Test
238 public void testQueryWithRowCallbackHandler() throws SQLException {
239 given(resultSet.next()).willReturn(true, false);
240 given(resultSet.getInt("id")).willReturn(1);
241 given(resultSet.getString("forename")).willReturn("rod");
242
243 params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
244 params.put("country", "UK");
245 final List<Customer> customers = new LinkedList<Customer>();
246 namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params, new RowCallbackHandler() {
247 @Override
248 public void processRow(ResultSet rs) throws SQLException {
249 Customer cust = new Customer();
250 cust.setId(rs.getInt(COLUMN_NAMES[0]));
251 cust.setForename(rs.getString(COLUMN_NAMES[1]));
252 customers.add(cust);
253 }
254 });
255
256 assertEquals(1, customers.size());
257 assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
258 assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
259 verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
260 verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
261 verify(preparedStatement).setString(2, "UK");
262 verify(preparedStatement).close();
263 verify(connection).close();
264 }
265
266 @Test
267 public void testQueryWithRowCallbackHandlerNoParameters() throws SQLException {
268 given(resultSet.next()).willReturn(true, false);
269 given(resultSet.getInt("id")).willReturn(1);
270 given(resultSet.getString("forename")).willReturn("rod");
271
272 final List<Customer> customers = new LinkedList<Customer>();
273 namedParameterTemplate.query(SELECT_NO_PARAMETERS, new RowCallbackHandler() {
274 @Override
275 public void processRow(ResultSet rs) throws SQLException {
276 Customer cust = new Customer();
277 cust.setId(rs.getInt(COLUMN_NAMES[0]));
278 cust.setForename(rs.getString(COLUMN_NAMES[1]));
279 customers.add(cust);
280 }
281 });
282
283 assertEquals(1, customers.size());
284 assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
285 assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
286 verify(connection).prepareStatement(SELECT_NO_PARAMETERS);
287 verify(preparedStatement).close();
288 verify(connection).close();
289 }
290
291 @Test
292 public void testQueryWithRowMapper() throws SQLException {
293 given(resultSet.next()).willReturn(true, false);
294 given(resultSet.getInt("id")).willReturn(1);
295 given(resultSet.getString("forename")).willReturn("rod");
296
297 params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
298 params.put("country", "UK");
299 List<Customer> customers = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params,
300 new RowMapper<Customer>() {
301 @Override
302 public Customer mapRow(ResultSet rs, int rownum) throws SQLException {
303 Customer cust = new Customer();
304 cust.setId(rs.getInt(COLUMN_NAMES[0]));
305 cust.setForename(rs.getString(COLUMN_NAMES[1]));
306 return cust;
307 }
308 });
309 assertEquals(1, customers.size());
310 assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
311 assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
312 verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
313 verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
314 verify(preparedStatement).setString(2, "UK");
315 verify(preparedStatement).close();
316 verify(connection).close();
317 }
318
319 @Test
320 public void testQueryWithRowMapperNoParameters() throws SQLException {
321 given(resultSet.next()).willReturn(true, false);
322 given(resultSet.getInt("id")).willReturn(1);
323 given(resultSet.getString("forename")).willReturn("rod");
324
325 List<Customer> customers = namedParameterTemplate.query(SELECT_NO_PARAMETERS,
326 new RowMapper<Customer>() {
327 @Override
328 public Customer mapRow(ResultSet rs, int rownum) throws SQLException {
329 Customer cust = new Customer();
330 cust.setId(rs.getInt(COLUMN_NAMES[0]));
331 cust.setForename(rs.getString(COLUMN_NAMES[1]));
332 return cust;
333 }
334 });
335 assertEquals(1, customers.size());
336 assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
337 assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
338 verify(connection).prepareStatement(SELECT_NO_PARAMETERS);
339 verify(preparedStatement).close();
340 verify(connection).close();
341 }
342
343 @Test
344 public void testQueryForObjectWithRowMapper() throws SQLException {
345 given(resultSet.next()).willReturn(true, false);
346 given(resultSet.getInt("id")).willReturn(1);
347 given(resultSet.getString("forename")).willReturn("rod");
348
349 params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
350 params.put("country", "UK");
351 Customer cust = namedParameterTemplate.queryForObject(SELECT_NAMED_PARAMETERS, params,
352 new RowMapper<Customer>() {
353 @Override
354 public Customer mapRow(ResultSet rs, int rownum) throws SQLException {
355 Customer cust = new Customer();
356 cust.setId(rs.getInt(COLUMN_NAMES[0]));
357 cust.setForename(rs.getString(COLUMN_NAMES[1]));
358 return cust;
359 }
360 });
361 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
362 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
363 verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
364 verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
365 verify(preparedStatement).setString(2, "UK");
366 verify(preparedStatement).close();
367 verify(connection).close();
368 }
369
370 @Test
371 public void testUpdate() throws SQLException {
372 given(preparedStatement.executeUpdate()).willReturn(1);
373
374 params.put("perfId", 1);
375 params.put("priceId", 1);
376 int rowsAffected = namedParameterTemplate.update(UPDATE_NAMED_PARAMETERS, params);
377
378 assertEquals(1, rowsAffected);
379 verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
380 verify(preparedStatement).setObject(1, 1);
381 verify(preparedStatement).setObject(2, 1);
382 verify(preparedStatement).close();
383 verify(connection).close();
384 }
385
386 @Test
387 public void testUpdateWithTypedParameters() throws SQLException {
388 given(preparedStatement.executeUpdate()).willReturn(1);
389
390 params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
391 params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
392 int rowsAffected = namedParameterTemplate.update(UPDATE_NAMED_PARAMETERS, params);
393
394 assertEquals(1, rowsAffected);
395 verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
396 verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
397 verify(preparedStatement).setObject(2, 1, Types.INTEGER);
398 verify(preparedStatement).close();
399 verify(connection).close();
400 }
401
402 @Test
403 public void testBatchUpdateWithPlainMap() throws Exception {
404 @SuppressWarnings("unchecked")
405 final Map<String, Integer>[] ids = new Map[2];
406 ids[0] = Collections.singletonMap("id", 100);
407 ids[1] = Collections.singletonMap("id", 200);
408 final int[] rowsAffected = new int[] { 1, 2 };
409
410 given(preparedStatement.executeBatch()).willReturn(rowsAffected);
411 given(connection.getMetaData()).willReturn(databaseMetaData);
412
413 JdbcTemplate template = new JdbcTemplate(dataSource, false);
414 namedParameterTemplate = new NamedParameterJdbcTemplate(template);
415 int[] actualRowsAffected = namedParameterTemplate.batchUpdate("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids);
416
417 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
418 assertEquals(rowsAffected[0], actualRowsAffected[0]);
419 assertEquals(rowsAffected[1], actualRowsAffected[1]);
420 verify(connection).prepareStatement("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?");
421 verify(preparedStatement).setObject(1, 100);
422 verify(preparedStatement).setObject(1, 200);
423 verify(preparedStatement, times(2)).addBatch();
424 verify(preparedStatement, atLeastOnce()).close();
425 verify(connection, atLeastOnce()).close();
426 }
427
428 @Test
429 public void testBatchUpdateWithSqlParameterSource() throws Exception {
430 SqlParameterSource[] ids = new SqlParameterSource[2];
431 ids[0] = new MapSqlParameterSource("id", 100);
432 ids[1] = new MapSqlParameterSource("id", 200);
433 final int[] rowsAffected = new int[] { 1, 2 };
434
435 given(preparedStatement.executeBatch()).willReturn(rowsAffected);
436 given(connection.getMetaData()).willReturn(databaseMetaData);
437
438 JdbcTemplate template = new JdbcTemplate(dataSource, false);
439 namedParameterTemplate = new NamedParameterJdbcTemplate(template);
440 int[] actualRowsAffected = namedParameterTemplate.batchUpdate("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids);
441
442 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
443 assertEquals(rowsAffected[0], actualRowsAffected[0]);
444 assertEquals(rowsAffected[1], actualRowsAffected[1]);
445 verify(connection).prepareStatement("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?");
446 verify(preparedStatement).setObject(1, 100);
447 verify(preparedStatement).setObject(1, 200);
448 verify(preparedStatement, times(2)).addBatch();
449 verify(preparedStatement, atLeastOnce()).close();
450 verify(connection, atLeastOnce()).close();
451 }
452
453 @Test
454 public void testBatchUpdateWithSqlParameterSourcePlusTypeInfo() throws Exception {
455 SqlParameterSource[] ids = new SqlParameterSource[2];
456 ids[0] = new MapSqlParameterSource().addValue("id", 100, Types.NUMERIC);
457 ids[1] = new MapSqlParameterSource().addValue("id", 200, Types.NUMERIC);
458 final int[] rowsAffected = new int[] { 1, 2 };
459
460 given(preparedStatement.executeBatch()).willReturn(rowsAffected);
461 given(connection.getMetaData()).willReturn(databaseMetaData);
462
463 JdbcTemplate template = new JdbcTemplate(dataSource, false);
464 namedParameterTemplate = new NamedParameterJdbcTemplate(template);
465 int[] actualRowsAffected = namedParameterTemplate.batchUpdate("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids);
466
467 assertTrue("executed 2 updates", actualRowsAffected.length == 2);
468 assertEquals(rowsAffected[0], actualRowsAffected[0]);
469 assertEquals(rowsAffected[1], actualRowsAffected[1]);
470 verify(connection).prepareStatement("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?");
471 verify(preparedStatement).setObject(1, 100, Types.NUMERIC);
472 verify(preparedStatement).setObject(1, 200, Types.NUMERIC);
473 verify(preparedStatement, times(2)).addBatch();
474 verify(preparedStatement, atLeastOnce()).close();
475 verify(connection, atLeastOnce()).close();
476 }
477
478 }