jdbctemplatetestsuite.java

Java/J2EE application framework based on [Expert One-on-One J2EE Design and Development] by Rod John

			actualRowsAffected == rowsAffected);

		ctrlStatement.verify();
	}

	public void testSqlUpdateEncountersSqlException() throws Exception {
		SQLException sex = new SQLException("bad update");
		final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";

		MockControl ctrlStatement = MockControl.createControl(Statement.class);
		Statement mockStatement = (Statement) ctrlStatement.getMock();
		mockStatement.executeUpdate(sql);
		ctrlStatement.setThrowable(sex);
		mockStatement.close();
		ctrlStatement.setVoidCallable();

		mockConnection.createStatement();
		ctrlConnection.setReturnValue(mockStatement);

		ctrlStatement.replay();
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		try {
			template.update(sql);
		}
		catch (DataAccessException ex) {
			assertTrue("root cause is correct", ex.getCause() == sex);
		}

		ctrlStatement.verify();
	}

	public void testSqlUpdateWithThreadConnection() throws Exception {
		final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
		int rowsAffected = 33;

		MockControl ctrlStatement = MockControl.createControl(Statement.class);
		Statement mockStatement = (Statement) ctrlStatement.getMock();
		mockStatement.executeUpdate(sql);
		ctrlStatement.setReturnValue(rowsAffected);
		mockStatement.getWarnings();
		ctrlStatement.setReturnValue(null);
		mockStatement.close();
		ctrlStatement.setVoidCallable();

		mockConnection.createStatement();
		ctrlConnection.setReturnValue(mockStatement);

		ctrlStatement.replay();
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		int actualRowsAffected = template.update(sql);
		assertTrue(
			"Actual rows affected is correct",
			actualRowsAffected == rowsAffected);

		ctrlStatement.verify();
	}

	public void testBatchUpdate() throws Exception {
		final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
		final int[] ids = new int[] { 100, 200 };

		MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
		PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
		mockPreparedStatement.getConnection();
		ctrlPreparedStatement.setReturnValue(mockConnection);
		mockPreparedStatement.setInt(1, ids[0]);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.addBatch();
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setInt(1, ids[1]);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.addBatch();
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeBatch();
		ctrlPreparedStatement.setReturnValue(ids);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
		DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
		mockDatabaseMetaData.getDatabaseProductName();
		ctrlDatabaseMetaData.setReturnValue("MySQL");
		mockDatabaseMetaData.getDriverVersion();
		ctrlDatabaseMetaData.setReturnValue("1.2.3");
		mockDatabaseMetaData.supportsBatchUpdates();
		ctrlDatabaseMetaData.setReturnValue(true);

		mockConnection.prepareStatement(sql);
		ctrlConnection.setReturnValue(mockPreparedStatement);
		mockConnection.getMetaData();
		ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);

		ctrlPreparedStatement.replay();
		ctrlDatabaseMetaData.replay();
		replay();

		BatchPreparedStatementSetter setter =
			new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i)
				throws SQLException {
				ps.setInt(1, ids[i]);
			}
			public int getBatchSize() {
				return ids.length;
			}
		};

		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		int[] actualRowsAffected = template.batchUpdate(sql, setter);
		assertTrue("executed 2 updates", actualRowsAffected.length == 2);

		ctrlPreparedStatement.verify();
		ctrlDatabaseMetaData.verify();
	}

	public void testBatchUpdateWithNoBatchSupport() throws Exception {
		final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
		final int[] ids = new int[] { 100, 200 };

		MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
		PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
		mockPreparedStatement.getConnection();
		ctrlPreparedStatement.setReturnValue(mockConnection);
		mockPreparedStatement.setInt(1, ids[0]);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeUpdate();
		ctrlPreparedStatement.setReturnValue(ids[0]);
		mockPreparedStatement.setInt(1, ids[1]);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeUpdate();
		ctrlPreparedStatement.setReturnValue(ids[1]);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(sql);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		ctrlPreparedStatement.replay();
		replay();

		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, ids[i]);
			}
			public int getBatchSize() {
				return ids.length;
			}
		};

		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		int[] actualRowsAffected = template.batchUpdate(sql, setter);
		assertTrue("executed 2 updates", actualRowsAffected.length == 2);

		ctrlPreparedStatement.verify();
	}

	public void testBatchUpdateFails() throws Exception {
		final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
		final int[] ids = new int[] { 100, 200 };
		SQLException sex = new SQLException();

		MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
		PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
		mockPreparedStatement.getConnection();
		ctrlPreparedStatement.setReturnValue(mockConnection);
		mockPreparedStatement.setInt(1, ids[0]);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.addBatch();
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setInt(1, ids[1]);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.addBatch();
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeBatch();
		ctrlPreparedStatement.setThrowable(sex);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
		DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
		mockDatabaseMetaData.getDatabaseProductName();
		ctrlDatabaseMetaData.setReturnValue("MySQL");
		mockDatabaseMetaData.getDriverVersion();
		ctrlDatabaseMetaData.setReturnValue("1.2.3");
		mockDatabaseMetaData.supportsBatchUpdates();
		ctrlDatabaseMetaData.setReturnValue(true);

		ctrlConnection.reset();
		mockConnection.prepareStatement(sql);
		ctrlConnection.setReturnValue(mockPreparedStatement);
		mockConnection.getMetaData();
		ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
		mockConnection.close();
		ctrlConnection.setVoidCallable(2);

		ctrlPreparedStatement.replay();
		ctrlDatabaseMetaData.replay();
		replay();

		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, ids[i]);
			}
			public int getBatchSize() {
				return ids.length;
			}
		};

		try {
			JdbcTemplate template = new JdbcTemplate(mockDataSource);
			template.batchUpdate(sql, setter);
			fail("Should have failed because of SQLException in bulk update");
		}
		catch (DataAccessException ex) {
			assertTrue("Root cause is SQLException", ex.getCause() == sex);
		}

		ctrlPreparedStatement.verify();
		ctrlDatabaseMetaData.verify();
	}

	public void testCouldntGetConnectionInOperationOrLazilyInstantiatedExceptionTranslator() throws SQLException {
		SQLException sex = new SQLException("foo", "07xxx");

		// Change behaviour in setUp() because we only expect one call to getConnection():
		// none is necessary to get metadata for exception translator
		ctrlDataSource = MockControl.createControl(DataSource.class);
		mockDataSource = (DataSource) ctrlDataSource.getMock();
		mockDataSource.getConnection();
		// Expect two calls (one call after caching data product name): make get Metadata fail also
		ctrlDataSource.setThrowable(sex, 2);
		replay();

		try {
			JdbcTemplate template2 = new JdbcTemplate(mockDataSource);
			RowCountCallbackHandler rcch = new RowCountCallbackHandler();
			template2.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
			fail("Shouldn't have executed query without a connection");
		} 
		catch (CannotGetJdbcConnectionException ex) {
			// pass
			assertTrue("Check root cause", ex.getCause() == sex);
		}
		
		ctrlDataSource.verify();
	}

	/**
	 * Verify that afterPropertiesSet invokes exception translator.
	 */
	public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitialized() throws SQLException {
		SQLException sex = new SQLException("foo", "07xxx");

		ctrlDataSource = MockControl.createControl(DataSource.class);
		mockDataSource = (DataSource) ctrlDataSource.getMock();
		//mockConnection.getMetaData();
		//ctrlConnection.setReturnValue(null, 1);
		//mockConnection.close();
		//ctrlConnection.setVoidCallable(1);
		ctrlConnection.replay();
		
		// Change behaviour in setUp() because we only expect one call to getConnection():
		// none is necessary to get metadata for exception translator
		ctrlDataSource = MockControl.createControl(DataSource.class);
		mockDataSource = (DataSource) ctrlDataSource.getMock();
		// Upfront call for metadata - no longer the case
		//mockDataSource.getConnection();
		//ctrlDataSource.setReturnValue(mockConnection, 1);
		// One call for operation
		mockDataSource.getConnection();
		ctrlDataSource.setThrowable(sex, 2);
		ctrlDataSource.replay();

		try {
			JdbcTemplate template2 = new JdbcTemplate(mockDataSource);
			template2.afterPropertiesSet();
			RowCountCallbackHandler rcch = new RowCountCallbackHandler();
			template2.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
			fail("Shouldn't have executed query without a connection");
		} 
		catch (CannotGetJdbcConnectionException ex) {
			// pass
			assertTrue("Check root cause", ex.getCause() == sex);
		}
	
		ctrlDataSource.verify();
		ctrlConnection.verify();
	}
	
	public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitializedViaBeanProperty()
			throws Exception {
		doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(true);
	}
	
	public void testCouldntGetConnectionInOperationWithExceptionTranslatorInitializedInAfterPropertiesSet()
			throws Exception {
		doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(false);
	}
	
	/**
	 * If beanProperty is true, initialize via exception translator bean property;
	 * if false, use afterPropertiesSet().
	 */
	private void doTestCouldntGetConnectionInOperationWithExceptionTranslatorInitialized(boolean beanProperty)
			throws SQLException {
		SQLException sex = new SQLException("foo", "07xxx");

		ctrlConnection = MockControl.createControl(Connection.class);
		mockConnection = (Connection) ctrlConnection.getMock();
		//mockConnection.getMetaData();
		//ctrlConnection.setReturnValue(null, 1);
		//mockConnection.close();
		//ctrlConnection.setVoidCallable(1);
		ctrlConnection.replay();
	
		// Change behaviour in setUp() because we only expect one call to getConnection():
		// none is necessary to get metadata for exception translator
		ctrlDataSource = MockControl.createControl(DataSource.class);
		mockDataSource = (DataSource) ctrlDataSource.getMock();
		// Upfront call for metadata - no longer the case
		//mockDataSource.getConnection();
		//ctrlDataSource.setReturnValue(mockConnection, 1);
		// One call for operation
		mockDataSource.getConnection();
		ctrlDataSource.setThrowable(sex, 2);
		ctrlDataSource.replay();

		try {
			JdbcTemplate template2 = new JdbcTemplate();
			template2.setDataSource(mockDataSource);
			if (beanProperty) {
				// This will get a connection.
				template2.setExceptionTranslator(new SQLErrorCodeSQLExceptionTranslator(mockDataSource));
			}
			else {
				// This will cause creation of default SQL translator.
				// Note that only call should be effective.
				template2.afterPropertiesSet();
				template2.afterPropertiesSet();
			}
			RowCountCallbackHandler rcch = new RowCountCallbackHandler();
			template2.query(
				"SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3",
				rcch);
			fail("Shouldn't have executed query without a connection");
		} 
		catch (CannotGetJdbcConnectionException ex) {
			// pass
			assertTrue("Check root cause", ex.getCause() == sex);
		}

		ctrlDataSource.verify();
		ctrlConnection.verify();
	}

	public void testPreparedStatementSetterSucceeds() throws Exception {
		final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1";
		final String name = "Gary";
		int expectedRowsUpdated = 1;

		MockControl ctrlPreparedStatement =
			MockControl.createControl(PreparedStatement.class);
		PreparedStatement mockPreparedStatement =
			(PreparedStatement) ctrlPreparedStatement.getMock();
		mockPreparedStatement.setString(1, name);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeUpdate();
		ctrlPreparedStatement.setReturnValue(expectedRowsUpdated);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(sql);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		ctrlPreparedStatement.replay();
		replay();

		PreparedStatementSetter pss = new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, name);
			}
		};
		int actualRowsUpdated =
			new JdbcTemplate(mockDataSource).update(sql, pss);
		assertTrue(
			"updated correct # of rows",
			actualRowsUpdated == expectedRowsUpdated);

		ctrlPreparedStatement.verify();
	}

	public void testPreparedStatementSetterFails() throws Exception {
		final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1";
		final String name = "Gary";
		SQLException sex = new SQLException();

		MockControl ctrlPreparedStatement =
			MockControl.createControl(PreparedStatement.class);
		PreparedStatement mockPreparedStatement =
			(PreparedStatement) ctrlPreparedStatement.getMock();
		mockPreparedStatement.setString(1, name);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeUpdate();
		ctrlPreparedStatement.setThrowable(sex);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(sql);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		ctrlPreparedStatement.replay();
		replay();

		PreparedStatementSetter pss = new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, name);
			}
		};
		try {
			new JdbcTemplate(mockDataSource).update(sql, pss);
			fail("Should have failed with SQLException");
		}
		catch (DataAccessException ex) {
			assertTrue("root cause was preserved", ex.getCause() == sex);
		}