JDBC 百万级别的批量插入

1，首先创建一张表

DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE 
   (  FIRST_NAME VARCHAR2(20 BYTE), 
      LAST_NAME VARCHAR2(20 BYTE),
      EMP_NO NUMBER, 
      JOIN_DATE DATE
   ) 
TABLESPACE SYSTEM ;</span>

2，自定义一个行类型

create or replace
TYPE t_type AS OBJECT (
  first_name varchar2(20),
  last_name varchar2(20),
  emp_no number,
  join_date date
);

3，再定义一个百万量级的数组

create or replace
type tb_t_type
as varray (1000000) of t_type;

4，最后我们再写一个批量插入的存储过程；

create or replace
procedure add_employees (emparray in tb_t_type) as

begin
  forall i in emparray.first .. emparray.last
    insert into EMPLOYEE( first_name,                
                          last_name,
                          emp_no,
                          join_date )
        
    values( emparray(i).first_name,
            emparray(i).last_name,
            emparray(i).emp_no, 
            emparray(i).join_date );

end add_employees;

5，接下来就是编程了

public class TestInsert {

	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
			OracleDataSource oracle=new OracleDataSource();
			
			oracle.setURL("jdbc:oracle:thin:@127.0.0.1:1521:orcl");oracle.setUser("project");oracle.setPassword("project");
			
			OracleConnection conn=(OracleConnection)oracle.getConnection();
			
			StructDescriptor sd=StructDescriptor.createDescriptor("T_TYPE", conn);
			
			int struct_size=Integer.parseInt("100000");
			
			STRUCT [] struct=new STRUCT[struct_size];
			
			for(int i=0;i<struct_size;i++){
				
				Object [] obj={"first_name "+i,"last_name "+i,i,new Date(System.currentTimeMillis())};
				
				struct[i]=new STRUCT(sd,conn,obj);
				
			}
			
			ArrayDescriptor ad=ArrayDescriptor.createDescriptor("TB_T_TYPE", conn);
			
			ARRAY emp_array=new ARRAY(ad,conn,struct);
			
			OraclePreparedStatement ops=(OraclePreparedStatement)conn.prepareStatement("call add_employees(?)");
			
			ops.setObject(1, emp_array);
			
			((OraclePreparedStatement)ops).execute();
			
			conn.commit();
			ops.close();
			conn.close();
	}

}