81,092
社区成员
发帖
与我相关
我的任务
分享
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++) {
if((i%2==0)&&(j>=50)){
break;
}
Child c = new Child();
c.setChildName("childName" + j);
c.setChildTitle("childT" + j);
c.setChildContent("childContent"+j);
clst.add(c);
}
clstMap.add(clst);
}
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;
}
}
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;
}
}
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);
}
}
SQL> select count(*) from Parent;
COUNT(*)
----------
1700
SQL> select count(*) from Child;
COUNT(*)
----------
300550
IWAV0055I Java Bean test.oracle.TestOracleArray started with the main method
类实例化成功!
创建连接对像成功!
It takes 1188 milliseconds to execute
disconnected
IWAV0055I Java Bean test.oracle.TestOracleArray started with the main method
类实例化成功!
创建连接对像成功!
It takes 3532 milliseconds to execute
disconnected
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;
/
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 + "}";
}
}
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 + "}";
}
}
int i=0;
int sum=集合的大小.
for (Object xxx: 数据集合) {
session.save(xxx);
i++;
if (i ==sum) {
tx.commit();
i = 0;
session.flush();
session.clear();
tx = session.beginTransaction();
}
}
if (i != 0) {
tx.commit();
session.flush();
session.clear();
}