JDBCTemplate调用存储过程

2/10/2017来源:ASP.NET技巧人气:1136

一、调用无返回值的存储过程
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);  
        }
}