Insert data into Oracle in large batches by array (100,000 11 seconds)

1. Create DATABASE person

CREATE TABLE  number, name Nvarchar2 ( number , sex nvarchar2 (  $ ), Password nvarchar2 ($))

2. Create a type in the database that corresponds to the object structure to be passed to the Java side:

   CREATE OR REPLACE TYPE but_ukbnov_ctc_order_rec1 as OBJECT (  ID number,   name NVARCHAR2 ($), age number   , 
   
    sex  nvarchar2 ($),   password  nvarchar2 (200))
   

3. To create an array type for the array transfer:

CREATE OR REPLACE  as TABLE  of But_ukbnov_ctc_order_rec1

4. Create a stored procedure to do the insertion work:

   Create or Replace procedureBulkinsertctdorder (i_ordersinchbut_ukbnov_ctc_order_tab) asCtcorder but_ukbnov_ctc_order_rec1;begin     forIdxinch 1.. I_orders.COUNTLOOP Ctcorder:=i_orders (IDX); INSERT  intoPerson (ID, name, age, sex, password)VALUES(Ctcorder.id, Ctcorder.name, Ctcorder.age, Ctcorder.sex, Ctcor    Der.password); EndLoop; Exception whenOthers Thenraise;End;

5. Create Java-side Java Bean object

 PackageMe.person; Public classPerson { Public intgetId () {returnID;} Public voidSetId (intID) { This. ID =ID;} PublicString GetName () {returnname;} Public voidsetName (String name) { This. Name =name;} Public intGetage () {returnAge ;} Public voidSetage (intAge ) { This. Age =Age ;} PublicString Getsex () {returnsex;} Public voidsetsex (String sex) { This. Sex =sex;} PublicString GetPassword () {returnpassword;} Public voidSetPassword (String password) { This. Password =password;}Private intID;PrivateString name;Private intAge ;PrivateString sex;PrivateString password;}

6. In Java-side access to the stored procedure to insert data, you need to do the Java data type and stored procedure type of the mapping, the following structdescriptor is the mapping Oracle end as Object type,
The TABDESC is a mapping Oracle-side array as TABLE of type.

 PackageMe.arrayinsertbatch;Importjava.sql.CallableStatement;Importjava.sql.Connection;Importjava.util.ArrayList;Importjava.util.List;ImportOrg.springframework.stereotype.Service;ImportMe.person.Person;ImportOracle.sql.ARRAY;ImportOracle.sql.ArrayDescriptor;ImportOracle.sql.STRUCT;Importoracle.sql.StructDescriptor; @Service ("Insertbatchservive") Public classArraybatchinsertfromexcel { Public voidInsertbatchfromexcel ()throwsException//100,000 Bar 11 sec     {         DoubleBegin =System.currenttimemillis (); Connection Con=NULL; CallableStatement cstmt=NULL; Try{con=Arrayinsertbatch.getconn (); List<Person> orderlist =NewArraylist<person>();  for(inti=0;i<100000;i++) {person per=NewPerson ();                 Per.setid (i); Per.setname ("Insertname" +i); Per.setpassword ("Insertpassword" +i); Per.setsex (Male);                 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 =NewArraylist<struct>();  for(person ord:orderlist) {object[] record=NewObject[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 =NewSTRUCT (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=NewARRAY (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 (); DoubleTime = (System.currenttimemillis ()-Begin)/1000; System.out.println ("Insert spend time" + "s"); }         Catch(Exception ex) {Throwex; }    }}

The above example only took 11 seconds to insert 100,000 records

Insert data into Oracle in large batches by array (100,000 11 seconds)