下面做一个插入10万条记录的示例步骤,,为了容易理解,表的结果很简单.
1,假设表结构如下:
CREATE TABLE UKBNOVCTCORDER(
LosingLEName varchar2(200),
LosingLECode varchar2(200)
)
2,在 数据库建立一个type,对应JAVA端要传入的对象结构:
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,建立存储过程做插入工作:
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方法省略,)
public class UkbnovCTCOrder {
private String losingLEName;
private String losingLECode;
.....
在JAVA端访问存储过程插入数据,需要做JAVA数据类型和存储过程类型type的映射,下面的StructDescriptor是mapping Oracle端AS OBJECT类型,
tabDesc 是mapping Oracle端数组AS TABLE OF类型的.
CallableStatement cstmt = null;
try {
con = OracleConnection.getConn();
List
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
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方法省略. www.2cto.com
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);