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 |