jdbctemplatetests.java

spring的源代码

/*
 * Copyright 2002-2005 the original author or authors.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.jdbc.core;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import javax.sql.DataSource;

import org.easymock.MockControl;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.dao.UncategorizedDataAccessException;
import org.springframework.jdbc.AbstractJdbcTests;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.SQLWarningException;
import org.springframework.jdbc.UncategorizedSQLException;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;
import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractorAdapter;

/** 
 * Mock object based tests for JdbcTemplate.
 *
 * @author Rod Johnson
 * @author Thomas Risberg
 */
public class JdbcTemplateTests extends AbstractJdbcTests {

	public void testBeanProperties() throws Exception {
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		assertTrue("datasource ok", template.getDataSource() == mockDataSource);
		assertTrue("ignores warnings by default", template.isIgnoreWarnings());
		template.setIgnoreWarnings(false);
		assertTrue("can set NOT to ignore warnings", !template.isIgnoreWarnings());
	}

	public void testCannotRunStaticSqlWithBindParameters() throws Exception {
		final String sql = "UPDATE FOO SET NAME='tony' WHERE ID > ?";

		replay();

		JdbcTemplate t = new JdbcTemplate(mockDataSource);
		try {
			t.query(sql, new RowCountCallbackHandler());
			fail("Should have objected to bind variables");
		}
		catch (InvalidDataAccessApiUsageException ex) {
			// OK
		}
	}

	public void testUpdateCount() throws Exception {
		final String sql =
			"UPDATE INVOICE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
		int idParam = 11111;

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

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

		ctrlPreparedStatement.replay();
		replay();

		Dispatcher d = new Dispatcher(idParam, sql);
		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		int rowsAffected = template.update(d);
		assertTrue("1 update affected 1 row", rowsAffected == 1);

		/*
		d = new Dispatcher(idParam);
		rowsAffected = template.update(d);
		assertTrue("bogus update affected 0 rows", rowsAffected == 0);
		*/

		ctrlPreparedStatement.verify();
	}

	public void testBogusUpdate() throws Exception {
		final String sql =
			"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
		final int idParam = 6666;

		// It's because Integers aren't canonical
		SQLException sex = new SQLException("bad update");

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

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

		ctrlPreparedStatement.replay();
		replay();

		Dispatcher d = new Dispatcher(idParam, sql);
		JdbcTemplate template = new JdbcTemplate(mockDataSource);

		try {
			template.update(d);
			fail("Bogus update should throw exception");
		}
		catch (UncategorizedDataAccessException ex) {
			// pass
			assertTrue(
				"Correct exception",
				ex instanceof UncategorizedSQLException);
			assertTrue("Root cause is correct", ex.getCause() == sex);
			//assertTrue("no update occurred", !je.getDataWasUpdated());
		}

		ctrlPreparedStatement.verify();
	}

	public void testStringsWithStaticSql() throws Exception {
		doTestStrings(new JdbcTemplateCallback() {
			public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
				template.query(sql, rch);
			}
		}, false, null, null, null);
	}

	public void testStringsWithStaticSqlAndFetchSizeAndMaxRows() throws Exception {
		doTestStrings(new JdbcTemplateCallback() {
			public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
				template.query(sql, rch);
			}
		}, false, new Integer(10), new Integer(20), null);
	}

	public void testStringsWithEmptyPreparedStatementSetter() throws Exception {
		doTestStrings(new JdbcTemplateCallback() {
			public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
				template.query(sql, (PreparedStatementSetter) null, rch);
			}
		}, true, null, null, null);
	}

	public void testStringsWithPreparedStatementSetter() throws Exception {
		final Integer argument = new Integer(99);
		doTestStrings(new JdbcTemplateCallback() {
			public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
				template.query(sql, new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps) throws SQLException {
						ps.setObject(1, argument);
					}
				}, rch);
			}
		}, true, null, null, argument);
	}

	public void testStringsWithEmptyPreparedStatementArgs() throws Exception {
		doTestStrings(new JdbcTemplateCallback() {
			public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
				template.query(sql, (Object[]) null, rch);
			}
		}, true, null, null, null);
	}

	public void testStringsWithPreparedStatementArgs() throws Exception {
		final Integer argument = new Integer(99);
		doTestStrings(new JdbcTemplateCallback() {
			public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
				template.query(sql, new Object[] {argument}, rch);
			}
		}, true, null, null, argument);
	}

	private void doTestStrings(
			JdbcTemplateCallback jdbcTemplateCallback, boolean usePreparedStatement,
			Integer fetchSize, Integer maxRows, Object argument)
			throws Exception {

		String sql = "SELECT FORENAME FROM CUSTMR";
		String[] results = { "rod", "gary", " portia" };

		class StringHandler implements RowCallbackHandler {
			private List list = new LinkedList();
			public void processRow(ResultSet rs) throws SQLException {
				list.add(rs.getString(1));
			}
			public String[] getStrings() {
				return (String[]) list.toArray(new String[list.size()]);
			}
		}

		MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
		ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(1);
		ctrlResultSet.setReturnValue(results[0]);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(1);
		ctrlResultSet.setReturnValue(results[1]);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(1);
		ctrlResultSet.setReturnValue(results[2]);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
		PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
		if (fetchSize != null) {
			mockStatement.setFetchSize(fetchSize.intValue());
		}
		if (maxRows != null) {
			mockStatement.setMaxRows(maxRows.intValue());
		}
		if (argument != null) {
			mockStatement.setObject(1, argument);
		}
		if (usePreparedStatement) {
			mockStatement.executeQuery();
		}
		else {
			mockStatement.executeQuery(sql);
		}
		ctrlStatement.setReturnValue(mockResultSet);
		mockStatement.getWarnings();
		ctrlStatement.setReturnValue(null);
		mockStatement.close();
		ctrlStatement.setVoidCallable();

		if (usePreparedStatement) {
			mockConnection.prepareStatement(sql);
		}
		else {
			mockConnection.createStatement();
		}
		ctrlConnection.setReturnValue(mockStatement);

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

		StringHandler sh = new StringHandler();
		JdbcTemplate template = new JdbcTemplate();
		template.setDataSource(mockDataSource);
		if (fetchSize != null) {
			template.setFetchSize(fetchSize.intValue());
		}
		if (maxRows != null) {
			template.setMaxRows(maxRows.intValue());
		}
		jdbcTemplateCallback.doInJdbcTemplate(template, sql, sh);

		// Match
		String[] forenames = sh.getStrings();
		assertTrue("same length", forenames.length == results.length);
		for (int i = 0; i < forenames.length; i++) {
			assertTrue("Row " + i + " matches", forenames[i].equals(results[i]));
		}

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

	public void testLeaveConnectionOpenOnRequest() throws Exception {
		String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";

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

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

		mockConnection.isClosed();
		ctrlConnection.setReturnValue(false, 2);
		mockConnection.createStatement();
		ctrlConnection.setReturnValue(mockStatement);
		// if close is called entire test will fail
		mockConnection.close();
		ctrlConnection.setDefaultThrowable(new RuntimeException());

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

		SingleConnectionDataSource scf = new SingleConnectionDataSource(mockDataSource.getConnection(), false);
		JdbcTemplate template2 = new JdbcTemplate(scf, false);
		RowCountCallbackHandler rcch = new RowCountCallbackHandler();
		template2.query(sql, rcch);

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

	public void testConnectionCallback() throws Exception {
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		template.setNativeJdbcExtractor(new PlainNativeJdbcExtractor());
		Object result = template.execute(new ConnectionCallback() {
			public Object doInConnection(Connection con) {
				assertSame(mockConnection, con);
				return "test";
			}
		});

		assertEquals("test", result);
	}

	public void testConnectionCallbackWithStatementSettings() throws Exception {
		MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
		PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
		mockConnection.prepareStatement("some SQL");
		ctrlConnection.setReturnValue(mockStatement, 1);
		mockStatement.setFetchSize(10);
		ctrlStatement.setVoidCallable(1);
		mockStatement.setMaxRows(20);
		ctrlStatement.setVoidCallable(1);
		mockStatement.close();
		ctrlStatement.setVoidCallable(1);
		replay();

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		Object result = template.execute(new ConnectionCallback() {
			public Object doInConnection(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement("some SQL");
				ps.close();
				assertSame(mockConnection, new PlainNativeJdbcExtractor().getNativeConnection(con));
				return "test";
			}
		});

		assertEquals("test", result);
	}

	public void testCloseConnectionOnRequest() throws Exception {
		String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";

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

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

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

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

		JdbcTemplate template = new JdbcTemplate(mockDataSource);
		RowCountCallbackHandler rcch = new RowCountCallbackHandler();
		template.query(sql, rcch);

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

	/**
	 * Test that we see a runtime exception come back.
	 */
	public void testExceptionComesBack() throws Exception {