jdbcTemplate 调用存储过程

 JdbcTemplate调用Oracle存储过程的一些方法：

1、 无返回值的存储过程调用：

public class JdbcTemplateTest {   private JdbcTemplate jdbcTemplate;   public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {   this.jdbcTemplate = jdbcTemplate;   }   public void test(){      this.jdbcTemplate.execute("call testpro('p1','p2')");   } } 

2、有返回值的存储过程（非结果集）

public void test() {   String param2Value = (String) jdbcTemplate.execute(      new CallableStatementCreator() {         public CallableStatement createCallableStatement(Connection con) throws SQLException {            String storedProc = "{call testpro(?,?)}";// 调用的sql            CallableStatement cs = con.prepareCall(storedProc);            cs.setString(1, "p1");// 设置输入参数的值            cs.registerOutParameter(2, OracleTypes.VARCHAR);// 注册输出参数的类型            return cs;         }      }, new CallableStatementCallback() {          public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {            cs.execute();            return cs.getString(2);// 获取输出参数的值      }   }); } 

3、有返回值的存储过程（结果集） 

1.建一个程序包,如下：
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
  TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
2.建立存储过程,如下：
CREATE OR REPLACE PROCEDURE TESTPRO(PARAM1 IN VARCHAR2,test_cursor out TESTPACKAGE.TEST_CURSOR) IS
BEGIN
     OPEN test_cursor FOR SELECT * FROM TESTTABLE;
END TESTPRO;
可以看到，列表是通过把游标作为一个out参数来返回的。

java测试代码：

public void test() {   List resultList = (List) jdbcTemplate.execute(      new CallableStatementCreator() {         public CallableStatement createCallableStatement(Connection con) throws SQLException {            String storedProc = "{call testpro(?,?)}";// 调用的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);   } }