Oracle部分:
--------------------------------------------------------------------------------------------------------------------------------------
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;
/
Java部分:
----------------------------------------------------------------------------------------------------------------------------------
- package test.oracle.conn;
- import java.sql.Connection;
- import java.sql.DriverManager;
- public class OConnection {
- public static Connection getConn() {
- String URL = "jdbc:oracle:thin:@127.0.0.1:1521:oradb";
- String user = "cartoon";// 这里替换成你自已的数据库用户名
- String password = "oracle";// 这里替换成你自已的数据库用户密码
- 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;
- }
- }
构造Java映射对象
- package test.oracle.array;
- 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 + "}";
- }
- }
构造Java映射对象
- package test.oracle.array;
- 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 + "}";
- }
- }
Java方法实现Oracle对象数组
- package test.oracle.array;
- 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(?,?,?)}";
- 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;
- }
- }
测试Java调用Oracle对象数组
- package test.oracle;
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.Date;
- import test.oracle.array.Child;
- import test.oracle.array.OracleArray;
- import test.oracle.array.Parent;
- import test.oracle.conn.OConnection;
- public class TestOracleArray {
- public static void main(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);
- }
- }