[转]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 a
Name Type Nullable Default Comments 
---- ------------ -------- ------- -------- 
ID INTEGER Y 
NAME VARCHAR2(20) Y
bulk 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)
as
c integer;
begin

forall 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
    {
    }
    
};