spring jdbctemplate或jdbc调用返回游标或复杂数据类型的存储过程

时间：2022-05-13 04:24:28

一，当存储过程返回游标类型时：

存储过程内容如下：

create or replace procedure pro_getcurtaskinfobytimeandid(taskId in varchar2,curTime in varchar2,rst out sys_refcursor)
is
begin 
  open rst for 
      select * from ( select * from disa_rescue_task_history
              where task_id = taskId
                    and record_time >=  to_date(curTime,'yyyy-mm-dd hh24:mi:ss')  order by record_time )  where rownum = 1;
end pro_getcurtaskinfobytimeandid;

         这时，java调用如下：

package test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import oracle.jdbc.driver.OracleTypes;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import com.supermap.earth.rims.util.SprintHelper;
import com.supermap.earth.server.base.dao.SpringJdbcUtil;


public class Test {
public static void main(String[] args)throws Exception {
  SpringJdbcUtil springJdbcUtil=  (SpringJdbcUtil)SprintHelper.getInstance().getBean("springJdbcUtil");
  List resultList = (List) springJdbcUtil.getJdbcTemplate().execute(    
    new CallableStatementCreator() {    
       public CallableStatement createCallableStatement(Connection con) throws SQLException {    
          String storedProc = "{ call    pro_getcurtaskinfobytimeandid(?,?,?) }";// 调用的sql    
          CallableStatement cs = con.prepareCall(storedProc);    
          cs.setString(1, "8a5fec0257954c798a94e86a2c60f680");// 设置输入参数的值    
          cs.setString(2, "2013-1-27 15:54:32");// 设置输入参数的值    
          cs.registerOutParameter(3,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(3);// 获取游标一行的值    注：这个位置的下标要和返回CURSOR的下标位置一致，否则报错
          while (rs.next()) {// 转换每行的返回值到Map中    
             Map rowMap = new HashMap();    
             rowMap.put("id", rs.getString("id"));    
             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();    
    System.out.println("id=" + id);    
 }    
}
}

二、当存储过程返回的是一个自定义的复杂类型时，需要注意以下几点：

1.要把这个自定义复杂类型放在包外创建，不能在包内创建，如：

CREATE OR REPLACE TYPE task_record is OBJECT (
       ID                    VARCHAR2(32),
       TASK_ID               VARCHAR2(32),   
       TASK_NAME             VARCHAR2(200),
       RECORD_TIME           VARCHAR2(200),
       LNG                   NUMBER(10,7),
       LAT                   NUMBER(9,7),
       TASK_STATE            VARCHAR2(200),
       SAVE_PERSON_NUM       INT,
       DIE_PERSON_NUM        INT,
       TRACK_PERSON_NUM      INT,
       END_TIME              DATE
  );

create or replace type TASK_Table is table of TASK_RECORD;

 

存储过程代码：

create or replace procedure pro_gettaskinfobyTime(curTimeStr in varchar2,
                                                    endTimeStr in varchar2,
                                                    taskinfos out task_table)
--根据开始时间和结束时间及时间间隔查询一个救援任务的这段时间内的历史数据状态                                                   
is
      taskRecord    task_record;--历史数据记录类型
      task_max BINARY_INTEGER;
      cursor datacursor is select * from  disa_vw_rescuetask where  report_time <= to_date(endTimeStr,'yyyy-mm-dd hh24:mi:ss');
begin
      task_max := 0;
      taskinfos := task_table();     
     
      for task in datacursor loop
--      FOR task IN datacursor LOOP
          exit when datacursor%notfound;
          --根据历史时间点和救援任务id去查询这个历史时间点的救援任务信息
           pro_getcurtaskinfobytimeandid(task.id, curTimeStr,taskRecord);
          
           if taskRecord is not null then
               --把救援任务信息存入到救援任务历史数据集合中
               task_max := task_max + 1;
               taskinfos.extend;    
               taskinfos(task_max) := task_record(null,null,null,null,null,null,null,null,null,null,null);
               taskinfos(task_max).id :=  taskRecord.id;
               taskinfos(task_max).TASK_ID :=  taskRecord.TASK_ID;
               taskinfos(task_max).TASK_NAME :=  taskRecord.TASK_NAME;
               taskinfos(task_max).RECORD_TIME :=  taskRecord.RECORD_TIME;
               taskinfos(task_max).LNG :=  taskRecord.LNG;
               taskinfos(task_max).LAT :=  taskRecord.LAT;
               taskinfos(task_max).TASK_STATE :=  taskRecord.TASK_STATE;
               taskinfos(task_max).SAVE_PERSON_NUM :=  taskRecord.SAVE_PERSON_NUM;
               taskinfos(task_max).DIE_PERSON_NUM :=  taskRecord.DIE_PERSON_NUM;
               taskinfos(task_max).TRACK_PERSON_NUM :=  taskRecord.TRACK_PERSON_NUM;
               taskinfos(task_max).END_TIME :=  taskRecord.END_TIME;
           end if;
      END LOOP;

end pro_gettaskinfobyTime;

java代码：

package test;
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import com.supermap.earth.rims.util.SprintHelper;
import com.supermap.earth.server.base.dao.SpringJdbcUtil;

public class Test {
 public static void main(String[] args)throws Exception {
  getJdbc();
//     SpringJdbcUtil springJdbcUtil=  (SpringJdbcUtil)SprintHelper.getInstance().getBean("springJdbcUtil");
//     List resultList = (List) springJdbcUtil.getJdbcTemplate().execute(   
//         new CallableStatementCreator() {   
//            public CallableStatement createCallableStatement(Connection con) throws SQLException {   
//               String storedProc = "{ call    pro_gettaskinfobytime(?,?,?) }";// 调用的sql   
//               CallableStatement cs = con.prepareCall(storedProc);   
//               cs.setString(1, "2013-1-27 15:54:32");// 设置输入参数的值   
//               cs.setString(2, "2013-1-28 15:54:32");// 设置输入参数的值   
//               cs.registerOutParameter(3,OracleTypes.STRUCT,"TASK_TABLE");// 注册输出参数的类型   
//               return cs;   
//            }   
//         }, new CallableStatementCallback() {   
//            public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {   
//               List resultsMap = new ArrayList();   
//               cs.execute();   
//               Array array = cs.getArray(3);
//               ResultSet rs = array.getResultSet();
//               while (rs.next()) {// 转换每行的返回值到Map中   
//                  Map rowMap = new HashMap();   
//                  rowMap.put("id", rs.getString("id"));   
//                  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();   
//         System.out.println("id=" + id);   
//      }   
 }
 
 public static void getJdbc()throws Exception{
  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection con = DriverManager.getConnection("jdbc:oracle:thin:@172.16.113.13:1522:earthdb","earth_disaster","123456");
  OracleCallableStatement cs = (OracleCallableStatement) con.prepareCall("{ call    pro_gettaskinfobytime(?,?,?) }");
  cs.setString(1, "2013-1-28 15:34:32");// 设置输入参数的值   
  cs.setString(2, "2013-1-28 15:54:32");// 设置输入参数的值   
  cs.registerOutParameter(3,oracle.jdbc.OracleTypes.ARRAY,"TASK_TABLE");
  cs.execute();
  ARRAY array = cs.getARRAY(3);
  Datum[] data = array.getOracleArray();
  for(Datum d:data)
  {
      STRUCT struct = (STRUCT)d; 
          Datum[] stringValue = struct.getOracleAttributes(); 
          Object[] noStringValue = struct.getAttributes(); 
             
          System.out.print((String)noStringValue[0]);//id
          System.out.println(new String(stringValue[1].getBytes()));//name
  }
  cs.close();
  con.close();

 }
}

 

 

 

 

 

标签：

相关文章