1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.springframework.jdbc.object;
18
19
20 import java.sql.Connection;
21 import java.sql.PreparedStatement;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.sql.Types;
25 import java.util.HashMap;
26 import java.util.List;
27 import java.util.Map;
28 import javax.sql.DataSource;
29
30 import org.junit.Before;
31 import org.junit.Test;
32
33 import org.springframework.beans.factory.BeanFactory;
34 import org.springframework.beans.factory.support.BeanDefinitionRegistry;
35 import org.springframework.beans.factory.support.DefaultListableBeanFactory;
36 import org.springframework.beans.factory.xml.XmlBeanDefinitionReader;
37 import org.springframework.core.io.ClassPathResource;
38 import org.springframework.jdbc.Customer;
39 import org.springframework.jdbc.datasource.TestDataSourceWrapper;
40
41 import static org.junit.Assert.*;
42 import static org.mockito.BDDMockito.*;
43
44
45
46
47 public class GenericSqlQueryTests {
48
49 private static final String SELECT_ID_FORENAME_NAMED_PARAMETERS_PARSED =
50 "select id, forename from custmr where id = ? and country = ?";
51
52 private BeanFactory beanFactory;
53
54 private Connection connection;
55
56 private PreparedStatement preparedStatement;
57
58 private ResultSet resultSet;
59
60 @Before
61 public void setUp() throws Exception {
62 this.beanFactory = new DefaultListableBeanFactory();
63 new XmlBeanDefinitionReader((BeanDefinitionRegistry) this.beanFactory).loadBeanDefinitions(
64 new ClassPathResource("org/springframework/jdbc/object/GenericSqlQueryTests-context.xml"));
65 DataSource dataSource = mock(DataSource.class);
66 this.connection = mock(Connection.class);
67 this.preparedStatement = mock(PreparedStatement.class);
68 this.resultSet = mock(ResultSet.class);
69 given(dataSource.getConnection()).willReturn(connection);
70 TestDataSourceWrapper testDataSource = (TestDataSourceWrapper) beanFactory.getBean("dataSource");
71 testDataSource.setTarget(dataSource);
72 }
73
74 @Test
75 public void testPlaceHoldersCustomerQuery() throws SQLException {
76 SqlQuery<?> query = (SqlQuery<?>) beanFactory.getBean("queryWithPlaceHolders");
77 doTestCustomerQuery(query, false);
78 }
79
80 @Test
81 public void testNamedParameterCustomerQuery() throws SQLException {
82 SqlQuery<?> query = (SqlQuery<?>) beanFactory.getBean("queryWithNamedParameters");
83 doTestCustomerQuery(query, true);
84 }
85
86 private void doTestCustomerQuery(SqlQuery<?> query, boolean namedParameters) throws SQLException {
87 given(resultSet.next()).willReturn(true);
88 given(resultSet.getInt("id")).willReturn(1);
89 given(resultSet.getString("forename")).willReturn("rod");
90 given(resultSet.next()).willReturn(true, false);
91 given(preparedStatement.executeQuery()).willReturn(resultSet);
92 given(connection.prepareStatement(SELECT_ID_FORENAME_NAMED_PARAMETERS_PARSED)).willReturn(preparedStatement);
93
94 List<?> queryResults;
95 if (namedParameters) {
96 Map<String, Object> params = new HashMap<String, Object>(2);
97 params.put("id", 1);
98 params.put("country", "UK");
99 queryResults = query.executeByNamedParam(params);
100 }
101 else {
102 Object[] params = new Object[] {1, "UK"};
103 queryResults = query.execute(params);
104 }
105 assertTrue("Customer was returned correctly", queryResults.size() == 1);
106 Customer cust = (Customer) queryResults.get(0);
107 assertTrue("Customer id was assigned correctly", cust.getId() == 1);
108 assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
109
110 verify(resultSet).close();
111 verify(preparedStatement).setObject(1, 1, Types.INTEGER);
112 verify(preparedStatement).setString(2, "UK");
113 verify(preparedStatement).close();
114 }
115
116 }