http://www.cnblogs.com/cl1255674805/p/5130393.html
1.创建数据库Person
CREATE TABLE Person(
id number,
name nvarchar2(200) ,
age number ,
sex nvarchar2(200) ,
password nvarchar2(200)
)
2.在数据库建立一个type,对应JAVA端要传入的对象结构:
CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_REC1 AS OBJECT (
id number,
name nvarchar2(200) ,
age number ,
sex nvarchar2(200) ,
password nvarchar2(200)
)
3.为了数组传输,建立一个数组类型的type:
CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_TAB AS TABLE OF BUT_UKBNOV_CTC_ORDER_REC1
4,建立存储过程做插入工作:
create or replace procedure bulkInsertCTDORDER(i_orders IN BUT_UKBNOV_CTC_ORDER_TAB)
as
ctcOrder BUT_UKBNOV_CTC_ORDER_REC1;
begin
FOR idx IN 1..i_orders.COUNT LOOP
ctcOrder:=i_orders(idx);
INSERT INTO person
(id,
name,
age,
sex,
password
)
VALUES
(ctcOrder.id,
ctcOrder.name,
ctcOrder.age,
ctcOrder.sex,
ctcOrder.password
);
end loop;
exception when others then
raise;
end;
5,建立JAVA端java bean对象
package me.person;
public class Person {
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
private int id;
private String name;
private int age;
private String sex;
private String password;
}
6.在JAVA端访问存储过程插入数据
需要做JAVA数据类型和存储过程类型type的映射,下面的StructDescriptor是mapping Oracle端AS OBJECT类型,
tabDesc 是mapping Oracle端数组 AS TABLE OF类型的.
package me.arrayinsertbatch;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import org.springframework.stereotype.Service;
import me.person.Person;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
@Service("insertbatchservive")
public class ArraybatchInsertFromExcel {
public void insertbatchfromexcel() throws Exception//10万条11秒
{
double begin = System.currentTimeMillis();
Connection con = null;
CallableStatement cstmt = null;
try {
con = ArrayInsertBatch.getConn();
List<Person> orderList = new ArrayList<Person>();
for(int i=0;i<100000;i++){
Person per=new Person();
per.setId(i);
per.setName("InsertName"+i);
per.setPassword("insertpassword"+i);
per.setSex("男");
per.setAge(i);
orderList.add(per);
}
//JSONArray json=JSONArray.fromObject(orderList);
//System.out.println(json.toString());
StructDescriptor recDesc = StructDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_REC1", con);
ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
for (Person ord:orderList) {
Object[] record = new Object[5];
record[0] = ord.getId();
record[1] = ord.getName();
record[2] = ord.getAge();
record[3] = ord.getSex();
record[4] = ord.getPassword();
// JSONArray json1=JSONArray.fromObject(record);
// System.out.println(json1.toString());
//System.out.println(record[4].toString());
STRUCT item = new STRUCT(recDesc, con, record);
pstruct.add(item);
}
//JSONArray json2=JSONArray.fromObject(pstruct);
// System.out.println(json2.toString());
ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);
ARRAY vArray = new ARRAY(tabDesc, con, pstruct.toArray());
//JSONArray json=JSONArray.fromObject(pstruct.toArray());
// System.out.println(json.toString());
cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");
cstmt.setArray(1, vArray);
//cstmt.setString(2, SYSJ);
// cstmt.setString(4, QYSJ);
cstmt.execute();
con.commit();
double time = (System.currentTimeMillis() - begin) / 1000;
System.out.println("插入共花费时间" + time + "s");
}
catch(Exception ex)
{
throw ex;
}
}
}