jdbctemplatetestsuite.java

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

		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		try {
			template.query("my query",	new ResultSetExtractor() {
				public Object extractData(ResultSet rs) throws SQLException {
					throw new InvalidDataAccessApiUsageException("");
				}
			});
			fail("Should have thrown InvalidDataAccessApiUsageException");
		}
		catch (InvalidDataAccessApiUsageException idaauex) {
			// ok
		}

		try {
			template.query(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					return con.prepareStatement("my query");
				}
				public String getSql() {
					return null;
				}
			}, new ResultSetExtractor() {
				public Object extractData(ResultSet rs2) throws SQLException {
					throw new InvalidDataAccessApiUsageException("");
				}
			});
			fail("Should have thrown InvalidDataAccessApiUsageException");
		}
		catch (InvalidDataAccessApiUsageException idaauex) {
			// ok
		}

		// verify confirms if test is successful by checking if close() called
		ctrlResultSet.verify();
		ctrlStatement.verify();
		ctrlResultSet2.verify();
		ctrlPreparedStatement.verify();
	}

	public void testExecuteClosed() throws Exception {
		MockControl ctrlResultSet;
		ResultSet mockResultSet;
		MockControl ctrlCallable;
		CallableStatement mockCallable;

		try {
			ctrlResultSet = MockControl.createControl(ResultSet.class);
			mockResultSet = (ResultSet) ctrlResultSet.getMock();
			mockResultSet.next();
			ctrlResultSet.setReturnValue(true);
			mockResultSet.close();
			ctrlResultSet.setVoidCallable();

			ctrlCallable = MockControl.createControl(CallableStatement.class);
			mockCallable = (CallableStatement) ctrlCallable.getMock();
			mockCallable.execute();
			ctrlCallable.setReturnValue(true);
			mockCallable.getUpdateCount();
			ctrlCallable.setReturnValue(-1);
			mockCallable.getResultSet();
			ctrlCallable.setReturnValue(mockResultSet);
			mockCallable.close();
			ctrlCallable.setVoidCallable();

			mockConnection.prepareCall("my query");
			ctrlConnection.setReturnValue(mockCallable);

		}
		catch (SQLException sex) {
			throw new RuntimeException("EasyMock initialization of jdbc objects failed");
		}

		ctrlResultSet.replay();
		ctrlCallable.replay();
		replay();

		List params = new ArrayList();
		params.add(new SqlReturnResultSet("", new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				throw new InvalidDataAccessApiUsageException("");
			}

		}));

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		try {
			template.call(new CallableStatementCreator() {
				public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
					return conn.prepareCall("my query");
				}
			}, params);
		}
		catch (InvalidDataAccessApiUsageException idaauex) {
			// ok
		}

		// verify confirms if test is successful by checking if close() called
		ctrlResultSet.verify();
		ctrlCallable.verify();
	}

	public void testQueryForList() throws Exception {
		String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";

		MockControl ctrlResultSetMetaData;
		ResultSetMetaData mockResultSetMetaData;
		MockControl ctrlResultSet;
		ResultSet mockResultSet;
		MockControl ctrlStatement;
		Statement mockStatement;

		try {
			ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
			mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
			mockResultSetMetaData.getColumnCount();
			ctrlResultSetMetaData.setReturnValue(1);
			mockResultSetMetaData.getColumnName(1);
			ctrlResultSetMetaData.setReturnValue("age", 2);

			ctrlResultSet = MockControl.createControl(ResultSet.class);
			mockResultSet = (ResultSet) ctrlResultSet.getMock();
			mockResultSet.getMetaData();
			ctrlResultSet.setReturnValue(mockResultSetMetaData);
			mockResultSet.next();
			ctrlResultSet.setReturnValue(true);
			mockResultSet.getObject(1);
			ctrlResultSet.setReturnValue(new Integer(11));
			mockResultSet.next();
			ctrlResultSet.setReturnValue(true);
			mockResultSet.getObject(1);
			ctrlResultSet.setReturnValue(new Integer(12));
			mockResultSet.next();
			ctrlResultSet.setReturnValue(false);
			mockResultSet.close();
			ctrlResultSet.setVoidCallable();

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

			mockConnection.createStatement();
			ctrlConnection.setReturnValue(mockStatement);
		}
		catch (SQLException sex) {
			throw new RuntimeException("EasyMock initialization of jdbc objects failed");
		}

		ctrlResultSetMetaData.replay();
		ctrlResultSet.replay();
		ctrlStatement.replay();
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		List li = template.queryForList(sql);
		assertEquals("All rows returned", 2, li.size());
		assertEquals("First row is Integer", 11, ((Integer)((Map)li.get(0)).get("age")).intValue());
		assertEquals("Second row is Integer", 12, ((Integer)((Map)li.get(1)).get("age")).intValue());

		ctrlResultSet.verify();
		ctrlStatement.verify();
	}

	public void testQueryForListWithSingleRowAndColumn() throws Exception {
		String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3";

		MockControl ctrlResultSetMetaData;
		ResultSetMetaData mockResultSetMetaData;
		MockControl ctrlResultSet;
		ResultSet mockResultSet;
		MockControl ctrlStatement;
		Statement mockStatement;

		try {
			ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
			mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
			mockResultSetMetaData.getColumnCount();
			ctrlResultSetMetaData.setReturnValue(1);
			mockResultSetMetaData.getColumnName(1);
			ctrlResultSetMetaData.setReturnValue("age", 2);

			ctrlResultSet = MockControl.createControl(ResultSet.class);
			mockResultSet = (ResultSet) ctrlResultSet.getMock();
			mockResultSet.getMetaData();
			ctrlResultSet.setReturnValue(mockResultSetMetaData);
			mockResultSet.next();
			ctrlResultSet.setReturnValue(true);
			mockResultSet.getObject(1);
			ctrlResultSet.setReturnValue(new Integer(11));
			mockResultSet.next();
			ctrlResultSet.setReturnValue(false);
			mockResultSet.close();
			ctrlResultSet.setVoidCallable();

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

			mockConnection.createStatement();
			ctrlConnection.setReturnValue(mockStatement);
		}
		catch (SQLException sex) {
			throw new RuntimeException("EasyMock initialization of jdbc objects failed");
		}

		ctrlResultSetMetaData.replay();
		ctrlResultSet.replay();
		ctrlStatement.replay();
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		List li = template.queryForList(sql);
		assertEquals("All rows returned", 1, li.size());
		assertEquals("First row is Integer", 11, ((Integer)((Map)li.get(0)).get("age")).intValue());

		ctrlResultSet.verify();
		ctrlStatement.verify();
	}

	public void testQueryForObject() throws Exception {
		String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";

		MockControl ctrlResultSetMetaData;
		ResultSetMetaData mockResultSetMetaData;
		MockControl ctrlResultSet;
		ResultSet mockResultSet;
		MockControl ctrlStatement;
		Statement mockStatement;

		try {
			ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
			mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
			mockResultSetMetaData.getColumnCount();
			ctrlResultSetMetaData.setReturnValue(1);

			ctrlResultSet = MockControl.createControl(ResultSet.class);
			mockResultSet = (ResultSet) ctrlResultSet.getMock();
			mockResultSet.getMetaData();
			ctrlResultSet.setReturnValue(mockResultSetMetaData);
			mockResultSet.next();
			ctrlResultSet.setReturnValue(true);
			mockResultSet.getObject(1);
			ctrlResultSet.setReturnValue(new Integer(22));
			mockResultSet.next();
			ctrlResultSet.setReturnValue(false);
			mockResultSet.close();
			ctrlResultSet.setVoidCallable();

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

			mockConnection.createStatement();
			ctrlConnection.setReturnValue(mockStatement);
		}
		catch (SQLException sex) {
			throw new RuntimeException("EasyMock initialization of jdbc objects failed");
		}

		ctrlResultSetMetaData.replay();
		ctrlResultSet.replay();
		ctrlStatement.replay();
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		Object o = template.queryForObject(sql, Integer.class);
		assertEquals("Return of an object", "java.lang.Integer", o.getClass().getName());

		ctrlResultSet.verify();
		ctrlStatement.verify();
	}

	public void testQueryForInt() throws Exception {
		String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";

		MockControl ctrlResultSetMetaData;
		ResultSetMetaData mockResultSetMetaData;
		MockControl ctrlResultSet;
		ResultSet mockResultSet;
		MockControl ctrlStatement;
		Statement mockStatement;

		try {
			ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
			mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
			mockResultSetMetaData.getColumnCount();
			ctrlResultSetMetaData.setReturnValue(1);
			
			ctrlResultSet = MockControl.createControl(ResultSet.class);
			mockResultSet = (ResultSet) ctrlResultSet.getMock();
			mockResultSet.getMetaData();
			ctrlResultSet.setReturnValue(mockResultSetMetaData);
			mockResultSet.next();
			ctrlResultSet.setReturnValue(true);
			mockResultSet.getObject(1);
			ctrlResultSet.setReturnValue(new Integer(22));
			mockResultSet.next();
			ctrlResultSet.setReturnValue(false);
			mockResultSet.close();
			ctrlResultSet.setVoidCallable();

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

			mockConnection.createStatement();
			ctrlConnection.setReturnValue(mockStatement);
		}
		catch (SQLException sex) {
			throw new RuntimeException("EasyMock initialization of jdbc objects failed");
		}

		ctrlResultSetMetaData.replay();
		ctrlResultSet.replay();
		ctrlStatement.replay();
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		int i = template.queryForInt(sql);
		assertEquals("Return of an int", 22, i);

		ctrlResultSet.verify();
		ctrlStatement.verify();
	}

	public void testQueryForLong() throws Exception {
		String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3";

		MockControl ctrlResultSetMetaData;
		ResultSetMetaData mockResultSetMetaData;
		MockControl ctrlResultSet;
		ResultSet mockResultSet;
		MockControl ctrlStatement;
		Statement mockStatement;

		try {
			ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
			mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
			mockResultSetMetaData.getColumnCount();
			ctrlResultSetMetaData.setReturnValue(1);
			
			ctrlResultSet = MockControl.createControl(ResultSet.class);
			mockResultSet = (ResultSet) ctrlResultSet.getMock();
			mockResultSet.getMetaData();
			ctrlResultSet.setReturnValue(mockResultSetMetaData);
			mockResultSet.next();
			ctrlResultSet.setReturnValue(true);
			mockResultSet.getObject(1);
			ctrlResultSet.setReturnValue(new java.math.BigDecimal(87));
			mockResultSet.next();
			ctrlResultSet.setReturnValue(false);
			mockResultSet.close();
			ctrlResultSet.setVoidCallable();

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

			mockConnection.createStatement();
			ctrlConnection.setReturnValue(mockStatement);
		}
		catch (SQLException sex) {
			throw new RuntimeException("EasyMock initialization of jdbc objects failed");
		}

		ctrlResultSetMetaData.replay();
		ctrlResultSet.replay();
		ctrlStatement.replay();
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		long l = template.queryForLong(sql);
		assertEquals("Return of a long", 87, l);

		ctrlResultSet.verify();
		ctrlStatement.verify();
	}


	private static interface JdbcTemplateCallback {

		void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch);
	}


	private static class Dispatcher implements PreparedStatementCreator, SqlProvider {

		private int id;
		private String sql;

		public Dispatcher(int id, String sql) {
			this.id = id;
			this.sql = sql;
		}

		public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			return ps;
		}

		public String getSql() {
			return sql;
		}
	}

}