jdbcTemplate 调用存储过程和回到函数

转：http://blog.youkuaiyun.com/dancelonely/article/details/9363939

1、使用jdbcTemplate调用存储过程

Spring的SimpleJdbcTemplate将存储过程的调用进行了良好的封装。

一）无返回值的存储过程调用

CREATE OR REPLACE PROCEDURE TESTPRO(PARAM1 IN VARCHAR2,PARAM2 IN VARCHAR2) AS   
  BEGIN  
     INSERT INTO TESTTABLE (ID,NAME) VALUES (PARAM1, PARAM2);  
  END TESTPRO;  

 

   package com.dragon.test; 
  import org.springframework.jdbc.core.JdbcTemplate; 
  public class JdbcTemplateTest { 
     private JdbcTemplate jdbcTemplate; 
     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
         this.jdbcTemplate = jdbcTemplate; 
     } 
     public void test(){ 
         this.jdbcTemplate.execute("call testpro('p1','p2')"); 
     } 
} 

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

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);// 获取输出参数的值    
          }    
  });    
}  

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

因oracle存储过程所有返回值都是通过out参数返回的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage。 

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);    
  }    
}   

 2、jdbcTemplate查询数据 三种callback

1）org.springframework.jdbc.core.ResultSetExtractor：基本上属于JdbcTemplate内部使用的Callback接口，相对于下面两个Callback接口来说，ResultSetExtractor拥有更多的控制权，因为使用它，需要自行处理ResultSet。

public interface ResultSetExtractor 
{ 
   Object extractData(ResultSet rs) throws SQLException, DataAccessException; 
} List customerList = (List)jdbcTemplate.query("select * from customer", new ResultSetExtractor(){ 
   public Object extractData(ResultSet rs) throws SQLException,DataAccessException 
   { 
      List customers = new ArrayList(); 
      while(rs.next()) 
      { 
         Customer customer = new Customer(); 
         customer.setFirstName(rs.getString(1)); 
         customer.setLastName(rs.getString(2)); 
         ... 
         customers.add(customer); 
      } 
      return customers; 
   }
}); 

 2）org.springframework.jdbc.core.RowCallbackHandler：RowCallbackHandler相对于ResultSetExtractor来说，仅仅关注单行结果的处理，处理后的结果可以根据需要存放到当前RowCallbackHandler对象内或者使用JdbcTemplate的程序上下文中。

public interface RowCallbackHandler 
{ 
    void processRow(ResultSet rs) throws SQLException; 
}

final List customerList = new ArrayList(); 
jdbcTemplate.query("select * from customer", new RowCallbackHandler(){ 
   public void processRow(ResultSet rs) throws SQLException { 
      Customer customer = new Customer(); 
      customer.setFirstName(rs.getString(1)); 
      customer.setLastName(rs.getString(2)); 
      ... 
      customerList.add(customer); 
   }
}); 

 3）org.springframework.jdbc.core.RowMapper：ResultSetExtractor的精简版，功能类似于RowCallbackHandler，也只关注处理单行的结果，不过，处理后的结果会由ResultSetExtractor实现类进行组合。

public interface RowMapper 
{ 
    Object mapRow(ResultSet rs, int rowNum) throws SQLException; 
} 
List customerList = jdbcTemplate.query("select * from customer", new RowMapper(){ 

   public Object mapRow(ResultSet rs, int rowNumber) throws SQLException { 
      Customer customer = new Customer(); 
      customer.setFirstName(rs.getString(1)); 
      customer.setLastName(rs.getString(2)); 
      ... 
      return customer; 
   }
});