对一个一个存储过程来说
[list]
[*]传入参数
[*]过程本身
[*]执行结果:结果包含所有结果集(ResultSet)和传出参数(Out)。返回值可以认为是一个特殊的传出参数(Out)
[/list]
适用数据库sybase 以及类似行为的数据库存储过程。
工具代码,T为结果对象的泛型,P为传入参数泛型
使用Sample
数据库创建存储过程,该过程有两个传入参数,一个传出参数,3个结果集(每个只有1行结果),和一个返回值99.
传入参数对象
执行结果对象
过程本身对象
Test类
执行结果
resultSetNum=0
resultSetNum=1
resultSetNum=2
SpObj [returnVal=99, outVal=6, list=[10, 20, 30]]
[list]
[*]传入参数
[*]过程本身
[*]执行结果:结果包含所有结果集(ResultSet)和传出参数(Out)。返回值可以认为是一个特殊的传出参数(Out)
[/list]
适用数据库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]]