存储过程部分:
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();
}
}
以上方法和过程都经过验证,准确无误!