View Javadoc
1   /*
2    * Copyright 2002-2013 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.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   * @author Rick Evans
52   * @author Juergen Hoeller
53   * @author Chris Beams
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 }