alvinalexander.com | career | drupal | java | mac | mysql | perl | scala | uml | unix  

Spring Framework example source code file (SqlQueryTests.java)

This example Spring Framework source code file (SqlQueryTests.java) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.

Java - Spring Framework tags/keywords

customer, customer, customerquery, customerquery, integer, jdbc, list, mappingsqlquery, mockcontrol, object, sql, sqlexception, sqlexception, sqlparameter, string, string, util

The Spring Framework SqlQueryTests.java source code

/*
 * Copyright 2002-2007 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.object;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.easymock.MockControl;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.AbstractJdbcTests;
import org.springframework.jdbc.Customer;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;

/**
 * @author Trevor Cook
 * @author Thomas Risberg
 * @author Juergen Hoeller
 */
public class SqlQueryTests extends AbstractJdbcTests {

	private static final String SELECT_ID = "select id from custmr";
	private static final String SELECT_ID_WHERE =
		"select id from custmr where forename = ? and id = ?";
	private static final String SELECT_FORENAME = "select forename from custmr";
	private static final String SELECT_FORENAME_EMPTY =
		"select forename from custmr WHERE 1 = 2";
	private static final String SELECT_ID_FORENAME_WHERE =
		"select id, forename from prefix:custmr where forename = ?";
	private static final String SELECT_ID_FORENAME_NAMED_PARAMETERS =
		"select id, forename from custmr where id = :id and country = :country";
	private static final String SELECT_ID_FORENAME_NAMED_PARAMETERS_PARSED =
		"select id, forename from custmr where id = ? and country = ?";
	private static final String SELECT_ID_FORENAME_WHERE_ID_IN_LIST_1 =
		"select id, forename from custmr where id in (?, ?)";
	private static final String SELECT_ID_FORENAME_WHERE_ID_IN_LIST_2 =
		"select id, forename from custmr where id in (:ids)";
	private static final String SELECT_ID_FORENAME_WHERE_ID_REUSED_1 =
		"select id, forename from custmr where id = ? or id = ?)";
	private static final String SELECT_ID_FORENAME_WHERE_ID_REUSED_2 =
		"select id, forename from custmr where id = :id1 or id = :id1)";
	private static final String SELECT_ID_FORENAME_WHERE_ID =
		"select id, forename from custmr where id <= ?";

	private static final String[] COLUMN_NAMES = new String[] {"id", "forename"};
	private static final int[] COLUMN_TYPES = new int[] {Types.INTEGER, Types.VARCHAR};

	private MockControl ctrlPreparedStatement;
	private PreparedStatement mockPreparedStatement;
	private MockControl ctrlResultSet;
	private ResultSet mockResultSet;

	protected void setUp() throws Exception {
		super.setUp();
		ctrlPreparedStatement =	MockControl.createControl(PreparedStatement.class);
		mockPreparedStatement =	(PreparedStatement) ctrlPreparedStatement.getMock();
		ctrlResultSet = MockControl.createControl(ResultSet.class);
		mockResultSet = (ResultSet) ctrlResultSet.getMock();
	}

	protected void tearDown() throws Exception {
		super.tearDown();
		if (shouldVerify()) {
			ctrlPreparedStatement.verify();
			ctrlResultSet.verify();
		}
	}

	protected void replay() {
		super.replay();
		ctrlPreparedStatement.replay();
		ctrlResultSet.replay();
	}


	public void testQueryWithoutParams() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt(1);
		ctrlResultSet.setReturnValue(1);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		replay();

		SqlQuery query = new MappingSqlQueryWithParameters() {
			protected Object mapRow(ResultSet rs, int rownum, Object[] params, Map context) throws SQLException {
				assertTrue("params were null", params == null);
				assertTrue("context was null", context == null);
				return new Integer(rs.getInt(1));
			}
		};

		query.setDataSource(mockDataSource);
		query.setSql(SELECT_ID);
		query.compile();
		List list = query.execute();
		assertTrue("Found customers", list.size() != 0);
		for (Iterator itr = list.iterator(); itr.hasNext();) {
			Integer id = (Integer) itr.next();
			assertTrue(
				"Customer id was assigned correctly",
				id.intValue() == 1);
		}
	}

	public void testQueryWithoutEnoughParams() {
		replay();

		MappingSqlQuery query = new MappingSqlQuery() {
			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				return new Integer(rs.getInt(1));
			}

		};
		query.setDataSource(mockDataSource);
		query.setSql(SELECT_ID_WHERE);
		query.declareParameter(
			new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
		query.declareParameter(
			new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
		query.compile();

		try {
			List list = query.execute();
			fail("Shouldn't succeed in running query without enough params");
		}
		catch (InvalidDataAccessApiUsageException ex) {
			// OK
		}
	}

	public void testQueryWithMissingMapParams() {
		replay();

		MappingSqlQuery query = new MappingSqlQuery() {
			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				return new Integer(rs.getInt(1));
			}
		};
		query.setDataSource(mockDataSource);
		query.setSql(SELECT_ID_WHERE);
		query.declareParameter(
			new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
		query.declareParameter(
			new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
		query.compile();

		try {
			Map params = new HashMap();
			params.put(COLUMN_NAMES[0], "Value");
			List list = query.executeByNamedParam(params);
			fail("Shouldn't succeed in running query with missing params");
		}
		catch (InvalidDataAccessApiUsageException ex) {
			// OK
		}
	}

	public void testStringQueryWithResults() throws Exception {
		String[] dbResults = new String[] { "alpha", "beta", "charlie" };

		MockControl[] ctrlCountResultSetMetaData = new MockControl[3];
		ResultSetMetaData[] mockCountResultSetMetaData = new ResultSetMetaData[3];
		MockControl[] ctrlCountResultSet = new MockControl[3];
		ResultSet[] mockCountResultSet = new ResultSet[3];
		MockControl[] ctrlCountPreparedStatement = new MockControl[3];
		PreparedStatement[] mockCountPreparedStatement = new PreparedStatement[3];

		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(1);
		ctrlResultSet.setReturnValue(dbResults[0]);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(1);
		ctrlResultSet.setReturnValue(dbResults[1]);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(1);
		ctrlResultSet.setReturnValue(dbResults[2]);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		for (int i = 0; i < dbResults.length; i++) {
			ctrlCountResultSetMetaData[i] = MockControl.createControl(ResultSetMetaData.class);
			mockCountResultSetMetaData[i] = (ResultSetMetaData) ctrlCountResultSetMetaData[i].getMock();
			mockCountResultSetMetaData[i].getColumnCount();
			ctrlCountResultSetMetaData[i].setReturnValue(1);

			ctrlCountResultSet[i] = MockControl.createControl(ResultSet.class);
			mockCountResultSet[i] = (ResultSet) ctrlCountResultSet[i].getMock();
			mockCountResultSet[i].getMetaData();
			ctrlCountResultSet[i].setReturnValue(mockCountResultSetMetaData[i]);
			mockCountResultSet[i].next();
			ctrlCountResultSet[i].setReturnValue(true);
			mockCountResultSet[i].getInt(1);
			ctrlCountResultSet[i].setReturnValue(1);
			mockCountResultSet[i].wasNull();
			ctrlCountResultSet[i].setReturnValue(false);
			mockCountResultSet[i].next();
			ctrlCountResultSet[i].setReturnValue(false);
			mockCountResultSet[i].close();
			ctrlCountResultSet[i].setVoidCallable();

			ctrlCountPreparedStatement[i] = MockControl.createControl(PreparedStatement.class);
			mockCountPreparedStatement[i] = (PreparedStatement) ctrlCountPreparedStatement[i].getMock();
			mockCountPreparedStatement[i].executeQuery();
			ctrlCountPreparedStatement[i].setReturnValue(mockCountResultSet[i]);
			mockCountPreparedStatement[i].getWarnings();
			ctrlCountPreparedStatement[i].setReturnValue(null);
			mockCountPreparedStatement[i].close();
			ctrlCountPreparedStatement[i].setVoidCallable();

			mockConnection.prepareStatement(
				"SELECT COUNT(FORENAME) FROM CUSTMR WHERE FORENAME='" + dbResults[i] + "'");
			ctrlConnection.setReturnValue(mockCountPreparedStatement[i]);

			ctrlCountResultSetMetaData[i].replay();
			ctrlCountResultSet[i].replay();
			ctrlCountPreparedStatement[i].replay();
		}

		replay();

		StringQuery query = new StringQuery(mockDataSource, SELECT_FORENAME);
		query.setRowsExpected(3);
		String[] results = query.run();
		assertTrue("Array is non null", results != null);
		assertTrue("Found results", results.length > 0);
		assertTrue(
			"Found expected number of results",
			query.getRowsExpected() == 3);

		JdbcTemplate helper = new JdbcTemplate(mockDataSource);
		for (int i = 0; i < results.length; i++) {
			// BREAKS ON ' in name
			int dbCount = helper.queryForInt(
					"SELECT COUNT(FORENAME) FROM CUSTMR WHERE FORENAME='" + results[i] + "'", (Object[]) null);
			assertTrue("found in db", dbCount == 1);
		}

		for (int i = 0; i < dbResults.length; i++) {
			ctrlCountResultSetMetaData[i].verify();
			ctrlCountResultSet[i].verify();
			ctrlCountPreparedStatement[i].verify();
		}
	}

	public void testStringQueryWithoutResults() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		replay();

		StringQuery query =	new StringQuery(mockDataSource, SELECT_FORENAME_EMPTY);
		String[] results = query.run();
		assertTrue("Array is non null", results != null);
		assertTrue("Found 0 results", results.length == 0);
	}

	public void testFindCustomerIntInt() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setObject(2, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_WHERE);
				declareParameter(new SqlParameter(Types.NUMERIC));
				declareParameter(new SqlParameter(Types.NUMERIC));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public Customer findCustomer(int id, int otherNum) {
				return (Customer) findObject(id, otherNum);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		Customer cust = query.findCustomer(1, 1);

		assertTrue("Customer id was assigned correctly", cust.getId() == 1);
		assertTrue(
			"Customer forename was assigned correctly",
			cust.getForename().equals("rod"));
	}

	public void testFindCustomerString() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setString(1, "rod");
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE);
				declareParameter(new SqlParameter(Types.VARCHAR));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public Customer findCustomer(String id) {
				return (Customer) findObject(id);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		Customer cust = query.findCustomer("rod");

		assertTrue("Customer id was assigned correctly", cust.getId() == 1);
		assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
	}

	public void testFindCustomerMixed() throws SQLException {
		MockControl ctrlResultSet2;
		ResultSet mockResultSet2;
		MockControl ctrlPreparedStatement2;
		PreparedStatement mockPreparedStatement2;

		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(1), Types.INTEGER);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setString(2, "rod");
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		ctrlResultSet2 = MockControl.createControl(ResultSet.class);
		mockResultSet2 = (ResultSet) ctrlResultSet2.getMock();
		mockResultSet2.next();
		ctrlResultSet2.setReturnValue(false);
		mockResultSet2.close();
		ctrlResultSet2.setVoidCallable();

		ctrlPreparedStatement2 = MockControl.createControl(PreparedStatement.class);
		mockPreparedStatement2 = (PreparedStatement) ctrlPreparedStatement2.getMock();
		mockPreparedStatement2.setObject(1, new Integer(1), Types.INTEGER);
		ctrlPreparedStatement2.setVoidCallable();
		mockPreparedStatement2.setString(2, "Roger");
		ctrlPreparedStatement2.setVoidCallable();
		mockPreparedStatement2.executeQuery();
		ctrlPreparedStatement2.setReturnValue(mockResultSet2);
		mockPreparedStatement2.getWarnings();
		ctrlPreparedStatement2.setReturnValue(null);
		mockPreparedStatement2.close();
		ctrlPreparedStatement2.setVoidCallable();

		mockConnection.prepareStatement(SELECT_ID_WHERE);
		ctrlConnection.setReturnValue(mockPreparedStatement);
		mockConnection.prepareStatement(SELECT_ID_WHERE);
		ctrlConnection.setReturnValue(mockPreparedStatement2);

		ctrlResultSet2.replay();
		ctrlPreparedStatement2.replay();
		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_WHERE);
				declareParameter(
					new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
				declareParameter(
					new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public Customer findCustomer(int id, String name) {
				return (Customer) findObject(
					new Object[] { new Integer(id), name });
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);

		Customer cust1 = query.findCustomer(1, "rod");
		assertTrue("Found customer", cust1 != null);
		assertTrue("Customer id was assigned correctly", cust1.getId() == 1);

		Customer cust2 = query.findCustomer(1, "Roger");
		assertTrue("No customer found", cust2 == null);
	}

	public void testFindTooManyCustomers() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(2);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setString(1, "rod");
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE);
				declareParameter(new SqlParameter(Types.VARCHAR));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public Customer findCustomer(String id) {
				return (Customer) findObject(id);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		try {
			Customer cust = query.findCustomer("rod");
			fail("Should fail if more than one row found");
		}
		catch (IncorrectResultSizeDataAccessException ex) {
			// OK
		}
	}

	public void testListCustomersIntInt() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(2);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("dave");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setObject(2, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_WHERE);
				declareParameter(new SqlParameter(Types.NUMERIC));
				declareParameter(new SqlParameter(Types.NUMERIC));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		List list = query.execute(1, 1);
		assertTrue("2 results in list", list.size() == 2);
		for (Iterator itr = list.iterator(); itr.hasNext();) {
			Customer cust = (Customer) itr.next();
		}
	}

	public void testListCustomersString() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(2);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("dave");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setString(1, "one");
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

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

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE);
				declareParameter(new SqlParameter(Types.VARCHAR));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		List list = query.execute("one");
		assertTrue("2 results in list", list.size() == 2);
		for (Iterator itr = list.iterator(); itr.hasNext();) {
			Customer cust = (Customer) itr.next();
		}
	}

	public void testFancyCustomerQuery() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(
				SELECT_ID_FORENAME_WHERE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE);
				setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
				declareParameter(new SqlParameter(Types.NUMERIC));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public Customer findCustomer(int id) {
				return (Customer) findObject(id);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		Customer cust = query.findCustomer(1);
		assertTrue("Customer id was assigned correctly", cust.getId() == 1);
		assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
	}

	public void testUnnamedParameterDeclarationWithNamedParameterQuery() throws SQLException {
		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE);
				setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
				declareParameter(new SqlParameter(Types.NUMERIC));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public Customer findCustomer(int id) {
				Map params = new HashMap();
				params.put("id", new Integer(id));
				return (Customer) executeByNamedParam(params).get(0);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		try {
			Customer cust = query.findCustomer(1);
			fail("Query should not succeed since parameter declaration did not specify parameter name");
		}
		catch (InvalidDataAccessApiUsageException ex) {
			// OK - it worked
		}
	}

	public void testNamedParameterCustomerQueryWithUnnamedDeclarations() throws SQLException {
		doTestNamedParameterCustomerQuery(false);
	}

	public void testNamedParameterCustomerQueryWithNamedDeclarations() throws SQLException {
		doTestNamedParameterCustomerQuery(true);
	}

	private void doTestNamedParameterCustomerQuery(final boolean namedDeclarations) throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setString(2, "UK");
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(
				SELECT_ID_FORENAME_NAMED_PARAMETERS_PARSED, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_NAMED_PARAMETERS);
				setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
				if (namedDeclarations) {
					declareParameter(new SqlParameter("country", Types.VARCHAR));
					declareParameter(new SqlParameter("id", Types.NUMERIC));
				}
				else {
					declareParameter(new SqlParameter(Types.NUMERIC));
					declareParameter(new SqlParameter(Types.VARCHAR));
				}
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public Customer findCustomer(int id, String country) {
				Map params = new HashMap();
				params.put("id", new Integer(id));
				params.put("country", country);
				return (Customer) executeByNamedParam(params).get(0);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		Customer cust = query.findCustomer(1, "UK");
		assertTrue("Customer id was assigned correctly", cust.getId() == 1);
		assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
	}

	public void testNamedParameterInListQuery() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(2);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("juergen");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setObject(2, new Integer(2), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(
				SELECT_ID_FORENAME_WHERE_ID_IN_LIST_1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE_ID_IN_LIST_2);
				setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
				declareParameter(new SqlParameter("ids", Types.NUMERIC));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public List findCustomers(List ids) {
				Map params = new HashMap();
				params.put("ids", ids);
				return (List) executeByNamedParam(params);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		List ids = new ArrayList();
		ids.add(new Integer(1));
		ids.add(new Integer(2));
		List cust = query.findCustomers(ids);

		assertEquals("We got two customers back", cust.size(), 2);
		assertEquals("First customer id was assigned correctly", ((Customer)cust.get(0)).getId(), 1);
		assertEquals("First customer forename was assigned correctly", ((Customer)cust.get(0)).getForename(), "rod");
		assertEquals("Second customer id was assigned correctly", ((Customer)cust.get(1)).getId(), 2);
		assertEquals("Second customer forename was assigned correctly", ((Customer)cust.get(1)).getForename(), "juergen");
	}
	
	public void testNamedParameterQueryReusingParameter() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("rod");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(2);
		mockResultSet.getString("forename");
		ctrlResultSet.setReturnValue("juergen");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.setObject(2, new Integer(1), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(
				SELECT_ID_FORENAME_WHERE_ID_REUSED_1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE_ID_REUSED_2);
				setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
				declareParameter(new SqlParameter("id1", Types.NUMERIC));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public List findCustomers(Integer id) {
				Map params = new HashMap();
				params.put("id1", id);
				return (List) executeByNamedParam(params);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		List cust = query.findCustomers(new Integer(1));

		assertEquals("We got two customers back", cust.size(), 2);
		assertEquals("First customer id was assigned correctly", ((Customer)cust.get(0)).getId(), 1);
		assertEquals("First customer forename was assigned correctly", ((Customer)cust.get(0)).getForename(), "rod");
		assertEquals("Second customer id was assigned correctly", ((Customer)cust.get(1)).getId(), 2);
		assertEquals("Second customer forename was assigned correctly", ((Customer)cust.get(1)).getForename(), "juergen");
	}

	public void testNamedParameterUsingInvalidQuestionMarkPlaceHolders() throws SQLException {

		mockConnection.prepareStatement(
				SELECT_ID_FORENAME_WHERE_ID_REUSED_1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		replay();

		class CustomerQuery extends MappingSqlQuery {

			public CustomerQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE_ID_REUSED_1);
				setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
				declareParameter(new SqlParameter("id1", Types.NUMERIC));
				compile();
			}

			protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			}

			public List findCustomers(Integer id1) {
				Map params = new HashMap();
				params.put("id1", id1);
				return (List) executeByNamedParam(params);
			}
		}

		CustomerQuery query = new CustomerQuery(mockDataSource);
		try {
			List cust = query.findCustomers(new Integer(1));
			fail("Should have caused an InvalidDataAccessApiUsageException");
		}
		catch (InvalidDataAccessApiUsageException e){
		}

	}

	public void testUpdateCustomers() throws SQLException {
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(1);
		mockResultSet.updateString(2, "Rod");
		ctrlResultSet.setVoidCallable();
		mockResultSet.updateRow();
		ctrlResultSet.setVoidCallable();
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getInt("id");
		ctrlResultSet.setReturnValue(2);
		mockResultSet.updateString(2, "Thomas");
		ctrlResultSet.setVoidCallable();
		mockResultSet.updateRow();
		ctrlResultSet.setVoidCallable();
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockPreparedStatement.setObject(1, new Integer(2), Types.NUMERIC);
		ctrlPreparedStatement.setVoidCallable();
		mockPreparedStatement.executeQuery();
		ctrlPreparedStatement.setReturnValue(mockResultSet);
		mockPreparedStatement.getWarnings();
		ctrlPreparedStatement.setReturnValue(null);
		mockPreparedStatement.close();
		ctrlPreparedStatement.setVoidCallable();

		mockConnection.prepareStatement(
				SELECT_ID_FORENAME_WHERE_ID, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		ctrlConnection.setReturnValue(mockPreparedStatement);

		replay();

		class CustomerUpdateQuery extends UpdatableSqlQuery {

			public CustomerUpdateQuery(DataSource ds) {
				super(ds, SELECT_ID_FORENAME_WHERE_ID);
				declareParameter(new SqlParameter(Types.NUMERIC));
				compile();
			}

			protected Object updateRow(ResultSet rs, int rownum, Map context) throws SQLException {
				rs.updateString(2, "" + context.get(new Integer(rs.getInt(COLUMN_NAMES[0]))));
				return null;
			}
		}
		CustomerUpdateQuery query = new CustomerUpdateQuery(mockDataSource);
		Map values = new HashMap(2);
		values.put(new Integer(1), "Rod");
		values.put(new Integer(2), "Thomas");
		List customers = query.execute(2, values);
	}


	private static class StringQuery extends MappingSqlQuery {

		public StringQuery(DataSource ds, String sql) {
			super(ds, sql);
			compile();
		}

		protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
			return rs.getString(1);
		}

		public String[] run() {
			List list = execute();
			String[] results = (String[]) list.toArray(new String[list.size()]);
			return results;
		}
	}

}

Other Spring Framework examples (source code examples)

Here is a short list of links related to this Spring Framework SqlQueryTests.java source code file:
... this post is sponsored by my books ...

#1 New Release!
FP Best Seller

 

new blog posts

 

Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.

A percentage of advertising revenue from
pages under the /java/jwarehouse URI on this website is
paid back to open source projects.