|
1
16
17 package org.springframework.jdbc.object;
18
19 import java.sql.PreparedStatement  ;
20 import java.sql.ResultSet ;
21 import java.sql.ResultSetMetaData ;
22 import java.sql.SQLException ;
23 import java.sql.Types ;
24
25 import org.easymock.MockControl;
26
27 import org.springframework.core.JdkVersion;
28 import org.springframework.jdbc.AbstractJdbcTests;
29 import org.springframework.jdbc.JdbcUpdateAffectedIncorrectNumberOfRowsException;
30 import org.springframework.jdbc.core.SqlParameter;
31 import org.springframework.jdbc.support.GeneratedKeyHolder;
32 import org.springframework.jdbc.support.KeyHolder;
33
34
38 public class SqlUpdateTests extends AbstractJdbcTests {
39
40 private static final String UPDATE =
41 "update seat_status set booking_id = null";
42 private static final String UPDATE_INT =
43 "update seat_status set booking_id = null where performance_id = ?";
44 private static final String UPDATE_INT_INT =
45 "update seat_status set booking_id = null where performance_id = ? and price_band_id = ?";
46 private static final String UPDATE_STRING =
47 "update seat_status set booking_id = null where name = ?";
48 private static final String UPDATE_OBJECTS =
49 "update seat_status set booking_id = null where performance_id = ? and price_band_id = ? and name = ? and confirmed = ?";
50 private static final String INSERT_GENERATE_KEYS =
51 "insert into show (name) values(?)";
52
53 private MockControl ctrlPreparedStatement;
54
55 private PreparedStatement mockPreparedStatement;
56
57 private MockControl ctrlResultSet;
58
59 private ResultSet mockResultSet;
60
61 private MockControl ctrlResultSetMetaData;
62
63 private ResultSetMetaData mockResultSetMetaData;
64
65 protected void setUp() throws Exception {
66 super.setUp();
67 ctrlPreparedStatement = MockControl.createControl(PreparedStatement .class);
68 mockPreparedStatement = (PreparedStatement ) ctrlPreparedStatement.getMock();
69 }
70
71 protected void tearDown() throws Exception {
72 super.tearDown();
73 if (shouldVerify()) {
74 ctrlPreparedStatement.verify();
75 }
76 }
77
78 protected void replay() {
79 super.replay();
80 ctrlPreparedStatement.replay();
81 }
82
83 public void testUpdate() throws SQLException {
84 mockPreparedStatement.executeUpdate();
85 ctrlPreparedStatement.setReturnValue(1);
86 mockPreparedStatement.getWarnings();
87 ctrlPreparedStatement.setReturnValue(null);
88 mockPreparedStatement.close();
89 ctrlPreparedStatement.setVoidCallable();
90
91 mockConnection.prepareStatement(UPDATE);
92 ctrlConnection.setReturnValue(mockPreparedStatement);
93
94 replay();
95
96 Updater pc = new Updater();
97 int rowsAffected = pc.run();
98 assertEquals(1, rowsAffected);
99 }
100
101 public void testUpdateInt() throws SQLException {
102 mockPreparedStatement.setObject(1, new Integer (1), Types.NUMERIC);
103 ctrlPreparedStatement.setVoidCallable();
104 mockPreparedStatement.executeUpdate();
105 ctrlPreparedStatement.setReturnValue(1);
106 mockPreparedStatement.getWarnings();
107 ctrlPreparedStatement.setReturnValue(null);
108 mockPreparedStatement.close();
109 ctrlPreparedStatement.setVoidCallable();
110
111 mockConnection.prepareStatement(UPDATE_INT);
112 ctrlConnection.setReturnValue(mockPreparedStatement);
113
114 replay();
115
116 IntUpdater pc = new IntUpdater();
117 int rowsAffected = pc.run(1);
118 assertEquals(1, rowsAffected);
119 }
120
121 public void testUpdateIntInt() throws SQLException {
122 mockPreparedStatement.setObject(1, new Integer (1), Types.NUMERIC);
123 mockPreparedStatement.setObject(2, new Integer (1), Types.NUMERIC);
124 ctrlPreparedStatement.setVoidCallable();
125 mockPreparedStatement.executeUpdate();
126 ctrlPreparedStatement.setReturnValue(1);
127 mockPreparedStatement.getWarnings();
128 ctrlPreparedStatement.setReturnValue(null);
129 mockPreparedStatement.close();
130 ctrlPreparedStatement.setVoidCallable();
131
132 mockConnection.prepareStatement(UPDATE_INT_INT);
133 ctrlConnection.setReturnValue(mockPreparedStatement);
134
135 replay();
136
137 IntIntUpdater pc = new IntIntUpdater();
138 int rowsAffected = pc.run(1, 1);
139 assertEquals(1, rowsAffected);
140 }
141
142 public void testUpdateString() throws SQLException {
143 mockPreparedStatement.setString(1, "rod");
144 ctrlPreparedStatement.setVoidCallable();
145 mockPreparedStatement.executeUpdate();
146 ctrlPreparedStatement.setReturnValue(1);
147 mockPreparedStatement.getWarnings();
148 ctrlPreparedStatement.setReturnValue(null);
149 mockPreparedStatement.close();
150 ctrlPreparedStatement.setVoidCallable();
151
152 mockConnection.prepareStatement(UPDATE_STRING);
153 ctrlConnection.setReturnValue(mockPreparedStatement);
154
155 replay();
156
157 StringUpdater pc = new StringUpdater();
158 int rowsAffected = pc.run("rod");
159 assertEquals(1, rowsAffected);
160 }
161
162 public void testUpdateMixed() throws SQLException {
163 mockPreparedStatement.setObject(1, new Integer (1), Types.NUMERIC);
164 mockPreparedStatement.setObject(2, new Integer (1), Types.NUMERIC);
165 mockPreparedStatement.setString(3, "rod");
166 mockPreparedStatement.setObject(4, Boolean.TRUE, Types.BOOLEAN);
167 ctrlPreparedStatement.setVoidCallable();
168 mockPreparedStatement.executeUpdate();
169 ctrlPreparedStatement.setReturnValue(1);
170 mockPreparedStatement.getWarnings();
171 ctrlPreparedStatement.setReturnValue(null);
172 mockPreparedStatement.close();
173 ctrlPreparedStatement.setVoidCallable();
174
175 mockConnection.prepareStatement(UPDATE_OBJECTS);
176 ctrlConnection.setReturnValue(mockPreparedStatement);
177
178 replay();
179
180 MixedUpdater pc = new MixedUpdater();
181 int rowsAffected = pc.run(1, 1, "rod", true);
182 assertEquals(1, rowsAffected);
183 }
184
185 public void testUpdateAndGeneratedKeys() throws SQLException {
186 if (JdkVersion.getMajorJavaVersion() < JdkVersion.JAVA_14) {
187 return;
188 }
189
190 ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData .class);
191 mockResultSetMetaData = (ResultSetMetaData ) ctrlResultSetMetaData.getMock();
192 mockResultSetMetaData.getColumnCount();
193 ctrlResultSetMetaData.setReturnValue(1);
194 mockResultSetMetaData.getColumnName(1);
195 ctrlResultSetMetaData.setReturnValue("1", 2);
196
197 ctrlResultSet = MockControl.createControl(ResultSet .class);
198 mockResultSet = (ResultSet ) ctrlResultSet.getMock();
199 mockResultSet.getMetaData();
200 ctrlResultSet.setReturnValue(mockResultSetMetaData);
201 mockResultSet.next();
202 ctrlResultSet.setReturnValue(true);
203 mockResultSet.getObject(1);
204 ctrlResultSet.setReturnValue(new Integer (11));
205 mockResultSet.next();
206 ctrlResultSet.setReturnValue(false);
207 mockResultSet.close();
208 ctrlResultSet.setVoidCallable();
209
210 mockPreparedStatement.setString(1, "rod");
211 ctrlPreparedStatement.setVoidCallable();
212 mockPreparedStatement.executeUpdate();
213 ctrlPreparedStatement.setReturnValue(1);
214 mockPreparedStatement.getGeneratedKeys();
215 ctrlPreparedStatement.setReturnValue(mockResultSet);
216 mockPreparedStatement.getWarnings();
217 ctrlPreparedStatement.setReturnValue(null);
218 mockPreparedStatement.close();
219 ctrlPreparedStatement.setVoidCallable();
220
221 mockConnection.prepareStatement(INSERT_GENERATE_KEYS, PreparedStatement.RETURN_GENERATED_KEYS);
222 ctrlConnection.setReturnValue(mockPreparedStatement);
223
224 replay();
225 ctrlResultSet.replay();
226 ctrlResultSetMetaData.replay();
227
228 GeneratedKeysUpdater pc = new GeneratedKeysUpdater();
229 KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
230 int rowsAffected = pc.run("rod", generatedKeyHolder);
231 assertEquals(1, rowsAffected);
232 assertEquals(1, generatedKeyHolder.getKeyList().size());
233 assertEquals(11, generatedKeyHolder.getKey().intValue());
234 }
235
236 public void testUpdateConstructor() throws SQLException {
237 mockPreparedStatement.setObject(1, new Integer (1), Types.NUMERIC);
238 mockPreparedStatement.setObject(2, new Integer (1), Types.NUMERIC);
239 mockPreparedStatement.setString(3, "rod");
240 mockPreparedStatement.setObject(4, Boolean.TRUE, Types.BOOLEAN);
241 ctrlPreparedStatement.setVoidCallable();
242 mockPreparedStatement.executeUpdate();
243 ctrlPreparedStatement.setReturnValue(1);
244 mockPreparedStatement.getWarnings();
245 ctrlPreparedStatement.setReturnValue(null);
246 mockPreparedStatement.close();
247 ctrlPreparedStatement.setVoidCallable();
248
249 mockConnection.prepareStatement(UPDATE_OBJECTS);
250 ctrlConnection.setReturnValue(mockPreparedStatement);
251
252 replay();
253
254 ConstructorUpdater pc = new ConstructorUpdater();
255 int rowsAffected = pc.run(1, 1, "rod", true);
256 assertEquals(1, rowsAffected);
257 }
258
259 public void testUnderMaxRows() throws SQLException {
260 mockPreparedStatement.executeUpdate();
261 ctrlPreparedStatement.setReturnValue(3);
262 mockPreparedStatement.getWarnings();
263 ctrlPreparedStatement.setReturnValue(null);
264 mockPreparedStatement.close();
265 ctrlPreparedStatement.setVoidCallable();
266
267 mockConnection.prepareStatement(UPDATE);
268 ctrlConnection.setReturnValue(mockPreparedStatement);
269
270 replay();
271
272 MaxRowsUpdater pc = new MaxRowsUpdater();
273 int rowsAffected = pc.run();
274 assertEquals(3, rowsAffected);
275 }
276
277 public void testMaxRows() throws SQLException {
278 mockPreparedStatement.executeUpdate();
279 ctrlPreparedStatement.setReturnValue(5);
280 mockPreparedStatement.getWarnings();
281 ctrlPreparedStatement.setReturnValue(null);
282 mockPreparedStatement.close();
283 ctrlPreparedStatement.setVoidCallable();
284
285 mockConnection.prepareStatement(UPDATE);
286 ctrlConnection.setReturnValue(mockPreparedStatement);
287
288 replay();
289
290 MaxRowsUpdater pc = new MaxRowsUpdater();
291 int rowsAffected = pc.run();
292 assertEquals(5, rowsAffected);
293 }
294
295 public void testOverMaxRows() throws SQLException {
296 mockPreparedStatement.executeUpdate();
297 ctrlPreparedStatement.setReturnValue(8);
298 mockPreparedStatement.getWarnings();
299 ctrlPreparedStatement.setReturnValue(null);
300 mockPreparedStatement.close();
301 ctrlPreparedStatement.setVoidCallable();
302
303 mockConnection.prepareStatement(UPDATE);
304 ctrlConnection.setReturnValue(mockPreparedStatement);
305
306 replay();
307
308 MaxRowsUpdater pc = new MaxRowsUpdater();
309 try {
310 int rowsAffected = pc.run();
311 fail("Shouldn't continue when too many rows affected");
312 }
313 catch (JdbcUpdateAffectedIncorrectNumberOfRowsException juaicrex) {
314 }
316 }
317
318 public void testRequiredRows() throws SQLException {
319 mockPreparedStatement.executeUpdate();
320 ctrlPreparedStatement.setReturnValue(3);
321 mockPreparedStatement.getWarnings();
322 ctrlPreparedStatement.setReturnValue(null);
323 mockPreparedStatement.close();
324 ctrlPreparedStatement.setVoidCallable();
325
326 mockConnection.prepareStatement(UPDATE);
327 ctrlConnection.setReturnValue(mockPreparedStatement);
328
329 replay();
330
331 RequiredRowsUpdater pc = new RequiredRowsUpdater();
332 int rowsAffected = pc.run();
333 assertEquals(3, rowsAffected);
334 }
335
336 public void testNotRequiredRows() throws SQLException {
337 mockPreparedStatement.executeUpdate();
338 ctrlPreparedStatement.setReturnValue(2);
339 mockPreparedStatement.getWarnings();
340 ctrlPreparedStatement.setReturnValue(null);
341 mockPreparedStatement.close();
342 ctrlPreparedStatement.setVoidCallable();
343
344 mockConnection.prepareStatement(UPDATE);
345 ctrlConnection.setReturnValue(mockPreparedStatement);
346
347 replay();
348
349 RequiredRowsUpdater pc = new RequiredRowsUpdater();
350 try {
351 int rowsAffected = pc.run();
352 fail("Shouldn't continue when too many rows affected");
353 }
354 catch (JdbcUpdateAffectedIncorrectNumberOfRowsException juaicrex) {
355 }
357 }
358
359
360 private class Updater extends SqlUpdate {
361
362 public Updater() {
363 setSql(UPDATE);
364 setDataSource(mockDataSource);
365 compile();
366 }
367
368 public int run() {
369 return update();
370 }
371 }
372
373
374 private class IntUpdater extends SqlUpdate {
375
376 public IntUpdater() {
377 setSql(UPDATE_INT);
378 setDataSource(mockDataSource);
379 declareParameter(new SqlParameter(Types.NUMERIC));
380 compile();
381 }
382
383 public int run(int performanceId) {
384 return update(performanceId);
385 }
386 }
387
388
389 private class IntIntUpdater extends SqlUpdate {
390
391 public IntIntUpdater() {
392 setSql(UPDATE_INT_INT);
393 setDataSource(mockDataSource);
394 declareParameter(new SqlParameter(Types.NUMERIC));
395 declareParameter(new SqlParameter(Types.NUMERIC));
396 compile();
397 }
398
399 public int run(int performanceId, int type) {
400 return update(performanceId, type);
401 }
402 }
403
404
405 private class StringUpdater extends SqlUpdate {
406
407 public StringUpdater() {
408 setSql(UPDATE_STRING);
409 setDataSource(mockDataSource);
410 declareParameter(new SqlParameter(Types.VARCHAR));
411 compile();
412 }
413
414 public int run(String name) {
415 return update(name);
416 }
417 }
418
419
420 private class MixedUpdater extends SqlUpdate {
421
422 public MixedUpdater() {
423 setSql(UPDATE_OBJECTS);
424 setDataSource(mockDataSource);
425 declareParameter(new SqlParameter(Types.NUMERIC));
426 declareParameter(new SqlParameter(Types.NUMERIC));
427 declareParameter(new SqlParameter(Types.VARCHAR));
428 declareParameter(new SqlParameter(Types.BOOLEAN));
429 compile();
430 }
431
432 public int run(int performanceId, int type, String name, boolean confirmed) {
433 Object [] params =
434 new Object [] {new Integer (performanceId), new Integer (type), name,
435 new Boolean (confirmed)};
436 return update(params);
437 }
438 }
439
440
441 private class GeneratedKeysUpdater extends SqlUpdate {
442
443 public GeneratedKeysUpdater() {
444 setSql(INSERT_GENERATE_KEYS);
445 setDataSource(mockDataSource);
446 declareParameter(new SqlParameter(Types.VARCHAR));
447 setReturnGeneratedKeys(true);
448 compile();
449 }
450
451 public int run(String name, KeyHolder generatedKeyHolder) {
452 Object [] params = new Object [] {name};
453 return update(params, generatedKeyHolder);
454 }
455 }
456
457
458 private class ConstructorUpdater extends SqlUpdate {
459
460 public ConstructorUpdater() {
461 super(mockDataSource, UPDATE_OBJECTS,
462 new int[] {Types.NUMERIC, Types.NUMERIC, Types.VARCHAR, Types.BOOLEAN });
463 compile();
464 }
465
466 public int run(int performanceId, int type, String name, boolean confirmed) {
467 Object [] params =
468 new Object [] {
469 new Integer (performanceId), new Integer (type), name, new Boolean (confirmed)};
470 return update(params);
471 }
472 }
473
474
475 private class MaxRowsUpdater extends SqlUpdate {
476
477 public MaxRowsUpdater() {
478 setSql(UPDATE);
479 setDataSource(mockDataSource);
480 setMaxRowsAffected(5);
481 compile();
482 }
483
484 public int run() {
485 return update();
486 }
487 }
488
489
490 private class RequiredRowsUpdater extends SqlUpdate {
491
492 public RequiredRowsUpdater() {
493 setSql(UPDATE);
494 setDataSource(mockDataSource);
495 setRequiredRowsAffected(3);
496 compile();
497 }
498
499 public int run() {
500 return update();
501 }
502 }
503
504 }
505 |
Popular Tags |
Java API By Example, From Geeks To Geeks.