JDBCTemplate调用存储过程

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

public class callProcedure {    
    private JdbcTemplate jdbcTemplate;    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {    
    this.jdbcTemplate = jdbcTemplate;    
    }    
    public void test(){    
       this.jdbcTemplate.execute("{call procedureName (?)}");    
    }    
  } 

二、调用有返回值的存储过程（不是结果集）

public class test {

      /**
       * 调用无参的存储过程（有返回值）
       * @return
       */
      public static int callProcedure() {
            String str = (String)jdbcTemplate.execute(new CallableStatementCreator(){
                  @Override
                  public CallableStatement createCallableStatement(Connection connection) {
                        String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_INCRE (?)}";//存储过程
                        CallableStatement cs = null;//创建存储过程的对象
                        try {
                              cs = connection.prepareCall(procedure);
                              cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return cs;
                  }
            }, new CallableStatementCallback(){
                  @Override
                  public Object doInCallableStatement(CallableStatement cs) {
                        String csStr = null;
                        try {
                              cs.execute();
                              csStr = cs.getString(1);//获取输出参数的值
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return csStr;//获取输出参数的值
                  }});
            return Integer.parseInt(str);
      }

      /**
       * 调用有参的存储过程（有返回值）
       * @return
       */
      public static int callProcedure() {
            String str = (String)jdbcTemplate.execute(new CallableStatementCreator(){
                  @Override
                  public CallableStatement createCallableStatement(Connection connection) {
                        String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_INCRE (?,?)}";//存储过程
                        CallableStatement cs = null;//创建存储过程的对象
                        try {
                              cs = connection.prepareCall(procedure);
                              cs.setString(1,"value1");//设置入参的值
                              cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return cs;
                  }
            }, new CallableStatementCallback(){
                  @Override
                  public Object doInCallableStatement(CallableStatement cs) {
                        String csStr = null;
                        try {
                              cs.execute();
                              csStr = cs.getString(2);//获取输出参数的值
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return csStr;//获取输出参数的值
                  }});
            return Integer.parseInt(str);
      }
}

三、调用有返回值的存储过程（是结果集）

public class test {

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