The Java client transmits the object array to the Oracle Stored Procedure for large data volume insertion

Recently, a Java client was used in the project to pass an array of objects to the Oracle Stored Procedure for large data volume insertion, such as 0.1 million levels.
The following is an example of inserting 0.1 million records. For ease of understanding, the table results are very simple.
1. Assume that the table structure is as follows:

 
Create Table ukbnovctcorder (losinglename varchar2 (200), losinglecode varchar2 (200 ))
 

2. Create a type in the database, corresponding to the object structure to be passed in on the Java end:

 
Create or replace type but_ukbnov_ctc_order_rec as object (losinglename varchar2 (200), losinglecode varchar2 (200 ));
 

3. Create an array type for Array transmission:
Create or replace type but_ukbnov_ctc_order_tab as table of but_ukbnov_ctc_order_rec
4. Create a stored procedure for insertion:

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. Create a Java Bean object. (To save the layout, the following get set method is omitted ,)

 
Public class ukbnovctcorder {private string losinglename; private string losinglecode ;.....
 

When accessing the Stored Procedure data on the Java end, you need to map the Java data type to the stored procedure type. The structdescriptor below is the mapping Oracle end as object type,
Tabdesc is a ing Oracle array as table of type.

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 <Str UCT> (); 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.exe cute (); con. Commit ();

6. If every call requires mapping from Java Bean to Oracle's "as object" type, It is very cumbersome. You can use Java Bean to implement oracle. SQL. oradata, so you don't have to perform mapping during the call.
the Java Bean object is as follows, which is omitted in the Get Set Method for saving layout.

 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 ;}.... 

You do not need to perform mapping from Java Bean to Oracle "as object" during the call. You only need to perform array mapping as follows:

 
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.exe cute (); con. Commit ();
 

the preceding example only takes 5 seconds to insert 0.1 million records (of course, it has little to do with the table structure field).