JAVA客户端传递对象数组到Oracle存储过程做大数据量插入

最近在项目中用到了JAVA客户端传递对象数组到Oracle存储过程做大数据量插入,比如10万级别.
下面做一个插入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)asctcOrder 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类型的.

        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方法省略.

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,如下:

        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秒(当然也和这里的表结构字段少有关系).