View Javadoc
1   /*
2    * Copyright 2002-2014 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;
18  
19  import java.sql.Connection;
20  import java.sql.DatabaseMetaData;
21  import java.sql.ParameterMetaData;
22  import java.sql.PreparedStatement;
23  import java.sql.SQLException;
24  import java.sql.Types;
25  import java.util.GregorianCalendar;
26  
27  import org.junit.Before;
28  import org.junit.Test;
29  
30  import static org.junit.Assert.*;
31  import static org.mockito.BDDMockito.*;
32  
33  /**
34   * @author Juergen Hoeller
35   * @since 31.08.2004
36   */
37  public class StatementCreatorUtilsTests {
38  
39  	private PreparedStatement preparedStatement;
40  
41  	@Before
42  	public void setUp() {
43  		preparedStatement = mock(PreparedStatement.class);
44  	}
45  
46  	@Test
47  	public void testSetParameterValueWithNullAndType() throws SQLException {
48  		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.VARCHAR, null, null);
49  		verify(preparedStatement).setNull(1, Types.VARCHAR);
50  	}
51  
52  	@Test
53  	public void testSetParameterValueWithNullAndTypeName() throws SQLException {
54  		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.VARCHAR, "mytype", null);
55  		verify(preparedStatement).setNull(1, Types.VARCHAR, "mytype");
56  	}
57  
58  	@Test
59  	public void testSetParameterValueWithNullAndUnknownType() throws SQLException {
60  		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
61  		verify(preparedStatement).setNull(1, Types.NULL);
62  	}
63  
64  	@Test
65  	public void testSetParameterValueWithNullAndUnknownTypeOnInformix() throws SQLException {
66  		StatementCreatorUtils.driversWithNoSupportForGetParameterType.clear();
67  		Connection con = mock(Connection.class);
68  		DatabaseMetaData dbmd = mock(DatabaseMetaData.class);
69  		given(preparedStatement.getConnection()).willReturn(con);
70  		given(con.getMetaData()).willReturn(dbmd);
71  		given(dbmd.getDatabaseProductName()).willReturn("Informix Dynamic Server");
72  		given(dbmd.getDriverName()).willReturn("Informix Driver");
73  		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
74  		verify(dbmd).getDatabaseProductName();
75  		verify(dbmd).getDriverName();
76  		verify(preparedStatement).setObject(1, null);
77  		assertEquals(1, StatementCreatorUtils.driversWithNoSupportForGetParameterType.size());
78  	}
79  
80  	@Test
81  	public void testSetParameterValueWithNullAndUnknownTypeOnDerbyEmbedded() throws SQLException {
82  		StatementCreatorUtils.driversWithNoSupportForGetParameterType.clear();
83  		Connection con = mock(Connection.class);
84  		DatabaseMetaData dbmd = mock(DatabaseMetaData.class);
85  		given(preparedStatement.getConnection()).willReturn(con);
86  		given(con.getMetaData()).willReturn(dbmd);
87  		given(dbmd.getDatabaseProductName()).willReturn("Apache Derby");
88  		given(dbmd.getDriverName()).willReturn("Apache Derby Embedded Driver");
89  		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
90  		verify(dbmd).getDatabaseProductName();
91  		verify(dbmd).getDriverName();
92  		verify(preparedStatement).setNull(1, Types.VARCHAR);
93  		assertEquals(1, StatementCreatorUtils.driversWithNoSupportForGetParameterType.size());
94  	}
95  
96  	@Test
97  	public void testSetParameterValueWithNullAndGetParameterTypeWorking() throws SQLException {
98  		StatementCreatorUtils.driversWithNoSupportForGetParameterType.clear();
99  		ParameterMetaData pmd = mock(ParameterMetaData.class);
100 		given(preparedStatement.getParameterMetaData()).willReturn(pmd);
101 		given(pmd.getParameterType(1)).willReturn(Types.SMALLINT);
102 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
103 		verify(pmd).getParameterType(1);
104 		verify(preparedStatement, never()).getConnection();
105 		verify(preparedStatement).setNull(1, Types.SMALLINT);
106 		assertTrue(StatementCreatorUtils.driversWithNoSupportForGetParameterType.isEmpty());
107 	}
108 
109 	@Test
110 	public void testSetParameterValueWithNullAndGetParameterTypeWorkingButNotForOtherDriver() throws SQLException {
111 		StatementCreatorUtils.driversWithNoSupportForGetParameterType.clear();
112 		StatementCreatorUtils.driversWithNoSupportForGetParameterType.add("Oracle JDBC Driver");
113 		Connection con = mock(Connection.class);
114 		DatabaseMetaData dbmd = mock(DatabaseMetaData.class);
115 		ParameterMetaData pmd = mock(ParameterMetaData.class);
116 		given(preparedStatement.getConnection()).willReturn(con);
117 		given(con.getMetaData()).willReturn(dbmd);
118 		given(dbmd.getDriverName()).willReturn("Apache Derby Embedded Driver");
119 		given(preparedStatement.getParameterMetaData()).willReturn(pmd);
120 		given(pmd.getParameterType(1)).willReturn(Types.SMALLINT);
121 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
122 		verify(dbmd).getDriverName();
123 		verify(pmd).getParameterType(1);
124 		verify(preparedStatement).setNull(1, Types.SMALLINT);
125 		assertEquals(1, StatementCreatorUtils.driversWithNoSupportForGetParameterType.size());
126 	}
127 
128 	@Test
129 	public void testSetParameterValueWithNullAndUnknownTypeAndGetParameterTypeNotWorking() throws SQLException {
130 		StatementCreatorUtils.driversWithNoSupportForGetParameterType.clear();
131 		Connection con = mock(Connection.class);
132 		DatabaseMetaData dbmd = mock(DatabaseMetaData.class);
133 		given(preparedStatement.getConnection()).willReturn(con);
134 		given(con.getMetaData()).willReturn(dbmd);
135 		given(dbmd.getDatabaseProductName()).willReturn("Apache Derby");
136 		given(dbmd.getDriverName()).willReturn("Apache Derby Embedded Driver");
137 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
138 		verify(dbmd).getDatabaseProductName();
139 		verify(dbmd).getDriverName();
140 		verify(preparedStatement).setNull(1, Types.VARCHAR);
141 		assertEquals(1, StatementCreatorUtils.driversWithNoSupportForGetParameterType.size());
142 
143 		reset(preparedStatement, con, dbmd);
144 		ParameterMetaData pmd = mock(ParameterMetaData.class);
145 		given(preparedStatement.getConnection()).willReturn(con);
146 		given(con.getMetaData()).willReturn(dbmd);
147 		given(preparedStatement.getParameterMetaData()).willReturn(pmd);
148 		given(pmd.getParameterType(1)).willThrow(new SQLException("unsupported"));
149 		given(dbmd.getDatabaseProductName()).willReturn("Informix Dynamic Server");
150 		given(dbmd.getDriverName()).willReturn("Informix Driver");
151 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
152 		verify(pmd).getParameterType(1);
153 		verify(dbmd).getDatabaseProductName();
154 		verify(dbmd).getDriverName();
155 		verify(preparedStatement).setObject(1, null);
156 		assertEquals(2, StatementCreatorUtils.driversWithNoSupportForGetParameterType.size());
157 
158 		reset(preparedStatement, con, dbmd, pmd);
159 		given(preparedStatement.getConnection()).willReturn(con);
160 		given(con.getMetaData()).willReturn(dbmd);
161 		given(dbmd.getDatabaseProductName()).willReturn("Informix Dynamic Server");
162 		given(dbmd.getDriverName()).willReturn("Informix Driver");
163 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, null);
164 		verify(preparedStatement, never()).getParameterMetaData();
165 		verify(dbmd).getDatabaseProductName();
166 		verify(dbmd).getDriverName();
167 		verify(preparedStatement).setObject(1, null);
168 		assertEquals(2, StatementCreatorUtils.driversWithNoSupportForGetParameterType.size());
169 	}
170 
171 	@Test
172 	public void testSetParameterValueWithString() throws SQLException {
173 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.VARCHAR, null, "test");
174 		verify(preparedStatement).setString(1, "test");
175 	}
176 
177 	@Test
178 	public void testSetParameterValueWithStringAndSpecialType() throws SQLException {
179 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.CHAR, null, "test");
180 		verify(preparedStatement).setObject(1, "test", Types.CHAR);
181 	}
182 
183 	@Test public void testSetParameterValueWithStringAndUnknownType() throws SQLException {
184 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, "test");
185 		verify(preparedStatement).setString(1, "test");
186 	}
187 
188 	@Test
189 	public void testSetParameterValueWithSqlDate() throws SQLException {
190 		java.sql.Date date = new java.sql.Date(1000);
191 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.DATE, null, date);
192 		verify(preparedStatement).setDate(1, date);
193 	}
194 
195 	@Test
196 	public void testSetParameterValueWithDateAndUtilDate() throws SQLException {
197 		java.util.Date date = new java.util.Date(1000);
198 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.DATE, null, date);
199 		verify(preparedStatement).setDate(1, new java.sql.Date(1000));
200 	}
201 
202 	@Test
203 	public void testSetParameterValueWithDateAndCalendar() throws SQLException {
204 		java.util.Calendar cal = new GregorianCalendar();
205 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.DATE, null, cal);
206 		verify(preparedStatement).setDate(1, new java.sql.Date(cal.getTime().getTime()), cal);
207 	}
208 
209 	@Test
210 	public void testSetParameterValueWithSqlTime() throws SQLException {
211 		java.sql.Time time = new java.sql.Time(1000);
212 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.TIME, null, time);
213 		verify(preparedStatement).setTime(1, time);
214 	}
215 
216 	@Test
217 	public void testSetParameterValueWithTimeAndUtilDate() throws SQLException {
218 		java.util.Date date = new java.util.Date(1000);
219 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.TIME, null, date);
220 		verify(preparedStatement).setTime(1, new java.sql.Time(1000));
221 	}
222 
223 	@Test
224 	public void testSetParameterValueWithTimeAndCalendar() throws SQLException {
225 		java.util.Calendar cal = new GregorianCalendar();
226 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.TIME, null, cal);
227 		verify(preparedStatement).setTime(1, new java.sql.Time(cal.getTime().getTime()), cal);
228 	}
229 
230 	@Test
231 	public void testSetParameterValueWithSqlTimestamp() throws SQLException {
232 		java.sql.Timestamp timestamp = new java.sql.Timestamp(1000);
233 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.TIMESTAMP, null, timestamp);
234 		verify(preparedStatement).setTimestamp(1, timestamp);
235 	}
236 
237 	@Test
238 	public void testSetParameterValueWithTimestampAndUtilDate() throws SQLException {
239 		java.util.Date date = new java.util.Date(1000);
240 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.TIMESTAMP, null, date);
241 		verify(preparedStatement).setTimestamp(1, new java.sql.Timestamp(1000));
242 	}
243 
244 	@Test
245 	public void testSetParameterValueWithTimestampAndCalendar() throws SQLException {
246 		java.util.Calendar cal = new GregorianCalendar();
247 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.TIMESTAMP, null, cal);
248 		verify(preparedStatement).setTimestamp(1, new java.sql.Timestamp(cal.getTime().getTime()), cal);
249 	}
250 
251 	@Test
252 	public void testSetParameterValueWithDateAndUnknownType() throws SQLException {
253 		java.util.Date date = new java.util.Date(1000);
254 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, date);
255 		verify(preparedStatement).setTimestamp(1, new java.sql.Timestamp(1000));
256 	}
257 
258 	@Test
259 	public void testSetParameterValueWithCalendarAndUnknownType() throws SQLException {
260 		java.util.Calendar cal = new GregorianCalendar();
261 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, SqlTypeValue.TYPE_UNKNOWN, null, cal);
262 		verify(preparedStatement).setTimestamp(1, new java.sql.Timestamp(cal.getTime().getTime()), cal);
263 	}
264 
265 	@Test  // SPR-8571
266 	public void testSetParameterValueWithStringAndVendorSpecificType() throws SQLException {
267 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.OTHER, null, "test");
268 		verify(preparedStatement).setString(1, "test");
269 	}
270 	@Test  // SPR-8571
271 	public void testSetParameterValueWithNullAndVendorSpecificType() throws SQLException {
272 		StatementCreatorUtils.setParameterValue(preparedStatement, 1, Types.OTHER, null, null);
273 		verify(preparedStatement).setNull(1, Types.NULL);
274 	}
275 
276 }