spring boot 调用带参数的存储过程

一、第一种方式

存储过程带参数：in out 

在dao层@Repository

@Autowired //注入数据库操作

private JdbcTemplate jdbcTemplate;

public List<String> doDeposit(AcctRechargeBeanEvt evt) {
List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call pro_account_recharge(?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(3, evt.getUnitcode()); 
cs.setString(4, evt.getLimitcode()); 
cs.setDouble(6, evt.getOperaValue()); 
cs.setString(7, evt.getAcctBookTypeCode()); 
cs.setLong(8, evt.getOperator());// 操作人id
cs.registerOutParameter(9,java.sql.Types.INTEGER);// 注册输出参数 返回类型
cs.registerOutParameter(10,java.sql.Types.VARCHAR);// 注册输出参数 返回信息
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<String> result = new ArrayList<String>();
cs.execute();

result.add(cs.getString(9));
result.add(cs.getString(10));
return result;
}
});


return resultList;

}

resultList就是存储过程返回的结果数据。

 

网上例子：

create or replace procedure sp_list_table(param1 in varchar2,param2 out mypackage.my_cursor) is
    begin
    open my_cursor for select * from myTable;

    end sp_list_table;

public void test() { 
  List resultList = (List) jdbcTemplate.execute( 
     new CallableStatementCreator() { 
        public CallableStatement createCallableStatement(Connection con) throws SQLException { 
           String storedProc = "{call sp_list_table(?,?)}";// 调用的sql 
           CallableStatement cs = con.prepareCall(storedProc); 
           cs.setString(1, "p1");// 设置输入参数的值 
           cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型 
           return cs; 
        } 
     }, new CallableStatementCallback() { 
        public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException { 
           List resultsMap = new ArrayList(); 
           cs.execute(); 
           ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值 
           while (rs.next()) {// 转换每行的返回值到Map中 
              Map rowMap = new HashMap(); 
              rowMap.put("id", rs.getString("id")); 
              rowMap.put("name", rs.getString("name")); 
              resultsMap.add(rowMap); 
           } 
           rs.close(); 
           return resultsMap; 
        } 
  }); 
  for (int i = 0; i < resultList.size(); i++) { 
     Map rowMap = (Map) resultList.get(i); 
     String id = rowMap.get("id").toString(); 
     String name = rowMap.get("name").toString(); 
     System.out.println("id=" + id + ";name=" + name); 
  } 
} 

 

二、第二种

mapper:

@SelectProvider(type = CustomerProvider.class , method = "cancelIntegralGetInfo")
    void cancelIntegralGetInfo(Long orderId);

provider:

public String cancelIntegralGetInfo(Long orderId) {
		return "call cancelIntegralGetInfo(#{orderId});";
	}