[转]jdbc批量insert———oracle数组类型与forall的应用
原文：http://blog.itpub.net/post/37572/465011
测试java的insert 同使用9i以后的bulk Insert 的速度.
测试结果显示通过bulk Insert 速度相当的快.
100000条记录
insert ,---------------93秒
bulk insert -------------0.441秒
环境:
oracle 10.2.0.3 Windows 2000Server?
java
代码:
?
SQL> desc aName Type Nullable Default Comments ---- ------------ -------- ------- -------- ID INTEGER Y NAME VARCHAR2(20) Ybulk Insert 使用的类型及过程create or replace type i_table is table of number(10);create or replace type v_table is table of varchar2(10);create or replace procedure pro_forall_insert(v_1 i_table,v_2 v_table)asc integer;beginforall i in 1.. v_1.count insert into a values(v_1(i),v_2(i));end;?
?
测试的java代码:
import java.io.*;import java.sql.*;import java.util.*;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.*;import oracle.jdbc.OracleTypes;import oracle.sql.*;import oracle.sql.ARRAY;import oracle.sql.ArrayDescriptor;import oracle.sql.STRUCT;import oracle.sql.StructDescriptor;import java.sql.Connection;import java.sql.DriverManager;import oracle.jdbc.OracleCallableStatement;public class testOracle{ public testOracle() { Connection oraCon = null; PreparedStatement ps = null; Statement st = null; ResultSet rs = null; try { try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException ex) { } oraCon = DriverManager.getConnection("jdbc:oracle:thin:@192.168.15.234:1521:ora10g", "imcs", "imcs"); oraCon.setAutoCommit(false); } catch (SQLException ex) { ex.printStackTrace(); } CallableStatement cstmt = null; oracle.sql.ArrayDescriptor a = null; oracle.sql.ArrayDescriptor b = null; if (1 == 1) { Object[] s1 = new Object[100000]; Object[] s2 = new Object[100000]; for (int i = 0; i < 100000; i++) { s1[i] = new Integer(1); s2[i] = new String("aaa").concat(String.valueOf(i)); } try { a = oracle.sql.ArrayDescriptor.createDescriptor("I_TABLE", oraCon); b = oracle.sql.ArrayDescriptor.createDescriptor("V_TABLE", oraCon); ARRAY a_test = new ARRAY(a, oraCon, s1); ARRAY b_test = new ARRAY(b, oraCon, s2); cstmt = oraCon.prepareCall("{ call pro_forall_insert(?,?) }"); cstmt.setObject(1, a_test); cstmt.setObject(2, b_test); long aaaa = System.currentTimeMillis(); System.out.println(System.currentTimeMillis()); cstmt.execute(); oraCon.commit(); System.out.println(System.currentTimeMillis() - aaaa); } catch (Exception e) { e.printStackTrace(); } } else { try { PreparedStatement oraPs = null; String oraInsertSql = "insert into a values(?,?)"; oraPs = oraCon.prepareStatement(oraInsertSql); long aaaa = System.currentTimeMillis(); System.out.println(System.currentTimeMillis()); for (int i = 0; i < 100000; i++) { oraPs.setInt(1, i); oraPs.setString(2, new String("aaa").concat(String.valueOf(i))); oraPs.executeUpdate(); } oraCon.commit(); System.out.println(System.currentTimeMillis() - aaaa); } catch (SQLException ex) { System.out.print("dddddd"); System.out.print(ex.getMessage()); } } try { jbInit(); } catch (Exception ex) { ex.printStackTrace(); } } public static void main(String args[]) { testOracle a = new testOracle(); } private void jbInit() throws Exception { } };??