顶
0
踩 最近在项目中用到了JAVA客户端传递对象数组到Oracle存储过程做大数据量插入,比如10万级别.
下面做一个插入10万条记录的示例步骤,,为了容易理解,表的结果很简单.
1,假设表结构如下:
源码copy to clipboard打印?
01.CREATE TABLE UKBNOVCTCORDER(
02.LosingLEName varchar2(200),
03.LosingLECode varchar2(200)
04.)
CREATE TABLE UKBNOVCTCORDER(
LosingLEName varchar2(200),
LosingLECode varchar2(200)
)2,在数据库建立一个type,对应JAVA端要传入的对象结构:
源码copy to clipboard打印?
01.CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_REC AS OBJECT (
02. losingLEName VARCHAR2(200),
03. losingLECode VARCHAR2(200)
04.);
CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_REC AS OBJECT (
losingLEName VARCHAR2(200),
losingLECode VARCHAR2(200)
);3,为了数组传输,建立一个数组类型的type:
CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_TAB AS TABLE OF BUT_UKBNOV_CTC_ORDER_REC
4,建立存储过程做插入工作:
源码copy to clipboard打印?
01.CREATE OR REPLACE procedure bulkInsertCTDORDER(i_orders IN BUT_UKBNOV_CTC_ORDER_TAB)
02.as
03.ctcOrder BUT_UKBNOV_CTC_ORDER_REC;
04.begin
05. FOR idx IN i_orders.first()..i_orders.last() LOOP
06. ctcOrder:=i_orders(idx);
07. INSERT INTO UKBNOVCTCORDER
08. (LosingLEName,
09. LosingLECode)
10. VALUES
11. (ctcOrder.losingLEName,
12. ctcOrder.losingLECode);
13. end loop;
14. exception when others then
15. raise;
16.end;
CREATE OR REPLACE procedure bulkInsertCTDORDER(i_orders IN BUT_UKBNOV_CTC_ORDER_TAB)
as
ctcOrder BUT_UKBNOV_CTC_ORDER_REC;
begin
FOR idx IN i_orders.first()..i_orders.last() LOOP
ctcOrder:=i_orders(idx);
INSERT INTO UKBNOVCTCORDER
(LosingLEName,
LosingLECode)
VALUES
(ctcOrder.losingLEName,
ctcOrder.losingLECode);
end loop;
exception when others then
raise;
end;5,建立JAVA端java bean对象,(为节省版面,下面的get set方法省略,)
源码copy to clipboard打印?
01.public class UkbnovCTCOrder {
02. private String losingLEName;
03. private String losingLECode;
04......
public class UkbnovCTCOrder {
private String losingLEName;
private String losingLECode;
.....在JAVA端访问存储过程插入数据,需要做JAVA数据类型和存储过程类型type的映射,下面的StructDescriptor是mapping Oracle端AS OBJECT类型,
tabDesc 是mapping Oracle端数组 AS TABLE OF类型的.
源码copy to clipboard打印?
01.Connection con = null;
02.CallableStatement cstmt = null;
03.try {
04. con = OracleConnection.getConn();
05. List<UkbnovCTCOrder> orderList = new ArrayList<UkbnovCTCOrder>();
06. for(int i=0;i<100000;i++){
07. orderList.add(new UkbnovCTCOrder("losingLEName"+i,"losingLECode+"+i));
08. }
09. StructDescriptor recDesc = StructDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_REC", con);
10. ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
11. for (UkbnovCTCOrder ord:orderList) {
12. Object[] record = new Object[2];
13. record[0] = ord.getLosingLEName();
14. record[1] = ord.getLosingLECode();
15. STRUCT item = new STRUCT(recDesc, con, record);
16. pstruct.add(item);
17. }
18. ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);
19. ARRAY vArray = new ARRAY(tabDesc, con, pstruct.toArray());
20. cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");
21. cstmt.setArray(1, vArray);
22. cstmt.execute();
23. con.commit();
Connection con = null;
CallableStatement cstmt = null;
try {
con = OracleConnection.getConn();
List<UkbnovCTCOrder> orderList = new ArrayList<UkbnovCTCOrder>();
for(int i=0;i<100000;i++){
orderList.add(new UkbnovCTCOrder("losingLEName"+i,"losingLECode+"+i));
}
StructDescriptor recDesc = StructDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_REC", con);
ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
for (UkbnovCTCOrder ord:orderList) {
Object[] record = new Object[2];
record[0] = ord.getLosingLEName();
record[1] = ord.getLosingLECode();
STRUCT item = new STRUCT(recDesc, con, record);
pstruct.add(item);
}
ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);
ARRAY vArray = new ARRAY(tabDesc, con, pstruct.toArray());
cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");
cstmt.setArray(1, vArray);
cstmt.execute();
con.commit();6,如果每次调用都需要做Java bean的到Oracle的"AS OBJECT"类型的mapping,则很繁琐,可以然Java bean实现oracle.sql.ORAData,这样就不用在调用时候在做mapping了.
java bean对象如下,为节省版面get set方法省略.
源码copy to clipboard打印?
01.public class UkbnovCTCOrder1 implements ORAData {
02. private String losingLEName;
03. private String losingLECode;
04. public static final String _ORACLE_TYPE_NAME = "BUT_UKBNOV_CTC_ORDER_REC";
05. protected MutableStruct _struct;
06. static int[] _sqlType = { OracleTypes.VARCHAR, OracleTypes.VARCHAR };
07. static ORADataFactory[] _factory = new ORADataFactory[_sqlType.length];
08. public UkbnovCTCOrder1() {
09. _struct = new MutableStruct(new Object[_sqlType.length], _sqlType, _factory);
10. }
11. public Datum toDatum(Connection conn) throws SQLException {
12. _struct.setAttribute(0, this.losingLEName);
13. _struct.setAttribute(1, this.losingLECode);
14. return _struct.toDatum(conn, _ORACLE_TYPE_NAME);
15. }
16. public UkbnovCTCOrder1(String losingLEName, String losingLECode) {
17. this();
18. this.losingLEName = losingLEName;
19. this.losingLECode = losingLECode;
20. }
21.....
public class UkbnovCTCOrder1 implements ORAData {
private String losingLEName;
private String losingLECode;
public static final String _ORACLE_TYPE_NAME = "BUT_UKBNOV_CTC_ORDER_REC";
protected MutableStruct _struct;
static int[] _sqlType = { OracleTypes.VARCHAR, OracleTypes.VARCHAR };
static ORADataFactory[] _factory = new ORADataFactory[_sqlType.length];
public UkbnovCTCOrder1() {
_struct = new MutableStruct(new Object[_sqlType.length], _sqlType, _factory);
}
public Datum toDatum(Connection conn) throws SQLException {
_struct.setAttribute(0, this.losingLEName);
_struct.setAttribute(1, this.losingLECode);
return _struct.toDatum(conn, _ORACLE_TYPE_NAME);
}
public UkbnovCTCOrder1(String losingLEName, String losingLECode) {
this();
this.losingLEName = losingLEName;
this.losingLECode = losingLECode;
}
....调用的时候不需要再做Java bean 到Oracle "AS OBJECT"数据类型的mapping,只需要做数组类型的mapping,如下:
源码copy to clipboard打印?
01.Connection con = null;
02.CallableStatement cstmt = null;
03.try {
04. con = OracleConnection.getConn();
05. System.out.println(new Date());
06. List<UkbnovCTCOrder1> orderList = new ArrayList<UkbnovCTCOrder1>();
07. for(int i=0;i<100000;i++){
08. orderList.add(new UkbnovCTCOrder1("losingLEName"+i,"losingLECode+"+i));
09. }
10. ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);
11. ARRAY vArray = new ARRAY(tabDesc, con, orderList.toArray());
12.
13. cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");
14. cstmt.setArray(1, vArray);
15. cstmt.execute();
16. con.commit();
Connection con = null;
CallableStatement cstmt = null;
try {
con = OracleConnection.getConn();
System.out.println(new Date());
List<UkbnovCTCOrder1> orderList = new ArrayList<UkbnovCTCOrder1>();
for(int i=0;i<100000;i++){
orderList.add(new UkbnovCTCOrder1("losingLEName"+i,"losingLECode+"+i));
}
ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);
ARRAY vArray = new ARRAY(tabDesc, con, orderList.toArray());
cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");
cstmt.setArray(1, vArray);
cstmt.execute();
con.commit();上面的示例在插入10万条记录只用了5秒(当然也和这里的表结构字段少有关系).
原文链接:http://blog.csdn.net/kkdelta/article/details/7226331
Oracle存储过程做大数据量插入
最新推荐文章于 2024-08-30 10:18:21 发布