jdbc调用存储过程

jdbc调用存储过程，输入参数设置比较简单，暂略。存储过程定义如下：

CREATE OR REPLACE PROCEDURE java_proc_test(data_curor out sys_refcursor)
  as
  BEGIN
  open data_curor for select * from corp_basic_info;
  END java_proc_test;

这是个返回结果集的存储过程。
java代码如下：

Connection conn = null;
ResultSet rs = null;
CallableStatement cstmt = null;
  try {
     Class.forName("oracle.jdbc.driver.OracleDriver");
     conn = DriverManager.getConnection ("jdbc:oracle:thin:@<ip>:1521:db", "user", "password");
cstmt = conn.prepareCall("{call java_proc_test(?)}");

//如果有输入参数，用cstmt 的set...方法设置即可，sql中的索引是以1开始
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
//注册返回类型，如果是简单类型也有相关的值
     cstmt.execute();
     rs = (ResultSet) cstmt.getObject(1);
     while (rs.next()) {
        System.out.println(rs.getString("corp_id") + " -- " + rs.getString("corp_name"));
     }
  } catch (ClassNotFoundException e) {
     e.printStackTrace();
  } catch (SQLException e) {
     e.printStackTrace();
  } finally {
     DBUtil.close(rs);
     DBUtil.close(cstmt);
     DBUtil.close(conn);
  }

Spring 使用 JdbcTemplate 调用存储过程需要自定义两个类，配置输入参数和取得查询结果。调用的代码如下：

JList<Map> results = (List<Map>) jdbcTemplate.execute(new ProcCallableStatementCreator(zone_id, corp_id, target_id), new ProcCallableStatementCallBack());

一个类实现org.springframework.jdbc.core.CallableStatementCreator接口，作为CallableStatement的创建者：

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

import oracle.jdbc.driver.OracleTypes;

import org.springframework.jdbc.core.CallableStatementCreator;

public class ProcCallableStatementCreator implements CallableStatementCreator {
     private int zoneId;
     private int corpId;
     private int targetId;

     public ProcCallableStatementCreator(int zoneId, int corpId, int targetId){
          this.zoneId = zoneId;
          this.corpId = corpId;
          this.targetId = targetId;
     }
 
     public CallableStatement createCallableStatement(Connection conn) throws SQLException {
          CallableStatement cstmt = null;
          cstmt = conn.prepareCall("{call select_warn_date(?,?,?,?)}");
          cstmt.setInt(1, zoneId);
          cstmt.setInt(2, corpId);
          cstmt.setInt(3, targetId);
          cstmt.registerOutParameter(4, OracleTypes.CURSOR);
          return cstmt;
     }

}

另一个类根据创建的CallableStatement获取查询结果：

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

public class ProcCallableStatementCallBack implements CallableStatementCallback {

 public Object doInCallableStatement(CallableStatement cstmt) throws DataAccessException, SQLException {
      List<Map> results = new ArrayList<Map>();
      cstmt.execute();
  
      ResultSet rs = null;
      rs = (ResultSet) cstmt.getObject(4);//根据存储过程输出参数的顺序获得值，从1开始，存储过程返回的结果集可以是多个
  
      Map eachRow = null;
      if (rsCod != null){
      while (rsCod.next()) {
          eachRow = new HashMap();
          eachRow.put("corpId", rsCod.getInt("corp_id"));
          eachRow.put("corpName", rsCod.getString("corp_name"));
          ...
          results.add(eachRow);
       }
    }
    return results;
 }

}