通過數組方式向Oracle大批量插入資料（10萬條11秒）

標籤：

1.建立資料庫Person

CREATE TABLE Person( id number,name nvarchar2(200) ,age number ,sex nvarchar2(200) ,password nvarchar2(200))

 

2.在資料庫建立一個type,對應JAVA端要傳入的對象結構:

   CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_REC1 AS OBJECT (  id number,   name nvarchar2(200) ,   age number ,   sex  nvarchar2(200) ,   password  nvarchar2(200))

　3.為了數組傳輸,建立一個數群組類型的type:

CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_TAB AS TABLE OF BUT_UKBNOV_CTC_ORDER_REC1

4,建立預存程序做插入工作:

   create or replace procedure bulkInsertCTDORDER(i_orders IN BUT_UKBNOV_CTC_ORDER_TAB)asctcOrder BUT_UKBNOV_CTC_ORDER_REC1;begin    FOR idx IN 1..i_orders.COUNT   LOOP        ctcOrder:=i_orders(idx);        INSERT INTO person          (id,           name,           age,           sex,           password           )        VALUES          (ctcOrder.id,           ctcOrder.name,            ctcOrder.age,             ctcOrder.sex,              ctcOrder.password           );    end loop;    exception when others then    raise;end;

5,建立JAVA端java bean對象

package me.person;public class Person {public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}private int id;private String name;private int age;private String sex;private String password;}

 

6.在JAVA端訪問預存程序插入資料,需要做JAVA資料類型和預存程序類型type的映射,下面的StructDescriptor是mapping Oracle端AS OBJECT類型,
tabDesc 是mapping Oracle端數組 AS TABLE OF類型的.

package me.arrayinsertbatch;import java.sql.CallableStatement;import java.sql.Connection;import java.util.ArrayList;import java.util.List;import org.springframework.stereotype.Service;import me.person.Person;import oracle.sql.ARRAY;import oracle.sql.ArrayDescriptor;import oracle.sql.STRUCT;import oracle.sql.StructDescriptor;@Service("insertbatchservive")public class ArraybatchInsertFromExcel {     public void insertbatchfromexcel() throws Exception//10萬條11秒     {         double begin = System.currentTimeMillis();         Connection con = null;           CallableStatement cstmt = null;                try {                              con = ArrayInsertBatch.getConn();             List<Person> orderList = new ArrayList<Person>();                           for(int i=0;i<100000;i++){                  Person per=new Person();                 per.setId(i);                 per.setName("InsertName"+i);                    per.setPassword("insertpassword"+i);                    per.setSex("男");                    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 = new ArrayList<STRUCT>();               for (Person ord:orderList) {                                   Object[] record = new Object[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 = new STRUCT(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 = new ARRAY(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();                double time = (System.currentTimeMillis() - begin) / 1000;              System.out.println("插入共花費時間" + time + "s");               }         catch(Exception ex)         {                          throw ex;         }    }}

上面的樣本在插入10萬條記錄只用了11秒

 

通過數組方式向Oracle大批量插入資料（10萬條11秒）