基于spring存储过程小工具类

对一个一个存储过程来说

• 传入参数
• 过程本身
• 执行结果：结果包含所有结果集（ResultSet）和传出参数（Out）。返回值可以认为是一个特殊的传出参数（Out）

适用数据库sybase 以及类似行为的数据库存储过程。

工具代码，T为结果对象的泛型，P为传入参数泛型

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.support.JdbcUtils;

public abstract class SpCallableStatementCallback<T> implements CallableStatementCallback<T> {

	@Override
	public final T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
		cs.execute();
		T t= this.creatResultObject();
		int resultSetNum = 0;
		while (cs.getMoreResults()) {
			handleResultSet(resultSetNum++, cs, t);
		}
		this.handleOutParameter(cs, t);
		return t;
	}
	
	private void handleResultSet(int resultSetNum, CallableStatement cs, T t) throws SQLException {
		ResultSet rs = cs.getResultSet();
		int rowNum = 0;
		while(rs.next()){
			this.handleResultSetMapping(resultSetNum, rs, rowNum++, t);
		}
		JdbcUtils.closeResultSet(rs);
	}
	
	protected abstract T creatResultObject();
	
	// int resultSetNum，一个存储过程返回多个结果集的时候，这个Num来区分结果集的index。
	protected abstract void handleResultSetMapping(int resultSetNum, ResultSet rs, int rowNum, T t) throws SQLException;
	
	protected abstract void handleOutParameter(CallableStatement cs, T t) throws SQLException;
}

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;

public abstract class StoreProcedureCallWithParamThreadSafe<P, T> extends SpCallableStatementCallback<T> {
	
	private JdbcTemplate jdbcTemplate;
	
	public StoreProcedureCallWithParamThreadSafe() {
	}
	
	public StoreProcedureCallWithParamThreadSafe(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	protected abstract CallableStatement createCallableStatement(Connection con, P inParamObj) throws SQLException ;

	protected CallableStatementCreator createCallableStatement(final P inParamObj) {
		return new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				return StoreProcedureCallWithParamThreadSafe.this.createCallableStatement(con, inParamObj);
			}
		};
	}
	
	public T call(P inputParameterObject){
		return jdbcTemplate.execute(this.createCallableStatement(inputParameterObject), this);
	}
	
}

使用Sample
数据库创建存储过程，该过程有两个传入参数，一个传出参数，3个结果集（每个只有1行结果），和一个返回值99.

CREATE PROCEDURE dbo.mathtutor2 
@mult1 int, @mult2 int, @result int output 
as 
select @result = @mult1 * @mult2
select 10
select 20
select 30
return 99

传入参数对象

public class TempSpInParam {
	private int a;
	private int b;

	public TempSpInParam(int a, int b) {
		super();
		this.a = a;
		this.b = b;
	}

	@Override
	public String toString() {
		return "TempSpIn [a=" + a + ", b=" + b + "]";
	}

	public int getA() {
		return a;
	}

	public void setA(int a) {
		this.a = a;
	}

	public int getB() {
		return b;
	}

	public void setB(int b) {
		this.b = b;
	}

}

执行结果对象

public class TempSpResult {
	private int returnVal;
	private int outVal;
	private List<Integer> list = new ArrayList<>();

	@Override
	public String toString() {
		return "SpObj [returnVal=" + returnVal + ", outVal=" + outVal
				+ ", list=" + list + "]";
	}

	public int getReturnVal() {
		return returnVal;
	}

	public void setReturnVal(int returnVal) {
		this.returnVal = returnVal;
	}

	public int getOutVal() {
		return outVal;
	}

	public void setOutVal(int outVal) {
		this.outVal = outVal;
	}

	public List<Integer> getList() {
		return list;
	}

	public void setList(List<Integer> list) {
		this.list = list;
	}

}

过程本身对象

public class TempSp extends StoreProcedureCallWithParamThreadSafe<TempSpInParam, TempSpResult>{
	// 
	private String spSql = "{? = call tempdb..mathtutor2 (?,?,?)}";
	
	@Override
	protected CallableStatement createCallableStatement(Connection con, TempSpInParam inParamObj) throws SQLException {
		CallableStatement cs = con.prepareCall(spSql);
		cs.registerOutParameter(1, java.sql.Types.INTEGER);
		cs.setInt(2, inParamObj.getA());
		cs.setInt(3, inParamObj.getB());
		cs.registerOutParameter(4, java.sql.Types.INTEGER);
		return cs;
	}
	
	@Override
	protected TempSpResult creatResultObject() {
		return new TempSpResult();
	}
	
	// 把所有结果集都放入t list中。这里没有加以区分，实际代码通常应该对resultSetNum 进行case switch，对不同的结果集分别处理。
	@Override
	protected void handleResultSetMapping(int resultSetNum, ResultSet rs, int rowNum, TempSpResult t) throws SQLException {
		System.out.println(String.format("resultSetNum=%s",  resultSetNum));
		t.getList().add(rs.getInt(1));
	}

	@Override
	protected void handleOutParameter(CallableStatement cs, TempSpResult t) throws SQLException {
		t.setReturnVal(cs.getInt(1));
		t.setOutVal(cs.getInt(4));
	}
	
}

Test类

@RunWith(SpringJUnit4ClassRunner.class)
public class TestSample extends GenericTestCase {

	@Autowired
	protected JdbcTemplate jdbcTemplate;

	@Test
	public void test1() {
		TempSp tempSp = new TempSp();
		tempSp.setJdbcTemplate(jdbcTemplate);
		System.out.println(tempSp.call(new TempSpInParam(2, 3)));
	}
	
}

执行结果
resultSetNum=0
resultSetNum=1
resultSetNum=2
SpObj [returnVal=99, outVal=6, list=[10, 20, 30]]