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

Spring Framework example source code file (StoredProcedureTests.java)

This example Spring Framework source code file (StoredProcedureTests.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

exception, exception, hashmap, integer, integer, jdbc, list, map, map, math, resultset, sql, sql, storedprocedure, storedprocedurewithresultsetmapped, string, string, util

The Spring Framework StoredProcedureTests.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.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.math.BigDecimal;

import javax.sql.DataSource;

import org.easymock.MockControl;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.AbstractJdbcTests;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SimpleRowCountCallbackHandler;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
import org.springframework.jdbc.datasource.ConnectionHolder;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;
import org.springframework.transaction.support.TransactionSynchronizationManager;

/**
 * @author Thomas Risberg
 * @author Trevor Cook
 * @author Rod Johnson
 */
public class StoredProcedureTests extends AbstractJdbcTests {

	private MockControl ctrlCallable;
	private CallableStatement mockCallable;

	protected void setUp() throws Exception {
		super.setUp();

		ctrlCallable = MockControl.createControl(CallableStatement.class);
		mockCallable = (CallableStatement) ctrlCallable.getMock();
	}

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

	protected void replay() {
		super.replay();
		ctrlCallable.replay();
	}

	public void testNoSuchStoredProcedure() throws Exception {
		SQLException sex =
			new SQLException(
				"Syntax error or access violation exception",
				"42000");
		mockCallable.execute();
		ctrlCallable.setThrowable(sex);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall(
			"{call " + NoSuchStoredProcedure.SQL + "()}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();

		NoSuchStoredProcedure sproc = new NoSuchStoredProcedure(mockDataSource);
		try {
			sproc.execute();
			fail("Shouldn't succeed in running stored procedure which doesn't exist");
		} catch (BadSqlGrammarException ex) {
			// OK
		}
	}

	private void testAddInvoice(final int amount, final int custid)
		throws Exception {
		AddInvoice adder = new AddInvoice(mockDataSource);
		int id = adder.execute(amount, custid);
		assertEquals(4, id);
	}

	public void testAddInvoices() throws Exception {
		mockCallable.setObject(1, new Integer(1106), Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.setObject(2, new Integer(3), Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.registerOutParameter(3, Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.execute();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getObject(3);
		ctrlCallable.setReturnValue(new Integer(4));
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + AddInvoice.SQL + "(?, ?, ?)}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();

		testAddInvoice(1106, 3);
	}

	public void testAddInvoicesWithinTransaction() throws Exception {
		mockCallable.setObject(1, new Integer(1106), Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.setObject(2, new Integer(3), Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.registerOutParameter(3, Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.execute();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getObject(3);
		ctrlCallable.setReturnValue(new Integer(4));
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + AddInvoice.SQL + "(?, ?, ?)}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();

		TransactionSynchronizationManager.bindResource(
			mockDataSource,
			new ConnectionHolder(mockConnection));

		try {
			testAddInvoice(1106, 3);
		}
		finally {
			TransactionSynchronizationManager.unbindResource(mockDataSource);
		}
	}

		
	/**
	 * Confirm no connection was used to get metadata.
	 * Does not use superclass replay mechanism.
	 * @throws Exception
	 */
	public void testStoredProcedureConfiguredViaJdbcTemplateWithCustomExceptionTranslator() throws Exception {					
		mockCallable.setObject(1, new Integer(11), Types.INTEGER);
		ctrlCallable.setVoidCallable(1);
		mockCallable.registerOutParameter(2, Types.INTEGER);
		ctrlCallable.setVoidCallable(1);
		mockCallable.execute();
		ctrlCallable.setReturnValue(false, 1);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getObject(2);
		ctrlCallable.setReturnValue(new Integer(5), 1);
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable(1);
		// Must call this here as we're not using setUp()/tearDown() mechanism
		ctrlCallable.replay();

		ctrlConnection = MockControl.createControl(Connection.class);
		mockConnection = (Connection) ctrlConnection.getMock();
		mockConnection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}");
		ctrlConnection.setReturnValue(mockCallable, 1);
		mockConnection.close();
		ctrlConnection.setVoidCallable(1);
		ctrlConnection.replay();
		
		MockControl dsControl = MockControl.createControl(DataSource.class);
		DataSource localDs = (DataSource) dsControl.getMock();
		localDs.getConnection();
		dsControl.setReturnValue(mockConnection, 1);
		dsControl.replay();

		class TestJdbcTemplate extends JdbcTemplate {
			int calls;
			public Map call(CallableStatementCreator csc, List declaredParameters) throws DataAccessException {
				calls++;
				return super.call(csc, declaredParameters);
			}

		}
		TestJdbcTemplate t = new TestJdbcTemplate();
		t.setDataSource(localDs);
		// Will fail without the following, because we're not able to get a connection from the
		// DataSource here if we need to to create an ExceptionTranslator
		t.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
		StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);
		
		assertEquals(sp.execute(11), 5);
		assertEquals(1, t.calls);
		
		dsControl.verify();
		ctrlCallable.verify();
		ctrlConnection.verify();
	}
	
	/**
	 * Confirm our JdbcTemplate is used
	 * @throws Exception
	 */
	public void testStoredProcedureConfiguredViaJdbcTemplate() throws Exception {
		mockCallable.setObject(1, new Integer(1106), Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.registerOutParameter(2, Types.INTEGER);
		ctrlCallable.setVoidCallable();
		mockCallable.execute();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getObject(2);
		ctrlCallable.setReturnValue(new Integer(4));
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();	
		JdbcTemplate t = new JdbcTemplate();
		t.setDataSource(mockDataSource);
		StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);
	
		assertEquals(sp.execute(1106), 4);
	}

	public void testNullArg() throws Exception {
		MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
		ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);

		mockCallable.setNull(1, Types.VARCHAR);
		ctrlCallable.setVoidCallable();
		mockCallable.execute();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + NullArg.SQL + "(?)}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		ctrlResultSet.replay();

		NullArg na = new NullArg(mockDataSource);
		na.execute((String) null);
	}

	public void testUnnamedParameter() throws Exception {
		replay();
		try {
			UnnamedParameterStoredProcedure unp =
				new UnnamedParameterStoredProcedure(mockDataSource);
			fail("Shouldn't succeed in creating stored procedure with unnamed parameter");
		} catch (InvalidDataAccessApiUsageException idaauex) {
			// OK
		}
	}

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

		try {
			MissingParameterStoredProcedure mp =
				new MissingParameterStoredProcedure(mockDataSource);
			mp.execute();
			fail("Shouldn't succeed in running stored procedure with missing required parameter");
		} catch (InvalidDataAccessApiUsageException idaauex) {
			// OK
		}
	}

	public void testStoredProcedureExceptionTranslator() throws Exception {
		SQLException sex =
			new SQLException(
				"Syntax error or access violation exception",
				"42000");
		mockCallable.execute();
		ctrlCallable.setThrowable(sex);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall(
			"{call " + StoredProcedureExceptionTranslator.SQL + "()}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();

		StoredProcedureExceptionTranslator sproc =
			new StoredProcedureExceptionTranslator(mockDataSource);
		try {
			sproc.execute();
			fail("Custom exception should be thrown");
		} catch (CustomDataException ex) {
			// OK
		}
	}

	public void testStoredProcedureWithResultSet() throws Exception {
		MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
		ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockCallable.execute();
		ctrlCallable.setReturnValue(true);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getResultSet();
		ctrlCallable.setReturnValue(mockResultSet);
		mockCallable.getMoreResults();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + StoredProcedureWithResultSet.SQL + "()}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		ctrlResultSet.replay();

		StoredProcedureWithResultSet sproc = new StoredProcedureWithResultSet(mockDataSource);
		sproc.execute();

		ctrlResultSet.verify();
		assertEquals(2, sproc.getCount());
	}
	
	public void testStoredProcedureWithResultSetMapped() throws Exception {
		MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
		ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(2);
		ctrlResultSet.setReturnValue("Foo");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(true);
		mockResultSet.getString(2);
		ctrlResultSet.setReturnValue("Bar");
		mockResultSet.next();
		ctrlResultSet.setReturnValue(false);
		mockResultSet.close();
		ctrlResultSet.setVoidCallable();

		mockCallable.execute();
		ctrlCallable.setReturnValue(true);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getResultSet();
		ctrlCallable.setReturnValue(mockResultSet);
		mockCallable.getMoreResults();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		ctrlResultSet.replay();

		StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped(mockDataSource);
		Map res = sproc.execute();

		ctrlResultSet.verify();
		
		List rs = (List) res.get("rs");
		assertEquals(2, rs.size());
		assertEquals("Foo", rs.get(0));
		assertEquals("Bar", rs.get(1));		

	}

	public void testStoredProcedureWithUndeclaredResults() throws Exception {
		MockControl ctrlResultSet1 = MockControl.createControl(ResultSet.class);
		ResultSet mockResultSet1 = (ResultSet) ctrlResultSet1.getMock();
		mockResultSet1.next();
		ctrlResultSet1.setReturnValue(true);
		mockResultSet1.getString(2);
		ctrlResultSet1.setReturnValue("Foo");
		mockResultSet1.next();
		ctrlResultSet1.setReturnValue(true);
		mockResultSet1.getString(2);
		ctrlResultSet1.setReturnValue("Bar");
		mockResultSet1.next();
		ctrlResultSet1.setReturnValue(false);
		mockResultSet1.close();
		ctrlResultSet1.setVoidCallable();

		MockControl ctrlMetaData = MockControl.createControl(ResultSetMetaData.class);
		ResultSetMetaData mockMetaData = (ResultSetMetaData) ctrlMetaData.getMock();
		mockMetaData.getColumnCount();
		ctrlMetaData.setReturnValue(2);
		mockMetaData.getColumnLabel(1);
		ctrlMetaData.setReturnValue("spam");
		mockMetaData.getColumnLabel(2);
		ctrlMetaData.setReturnValue("eggs");

		MockControl ctrlResultSet2 = MockControl.createControl(ResultSet.class);
		ResultSet mockResultSet2 = (ResultSet) ctrlResultSet2.getMock();
		mockResultSet2.getMetaData();
		ctrlResultSet2.setReturnValue(mockMetaData);
		mockResultSet2.next();
		ctrlResultSet2.setReturnValue(true);
		mockResultSet2.getObject(1);
		ctrlResultSet2.setReturnValue("Spam");
		mockResultSet2.getObject(2);
		ctrlResultSet2.setReturnValue("Eggs");
		mockResultSet2.next();
		ctrlResultSet2.setReturnValue(false);
		mockResultSet2.close();
		ctrlResultSet2.setVoidCallable();

		mockCallable.execute();
		ctrlCallable.setReturnValue(true);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getResultSet();
		ctrlCallable.setReturnValue(mockResultSet1);
		mockCallable.getMoreResults();
		ctrlCallable.setReturnValue(true);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getResultSet();
		ctrlCallable.setReturnValue(mockResultSet2);
		mockCallable.getMoreResults();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(0);
		mockCallable.getMoreResults();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		ctrlResultSet1.replay();
		ctrlMetaData.replay();
		ctrlResultSet2.replay();

		StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped(mockDataSource);
		Map res = sproc.execute();

		ctrlResultSet1.verify();
		ctrlResultSet2.verify();

		assertEquals("incorrect number of returns", 3, res.size());

		List rs1 = (List) res.get("rs");
		assertEquals(2, rs1.size());
		assertEquals("Foo", rs1.get(0));
		assertEquals("Bar", rs1.get(1));

		List rs2 = (List) res.get("#result-set-2");
		assertEquals(1, rs2.size());
		Object o2 = rs2.get(0);
		assertTrue("wron type returned for result set 2", o2 instanceof Map);
		Map m2 = (Map) o2;
		assertEquals("Spam", m2.get("spam"));
		assertEquals("Eggs", m2.get("eggs"));

		Number n = (Number) res.get("#update-count-1");
		assertEquals("wrong update count", 0, n.intValue());

	}

	public void testStoredProcedureSkippingResultsProcessing() throws Exception {

		mockCallable.execute();
		ctrlCallable.setReturnValue(true);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(mockDataSource);
		jdbcTemplate.setSkipResultsProcessing(true);
		StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped(jdbcTemplate);
		Map res = sproc.execute();

		assertEquals("incorrect number of returns", 0, res.size());

	}

	public void testStoredProcedureSkippingUndeclaredResults() throws Exception {
		MockControl ctrlResultSet1 = MockControl.createControl(ResultSet.class);
		ResultSet mockResultSet1 = (ResultSet) ctrlResultSet1.getMock();
		mockResultSet1.next();
		ctrlResultSet1.setReturnValue(true);
		mockResultSet1.getString(2);
		ctrlResultSet1.setReturnValue("Foo");
		mockResultSet1.next();
		ctrlResultSet1.setReturnValue(true);
		mockResultSet1.getString(2);
		ctrlResultSet1.setReturnValue("Bar");
		mockResultSet1.next();
		ctrlResultSet1.setReturnValue(false);
		mockResultSet1.close();
		ctrlResultSet1.setVoidCallable();

		mockCallable.execute();
		ctrlCallable.setReturnValue(true);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getResultSet();
		ctrlCallable.setReturnValue(mockResultSet1);
		mockCallable.getMoreResults();
		ctrlCallable.setReturnValue(true);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getMoreResults();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		ctrlResultSet1.replay();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(mockDataSource);
		jdbcTemplate.setSkipUndeclaredResults(true);
		StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped(jdbcTemplate);
		Map res = sproc.execute();

		ctrlResultSet1.verify();

		assertEquals("incorrect number of returns", 1, res.size());

		List rs1 = (List) res.get("rs");
		assertEquals(2, rs1.size());
		assertEquals("Foo", rs1.get(0));
		assertEquals("Bar", rs1.get(1));

	}

	public void testParameterMapper() throws Exception {
		mockCallable.setString(1, "EasyMock for interface java.sql.Connection");
		ctrlCallable.setVoidCallable();
		mockCallable.registerOutParameter(2, Types.VARCHAR);
		ctrlCallable.setVoidCallable();
		mockCallable.execute();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getObject(2);
		ctrlCallable.setReturnValue("OK");
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall(
			"{call " + ParameterMapperStoredProcedure.SQL + "(?, ?)}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		ParameterMapperStoredProcedure pmsp = new ParameterMapperStoredProcedure(mockDataSource);
		Map out = pmsp.executeTest();
		assertEquals("OK", out.get("out"));
	}

	public void testSqlTypeValue() throws Exception {
		int[] testVal = new int[] {1, 2};
		mockCallable.getConnection();
		ctrlCallable.setDefaultReturnValue(mockConnection);
		mockCallable.setObject(1, testVal, Types.ARRAY);
		ctrlCallable.setVoidCallable();
		mockCallable.registerOutParameter(2, Types.VARCHAR);
		ctrlCallable.setVoidCallable();
		mockCallable.execute();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getObject(2);
		ctrlCallable.setReturnValue("OK");
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall(
			"{call " + SqlTypeValueStoredProcedure.SQL + "(?, ?)}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		SqlTypeValueStoredProcedure stvsp = new SqlTypeValueStoredProcedure(mockDataSource);
		Map out = stvsp.executeTest(testVal);
		assertEquals("OK", out.get("out"));
	}

	public void testNumericWithScale() throws Exception {
		mockCallable.getConnection();
		ctrlCallable.setDefaultReturnValue(mockConnection);
		mockCallable.registerOutParameter(1, Types.DECIMAL, 4);
		ctrlCallable.setVoidCallable();
		mockCallable.execute();
		ctrlCallable.setReturnValue(false);
		mockCallable.getUpdateCount();
		ctrlCallable.setReturnValue(-1);
		mockCallable.getObject(1);
		ctrlCallable.setReturnValue(new BigDecimal("12345.6789"));
		mockCallable.getWarnings();
		ctrlCallable.setReturnValue(null);
		mockCallable.close();
		ctrlCallable.setVoidCallable();

		mockConnection.prepareCall(
			"{call " + NumericWithScaleStoredProcedure.SQL + "(?)}");
		ctrlConnection.setReturnValue(mockCallable);

		replay();
		NumericWithScaleStoredProcedure nwssp = new NumericWithScaleStoredProcedure(mockDataSource);
		Map out = nwssp.executeTest();
		assertEquals(new BigDecimal("12345.6789"), out.get("out"));
	}


	private static class StoredProcedureConfiguredViaJdbcTemplate extends StoredProcedure {

		public static final String SQL = "configured_via_jt";

		public StoredProcedureConfiguredViaJdbcTemplate(JdbcTemplate t) {
			setJdbcTemplate(t);
			setSql(SQL);
			declareParameter(new SqlParameter("intIn", Types.INTEGER));
			declareParameter(new SqlOutParameter("intOut", Types.INTEGER));
			compile();
		}

		public int execute(int intIn) {
			Map in = new HashMap();
			in.put("intIn", new Integer(intIn));
			Map out = execute(in);
			Number intOut = (Number) out.get("intOut");
			return intOut.intValue();
		}
	}


	private static class AddInvoice extends StoredProcedure {

		public static final String SQL = "add_invoice";

		public AddInvoice(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			declareParameter(new SqlParameter("amount", Types.INTEGER));
			declareParameter(new SqlParameter("custid", Types.INTEGER));
			declareParameter(new SqlOutParameter("newid", Types.INTEGER));
			compile();
		}

		public int execute(int amount, int custid) {
			Map in = new HashMap();
			in.put("amount", new Integer(amount));
			in.put("custid", new Integer(custid));
			Map out = execute(in);
			Number id = (Number) out.get("newid");
			return id.intValue();
		}
	}


	private static class NullArg extends StoredProcedure {

		public static final String SQL = "takes_null";

		public NullArg(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			declareParameter(new SqlParameter("ptest", Types.VARCHAR));
			compile();
		}

		public void execute(String s) {
			Map in = new HashMap();
			in.put("ptest", s);
			Map out = execute(in);
		}
	}


	private static class NoSuchStoredProcedure extends StoredProcedure {

		public static final String SQL = "no_sproc_with_this_name";

		public NoSuchStoredProcedure(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			compile();
		}

		public void execute() {
			execute(new HashMap());
		}
	}


	private static class UnnamedParameterStoredProcedure extends StoredProcedure {

		public UnnamedParameterStoredProcedure(DataSource ds) {
			super(ds, "unnamed_parameter_sp");
			declareParameter(new SqlParameter(Types.INTEGER));
			compile();
		}

		public void execute(int id) {
			Map in = new HashMap();
			in.put("id", new Integer(id));
			Map out = execute(in);
		}
	}


	private static class MissingParameterStoredProcedure extends StoredProcedure {

		public MissingParameterStoredProcedure(DataSource ds) {
			setDataSource(ds);
			setSql("takes_string");
			declareParameter(new SqlParameter("mystring", Types.VARCHAR));
			compile();
		}

		public void execute() {
			execute(new HashMap());
		}
	}


	private static class StoredProcedureWithResultSet extends StoredProcedure {

		public static final String SQL = "sproc_with_result_set";

		private final SimpleRowCountCallbackHandler handler = new SimpleRowCountCallbackHandler();

		public StoredProcedureWithResultSet(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			declareParameter(new SqlReturnResultSet("rs", this.handler));
			compile();
		}

		public void execute() {
			execute(new HashMap());
		}

		public int getCount() {
			return this.handler.getCount();
		}
	}


	private static class StoredProcedureWithResultSetMapped extends StoredProcedure {

		public static final String SQL = "sproc_with_result_set";

		public StoredProcedureWithResultSetMapped(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			declareParameter(
				new SqlReturnResultSet("rs", new RowMapperImpl()));
			compile();
		}

		public StoredProcedureWithResultSetMapped(JdbcTemplate jt) {
			setJdbcTemplate(jt);
			setSql(SQL);
			declareParameter(
				new SqlReturnResultSet("rs", new RowMapperImpl()));
			compile();
		}

		public Map execute() {
			Map out = execute(new HashMap());
			return out;
		}

		private static class RowMapperImpl implements RowMapper {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString(2);
			}
		}
	}


	private static class ParameterMapperStoredProcedure extends StoredProcedure {

		public static final String SQL = "parameter_mapper_sp";

		public ParameterMapperStoredProcedure(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			declareParameter(new SqlParameter("in", Types.VARCHAR));
			declareParameter(new SqlOutParameter("out", Types.VARCHAR));
			compile();
		}

		public Map executeTest() {
			Map out = null;
			out = execute(new TestParameterMapper());
			return out;
		}
		
		private static class TestParameterMapper implements ParameterMapper {
			
			private TestParameterMapper() {
			}
			
			public Map createMap(Connection conn) throws SQLException {
				Map inParms = new HashMap();
				String testValue = conn.toString();
				inParms.put("in", testValue);
				return inParms;
			}
		}
	}


	private static class SqlTypeValueStoredProcedure extends StoredProcedure {

		public static final String SQL = "sql_type_value_sp";

		public SqlTypeValueStoredProcedure(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			declareParameter(new SqlParameter("in", Types.ARRAY, "NUMBERS"));
			declareParameter(new SqlOutParameter("out", Types.VARCHAR));
			compile();
		}

		public Map executeTest(final int[] inValue) {
			Map in = new HashMap(1);
			in.put("in", new AbstractSqlTypeValue() {
				public Object createTypeValue(Connection con, int type, String typeName) {
					//assertEquals(Connection.class, con.getClass());
					//assertEquals(Types.ARRAY, type);
					//assertEquals("NUMBER", typeName);
					return inValue;
				}
			});
			Map out = null;
			out = execute(in);
			return out;
		}
	}

	private static class NumericWithScaleStoredProcedure extends StoredProcedure {

		public static final String SQL = "numeric_with_scale_sp";

		public NumericWithScaleStoredProcedure(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			declareParameter(new SqlOutParameter("out", Types.DECIMAL, 4));
			compile();
		}

		public Map executeTest() {
			Map in = new HashMap(1);
			Map out = null;
			out = execute(in);
			return out;
		}
	}


	private static class StoredProcedureExceptionTranslator extends StoredProcedure {

		public static final String SQL = "no_sproc_with_this_name";

		public StoredProcedureExceptionTranslator(DataSource ds) {
			setDataSource(ds);
			setSql(SQL);
			getJdbcTemplate().setExceptionTranslator(new SQLExceptionTranslator() {
				public DataAccessException translate(
					String task,
					String sql,
					SQLException sqlex) {
					return new CustomDataException(sql, sqlex);
				}

			});
			compile();
		}

		public void execute() {
			execute(new HashMap());
		}
	}


	private static class CustomDataException extends DataAccessException {

		public CustomDataException(String s) {
			super(s);
		}

		public CustomDataException(String s, Throwable ex) {
			super(s, ex);
		}
	}

}

Other Spring Framework examples (source code examples)

Here is a short list of links related to this Spring Framework StoredProcedureTests.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.