Spring提供了JDBC上调用数据库存储过程的各种抽象方法,本文主要介绍Java Sping JDBC 调用存储过程(Stored Procedure),并且带有输入(In)输出(Out)参数。

示例存储过程

PROCEDURE MOVE_TO_HISTORY (IN person_id_in INT, OUT status_out BOOLEAN)

1、使用JdbcTemplate#call(CallableStatementCreator csc, List<SqlParameter> inOutParams)调用

public void moveToHistoryTable(Person person) {
List<SqlParameter> parameters = Arrays.asList(
new SqlParameter(Types.BIGINT), new SqlOutParameter("status_out", Types.BOOLEAN));
Map<String, Object> t = jdbcTemplate.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement callableStatement = con.prepareCall("{call MOVE_TO_HISTORY (?, ?)}");
callableStatement.setLong(1, person.getId());
callableStatement.registerOutParameter(2, Types.BOOLEAN);
return callableStatement;
}
}, parameters);
}

2、使用SimpleJdbcCall(这个类大大简化了访问存储过程/函数所需的代码)

public void moveToHistoryTable(Person person){
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("MOVE_TO_HISTORY")
. declareParameters(
new SqlParameter("peron_id_in", Types.BIGINT),
new SqlOutParameter("status_out", Types.BOOLEAN));
Map<String, Object> execute = call.execute(new MapSqlParameterSource("peron_id_in", person.getId()));
}

3、使用StoredProcedure

该类在包org.springframework.jdbc中。对象,使我们能够以更面向对象的方式访问数据库。StoredProcedure是抽象的,因此我们通常必须扩展它或使用现有的实现。这里我们使用一个子类GenericStoredProcedure

public void moveToHistoryTable(Person person) {
StoredProcedure procedure = new GenericStoredProcedure();
procedure.setDataSource(dataSource);
procedure.setSql("MOVE_TO_HISTORY");
procedure.setFunction(false);
SqlParameter[] parameters = {
new SqlParameter(Types.BIGINT),
new SqlOutParameter("status_out", Types.BOOLEAN)
};
procedure.setParameters(parameters);
procedure.compile();
Map<String, Object> result = procedure.execute(person.getId());
}

参考文档www.logicbig.com/tutorials/spring-framework/spring-data-access-with-jdbc/spring-call-stored-procedur...