Java中传入和取得oracle10g存储过程返回的数组对象（多维数组）

存储过程部分：

 

create table parent(
    id number(10),
    name varchar2(100),
    title varchar2(10)
);

create table child(
    id number(10),
    parent_id number(10),
    child_name varchar2(100),
    child_title varchar2(10),
    child_content varchar2(200),
    child_time timestamp
);

create sequence seq_p_c_id
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
nocache;

drop type t_child_lst_map;
drop type t_child_lst;
drop type t_parent_lst;

create or replace type t_parent as object (
    name varchar2(100),
    title varchar2(10)
);

create or replace type t_child as object (
    child_name varchar2(100),
    child_title varchar2(10),
    child_content varchar2(200)
);

create or replace type t_parent_lst as table of t_parent;

create or replace type t_child_lst as table of t_child;

create or replace type t_child_lst_map as table of t_child_lst;

 

--输入数组参数的存储过程
create or replace procedure proc_ins_parent_child(
    i_parent_lst in t_parent_lst,        --parent列表
    i_child_map_lst in t_child_lst_map,  --child列表集合，一个map元素对应一个child_lst，其下标与 parent列表的下标相同。
    o_ret out number
) as
var_parent t_parent;
var_child_lst t_child_lst;
var_child t_child;
var_parent_id number;
var_child_id number;
begin
    for i in 1..i_parent_lst.count loop
        --取得parent各列的值
        var_parent := i_parent_lst(i);

        --取得parent_id;
        select seq_p_c_id.nextVal into var_parent_id from dual;
       
        --插入parent表
        insert into parent(
            id,
            name,
            title
        )
        values(
            var_parent_id,
            var_parent.name,
            var_parent.title
        );
       
        --取得该parent对应的child列表
        var_child_lst := i_child_map_lst(i);

        for j in 1..var_child_lst.count loop
            var_child := var_child_lst(j);
           
            --取得child_id;
            select seq_p_c_id.nextVal into var_child_id from dual;

            --插入child表
            insert into child(
                id,
                parent_id,
                child_name,
                child_title,
                child_content,
                child_time
            )
            values(
                var_child_id,
                var_parent_id,
                var_child.child_name,
                var_child.child_title,
                var_child.child_content,
                systimestamp
            );
        end loop;
   
    end loop;
    o_ret := 0;

    exception when others then
    begin
        o_ret := -1;
        raise;
    end;
end proc_ins_parent_child;

 

--输出数组参数的存储过程

create or replace procedure pro_t_parent(v_parent out t_parent_lst)
is
begin
  v_parent := t_parent_lst();
  for idx in (select * from parent where rownum < 5)loop
     v_parent.extend;
     v_parent(v_parent.count) := t_parent(idx.name, idx.title);
  end loop;
end;

 

--------------------------------------------------------------------------------------------------------------------------------
Java部分代码：

 

//测试方法

import java.sql.Array;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

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

public class TestOracleArray {
 private static final String PROC_OUT_PARENT = "call PRO_T_PARENT(?)";
 private static final String T_PARENT_LST = "T_PARENT_LST";

 public static void getParent(Connection con) throws Exception {
  OracleCallableStatement cstmt = null;
  try {
   cstmt = (OracleCallableStatement) con.prepareCall(PROC_OUT_PARENT);
   cstmt.registerOutParameter(1, OracleTypes.ARRAY, T_PARENT_LST);
   cstmt.execute();

   ARRAY ary = cstmt.getARRAY(1);
   Datum[] dtm = ary.getOracleArray();
   for (int i = 0; i < dtm.length; i++) {
    STRUCT st = (STRUCT) dtm[i];
    Object obj[] = st.getAttributes();
    System.out.println(obj[0] + "---" + obj[1]);
   }

  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   try {
    if (cstmt != null) {
     cstmt.close();
    }
   } catch (SQLException sqle) {
    sqle.printStackTrace();
   }
  }
 }

 

//输出数组参数测试

 public static void main(String[] args) {
  Connection con = null;
  try {
   long startTime = 0;
   long endTime = 0;
   con = OConnection.getConn();
   startTime = new Date().getTime();
   getParent(con);
   endTime = new Date().getTime();
   System.out.println("It takes " + (endTime - startTime)
     + " milliseconds to execute");
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (con != null) {
     con.close();
     System.out.println("disconnected");
    }
   } catch (SQLException sqle) {
    sqle.printStackTrace();
   }
  }
  System.exit(0);
 }

 

//输入数组参数测试

 public static void main_old(String[] args) {
  ArrayList<Parent> plst = new ArrayList<Parent>();
  for (int i = 0; i < 100; i++) {
   Parent p = new Parent();
   p.setName("name" + i);
   p.setTitle("title" + i);
   plst.add(p);
  }
  ArrayList<ArrayList<Child>> clstMap = new ArrayList<ArrayList<Child>>();
  for (int i = 0; i < 100; i++) {
   ArrayList<Child> clst = new ArrayList<Child>();
   for (int j = 0; j < 100; j++) {
    Child c = new Child();
    c.setChildName("childName" + j);
    c.setChildTitle("childT" + j);
    c.setChildContent("childContent" + j);
    clst.add(c);
   }
   clstMap.add(clst);
  }
  Connection con = null;
  try {
   long startTime = 0;
   long endTime = 0;
   con = OConnection.getConn();
   startTime = new Date().getTime();
   OracleArray.insParentChils(plst, clstMap, con);
   endTime = new Date().getTime();
   System.out.println("It takes " + (endTime - startTime)
     + " milliseconds to execute");
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (con != null) {
     con.close();
     System.out.println("disconnected");
    }
   } catch (SQLException sqle) {
    sqle.printStackTrace();
   }
  }
  System.exit(0);
 }
}

//构造数据对象

public class Child {  
    private String id;  
    private String parentId;  
    private String childName;  
    private String childTitle;  
    private String childContent;  
    private String childTime;  
    public String getChildContent() {  
        return childContent;  
    }  
    public void setChildContent(String childContent) {  
        this.childContent = childContent;  
    }  
    public String getChildName() {  
        return childName;  
    }  
    public void setChildName(String childName) {  
        this.childName = childName;  
    }  
    public String getChildTime() {  
        return childTime;  
    }  
    public void setChildTime(String childTime) {  
        this.childTime = childTime;  
    }  
    public String getChildTitle() {  
        return childTitle;  
    }  
    public void setChildTitle(String childTitle) {  
        this.childTitle = childTitle;  
    }  
    public String getId() {  
        return id;  
    }  
    public void setId(String id) {  
        this.id = id;  
    }  
    public String getParentId() {  
        return parentId;  
    }  
    public void setParentId(String parentId) {  
        this.parentId = parentId;  
    }  
    public String toString() {  
        return "Child{id=" + this.id + ",parentId=" + this.parentId  
                + ",childName=" + this.childName + ",childTitle=" 
                + this.childTitle + ",childContent=" + this.childContent  
                + ",childTime=" + this.childTime + "}";  
    }  
} 

//构造数据对象

public class Parent {  
    private String id;  
    private String name;  
    private String title;  
    public String getId() {  
        return id;  
    }  
    public void setId(String id) {  
        this.id = id;  
    }  
    public String getName() {  
        return name;  
    }  
    public void setName(String name) {  
        this.name = name;  
    }  
    public String getTitle() {  
        return title;  
    }  
    public void setTitle(String title) {  
        this.title = title;  
    }  
    public String toString() {  
        return "Parent{id=" + this.id + ",name=" + this.name + ",title=" 
                + this.title + "}";  
    }  
}  

 

//存储过程调用处理

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class OracleArray {
 private static final String T_PARENT = "T_PARENT";
 private static final String T_PARENT_LST = "T_PARENT_LST";
 private static final String T_CHILD = "T_CHILD";
 private static final String T_CHILD_LST = "T_CHILD_LST";
 private static final String T_CHILD_LST_MAP = "T_CHILD_LST_MAP";
 private static final String PROC_INS_PARENT_CHILD = "{ call PROC_INS_PARENT_CHILD(?,?,?)}";
 private static final String PROC_OUT_PARENT = "call PRO_T_PARENT(?)";

 public static void getParent(Connection con) throws Exception {
  CallableStatement cstmt = null;
  try{
   cstmt = con.prepareCall(PROC_OUT_PARENT);
   cstmt.registerOutParameter(1, OracleTypes.ARRAY, T_PARENT_LST);
   cstmt.execute();
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   try {
    if (cstmt != null) {
     cstmt.close();
    }
   } catch (SQLException sqle) {
    sqle.printStackTrace();
   }
  }
 }
 public static int insParentChils(ArrayList<Parent> plst,
   ArrayList<ArrayList<Child>> clstMap, Connection con)
   throws Exception {
  CallableStatement cstmt = null;
  int retVal = -1;
  try {
   ArrayDescriptor parentLstDesc = ArrayDescriptor.createDescriptor(
     T_PARENT_LST, con);
   StructDescriptor parentDesc = StructDescriptor.createDescriptor(
     T_PARENT, con);
   ArrayDescriptor childLstMapDesc = ArrayDescriptor.createDescriptor(
     T_CHILD_LST_MAP, con);
   ArrayDescriptor childLstDesc = ArrayDescriptor.createDescriptor(
     T_CHILD_LST, con);
   StructDescriptor childDesc = StructDescriptor.createDescriptor(
     T_CHILD, con);
   ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
   // 转换plst为Oracle 对象数组
   for (int i = 0; i < plst.size(); i++) {
    Parent p = plst.get(i);
    Object[] record = new Object[2];
    record[0] = p.getName();
    record[1] = p.getTitle();
    STRUCT item = new STRUCT(parentDesc, con, record);
    pstruct.add(item);
   }
   ARRAY dataps = new ARRAY(parentLstDesc, con, pstruct.toArray());
   ArrayList<ARRAY> cMap = new ArrayList<ARRAY>();
   // 转换clst为Oracle 对象数组
   for (int i = 0; i < clstMap.size(); i++) {
    ArrayList<Child> childLst = clstMap.get(i);
    ArrayList<STRUCT> cstruct = new ArrayList<STRUCT>();
    for (int j = 0; j < childLst.size(); j++) {
     Child c = childLst.get(j);
     Object[] record = new Object[3];
     record[0] = c.getChildName();
     record[1] = c.getChildTitle();
     record[2] = c.getChildContent();
     STRUCT item = new STRUCT(childDesc, con, record);
     cstruct.add(item);
    }
    ARRAY datacs = new ARRAY(childLstDesc, con, cstruct.toArray());
    cMap.add(datacs);
   }
   ARRAY datacsMap = new ARRAY(childLstMapDesc, con, cMap.toArray());
   cstmt = con.prepareCall(PROC_INS_PARENT_CHILD);
   cstmt.setArray(1, dataps);
   cstmt.setArray(2, datacsMap);
   cstmt.registerOutParameter(3, OracleTypes.INTEGER);
   cstmt.execute();
   retVal = cstmt.getInt(3);
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   try {
    if (cstmt != null) {
     cstmt.close();
    }
   } catch (SQLException sqle) {
    sqle.printStackTrace();
   }
  }
  return retVal;
 }
}

 

import java.sql.Connection;
import java.sql.DriverManager;

public class OConnection {  
    public static Connection getConn() {  
//        String URL = "jdbc:oracle:thin:@192.168.0.29:1521:orcl";  
        String URL = "jdbc:oracle:thin:@218.85.135.131:1521:orcl";  
        String user = "SMSBESTTONE_II";// 这里替换成你自已的数据库用户名  
        String password = "SMSBESTTONE_II";// 这里替换成你自已的数据库用户密码  
        Connection connection = null;  
        try {  
            Class.forName("oracle.jdbc.driver.OracleDriver");  
            System.out.println("类实例化成功!");  
            connection = DriverManager.getConnection(URL, user, password);  
            System.out.println("创建连接对像成功!");  
        } catch (Exception err) {  
            err.printStackTrace();  
            return null;  
        }  
        return connection;  
    } 
    public static void main(String[] args) {
     getConn();
    }
} 

 

以上方法和过程都经过验证，准确无误！