Java Spring 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...