java 操作Oracle 批量入库的问题

时间：2021-11-09 15:50:58

java 操作Oracle 批量入库的问题

先说下我运行的环境：

Windows7 64位操作系统

(四核)Intel i5-2300 CPU @2.80GHz

内存4G

硬盘1T

Jdk1.6+MyEclipse8.6 for Spring+ojdbc14.jar

Oracle10.2.0+PLSQL7.1.4.1390

情况是这样的，今天一个朋友说用了我说的Oracle的Table类型批量入库过后，感觉慢了点。为此我在不考虑多线程的情况下，分别用“Oracle的 自定义类型定义表类型”、“Oracle的基本类型定义表类型”、“JDBCBATCH”一次性向Oracle插入10000条数据的用时进行了测试。结 果让我很费解。在这里列出来，希望知道的能解答一下，万分感谢。

数据表：

数据表：

-- Create table

create table DATE_JH_INC_UDR_01

(

  SERIAL_NUMBER    VARCHAR2(16) not null,

  OPR_TYPE         VARCHAR2(10) not null,

  SERV_CODE        VARCHAR2(10) not null,

  SP_CODE          VARCHAR2(10) not null,

  OPER_CODE        VARCHAR2(20) not null,

  INPUT_START_DATE DATE,

  INPUT_END_DATE   DATE,

  OPR_TIME         DATE,

  EFF_TIME         DATE not null,

  OPR_SOURCE       VARCHAR2(10),

  THIRD_DN         VARCHAR2(30),

  CHRG_TYPE        VARCHAR2(10)

);

-- Create/Recreate primary, unique and foreign key constraints

alter table DATE_JH_INC_UDR_01

  add constraint PK_DATE_JH_INC_UDR_01 primary key (SERIAL_NUMBER, OPR_TYPE, SERV_CODE, SP_CODE, OPER_CODE, EFF_TIME);

-- Create/Recreate indexes

create index IDX_DATE_JH_INC_UDR_011 on DATE_JH_INC_UDR_01 (SP_CODE, OPER_CODE);

一、采用Oracle“基本类型”定义表类型入库。

　　数据库准备：

　　数据库准备：

CREATE OR REPLACE TYPE D_SERIAL_NUMBER IS TABLE OF NVARCHAR2(16);

    CREATE OR REPLACE TYPE D_OPR_TYPE IS TABLE OF NVARCHAR2(2);

    CREATE OR REPLACE TYPE D_SERV_CODE IS TABLE OF NUMBER(10);

    CREATE OR REPLACE TYPE D_SP_CODE IS TABLE OF NVARCHAR2(10);

    CREATE OR REPLACE TYPE D_OPER_CODE IS TABLE OF NVARCHAR2(20);

    CREATE OR REPLACE TYPE D_INPUT_START_DATE IS TABLE OF NVARCHAR2(25);

    CREATE OR REPLACE TYPE D_INPUT_END_DATE IS TABLE OF NVARCHAR2(25);

    CREATE OR REPLACE TYPE D_OPR_TIME IS TABLE OF NVARCHAR2(25);

    CREATE OR REPLACE TYPE D_EFF_TIME IS TABLE OF NVARCHAR2(25);

    CREATE OR REPLACE TYPE D_OPR_SOURCE IS TABLE OF NVARCHAR2(2);

    CREATE OR REPLACE TYPE D_THIRD_DN IS TABLE OF NVARCHAR2(30);

    CREATE OR REPLACE TYPE D_CHRG_TYPE IS TABLE OF NVARCHAR2(2);

    CREATE OR REPLACE PROCEDURE ARRAY_TO_IBOSS_UDR2(V_1 D_SERIAL_NUMBER,V_2 D_OPR_TYPE,V_3 D_SERV_CODE,V_4 D_SP_CODE,V_5 D_OPER_CODE,V_6 D_INPUT_START_DATE,V_7 D_INPUT_END_DATE,V_8 D_OPR_TIME,V_9 D_EFF_TIME,V_10 D_OPR_SOURCE,V_11 D_THIRD_DN,V_12 D_CHRG_TYPE,O_ERR OUT VARCHAR2,SUCC_COUNT OUT NUMBER,FAIL_COUNT OUT NUMBER)

    AS

    I INTEGER;

    BEGIN

        I:=0;

        SUCC_COUNT:=0;

        FAIL_COUNT:=0;

        WHILE I<V_1.COUNT LOOP

        BEGIN

            I:=I+1;

            INSERT INTO DATE_JH_INC_UDR_01 VALUES(V_1(I),V_2(I),V_3(I),V_4(I),V_5(I),TO_DATE(V_6(I),'YYYY-MM-DD HH24:MI:SS'),TO_DATE(V_7(I),'YYYY-MM-DD HH24:MI:SS'),TO_DATE(V_8(I),'YYYY-MM-DD HH24:MI:SS'),TO_DATE(V_9(I),'YYYY-MM-DD HH24:MI:SS'),V_10(I),V_11(I),V_12(I));

            SUCC_COUNT:=SUCC_COUNT+1;

        EXCEPTION

        WHEN OTHERS THEN

        FAIL_COUNT:=FAIL_COUNT+1;

        O_ERR := I||':'||SQLERRM;

        END;

        END LOOP;

    END;

Java代码：

import java.sql.CallableStatement;

import java.sql.DriverManager;

import java.util.ArrayList;

import java.util.List;

import oracle.jdbc.OracleConnection;

import oracle.sql.ARRAY;

public class AutoMcasDataDeal {

    @SuppressWarnings("unchecked")

    public static void main(String[] args) throws Exception

    {

        System.out.println("----------这是使用Oracle“基本类型”定义表类型入库--------");

        long datebegin = System.currentTimeMillis();

        try{

            int succcount=0;

            int failcount=0;

            long datebegin1 = System.currentTimeMillis();

            List s1 = new ArrayList();

            List s2 = new ArrayList();

            List s3 = new ArrayList();

            List s4 = new ArrayList();

            List s5 = new ArrayList();

            List s6 = new ArrayList();

            List s7 = new ArrayList();

            List s8 = new ArrayList();

            List s9 = new ArrayList();

            List s10 = new ArrayList();

            List s11 = new ArrayList();

            List s12 = new ArrayList();

            oracle.sql.ArrayDescriptor a = null;

            oracle.sql.ArrayDescriptor b = null;

            oracle.sql.ArrayDescriptor c = null;

            oracle.sql.ArrayDescriptor d = null;

            oracle.sql.ArrayDescriptor e = null;

            oracle.sql.ArrayDescriptor f = null;

            oracle.sql.ArrayDescriptor g = null;

            oracle.sql.ArrayDescriptor h = null;

            oracle.sql.ArrayDescriptor j = null;

            oracle.sql.ArrayDescriptor k = null;

            oracle.sql.ArrayDescriptor l = null;

            oracle.sql.ArrayDescriptor m = null;

            for (int i = 0; i < 10000; i++) {

                s1.add(i);

                s2.add("0000");

                s3.add("0000");

                s4.add("0000");

                s5.add("0000");

                s6.add("20131226000000");

                s7.add("20131226235959");

                s8.add("20131227120054");

                s9.add("20131228120054");

                s10.add("0000");

                s11.add("0000");

                s12.add("0000");

            }

            long end1 = System.currentTimeMillis();

            System.out.println("组装list用时："+(end1-datebegin1)+"毫秒");

            long date1 = System.currentTimeMillis();

            OracleConnection conn = null;

            CallableStatement cstmt = null;

            Class.forName("oracle.jdbc.driver.OracleDriver");

            conn = (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@"+"127.0.0.1"+":1521:"+"orcl", "test", "root");

            a = oracle.sql.ArrayDescriptor.createDescriptor("D_SERIAL_NUMBER", conn);

            b = oracle.sql.ArrayDescriptor.createDescriptor("D_OPR_TYPE", conn);

            c = oracle.sql.ArrayDescriptor.createDescriptor("D_SERV_CODE", conn);

            d = oracle.sql.ArrayDescriptor.createDescriptor("D_SP_CODE", conn);

            e = oracle.sql.ArrayDescriptor.createDescriptor("D_OPER_CODE", conn);

            f = oracle.sql.ArrayDescriptor.createDescriptor("D_INPUT_START_DATE", conn);

            g = oracle.sql.ArrayDescriptor.createDescriptor("D_INPUT_END_DATE", conn);

            h = oracle.sql.ArrayDescriptor.createDescriptor("D_OPR_TIME", conn);

            j = oracle.sql.ArrayDescriptor.createDescriptor("D_EFF_TIME", conn);

            k = oracle.sql.ArrayDescriptor.createDescriptor("D_OPR_SOURCE", conn);

            l = oracle.sql.ArrayDescriptor.createDescriptor("D_THIRD_DN", conn);

            m = oracle.sql.ArrayDescriptor.createDescriptor("D_CHRG_TYPE", conn);

            long date2 = System.currentTimeMillis();

            System.out.println("数据库连接准备用时："+(date2-date1)+"毫秒");

            long array1 = System.currentTimeMillis();

            ARRAY a_test = new ARRAY(a, conn, s1.toArray());

            ARRAY b_test = new ARRAY(b, conn, s2.toArray());

            ARRAY c_test = new ARRAY(c, conn, s3.toArray());

            ARRAY d_test = new ARRAY(d, conn, s4.toArray());

            ARRAY e_test = new ARRAY(e, conn, s5.toArray());

            ARRAY f_test = new ARRAY(f, conn, s6.toArray());

            ARRAY g_test = new ARRAY(g, conn, s7.toArray());

            ARRAY h_test = new ARRAY(h, conn, s8.toArray());

            ARRAY j_test = new ARRAY(j, conn, s9.toArray());

            ARRAY k_test = new ARRAY(k, conn, s10.toArray());

            ARRAY l_test = new ARRAY(l, conn, s11.toArray());

            ARRAY m_test = new ARRAY(m, conn, s12.toArray());

            long array2 = System.currentTimeMillis();

            System.err.println("组装ARRAY用时："+(array2-array1)+"毫秒");

            long params1 = System.currentTimeMillis();

            cstmt = conn.prepareCall("{ call array_to_iboss_udr2(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");

            cstmt.setObject(1, a_test);

            cstmt.setObject(2, b_test);

            cstmt.setObject(3, c_test);

            cstmt.setObject(4, d_test);

            cstmt.setObject(5, e_test);

            cstmt.setObject(6, f_test);

            cstmt.setObject(7, g_test);

            cstmt.setObject(8, h_test);

            cstmt.setObject(9, j_test);

            cstmt.setObject(10, k_test);

            cstmt.setObject(11, l_test);

            cstmt.setObject(12, m_test);

            cstmt.registerOutParameter(13, java.sql.Types.VARCHAR);

            cstmt.registerOutParameter(14, java.sql.Types.NUMERIC);

            cstmt.registerOutParameter(15, java.sql.Types.NUMERIC);

            long params2 = System.currentTimeMillis();

            System.out.println("设置参数用时："+(params2-params1)+"毫秒");

            long exe1 = System.currentTimeMillis();

            cstmt.execute();

            long exe2 = System.currentTimeMillis();

            System.err.println("执行用时："+(exe2-exe1)+"毫秒");

            long cl1 = System.currentTimeMillis();

            String error_info = cstmt.getString(13);

            succcount = succcount+cstmt.getInt(14);

            failcount = failcount+cstmt.getInt(15);

            if(error_info!=null)System.out.println(error_info);

            long cl2 = System.currentTimeMillis();

            cstmt.close();

            conn.close();

            System.out.println("获取返回参数及关闭用时："+(cl2-cl1)+"毫秒");

        }

        catch(Exception e){

            e.printStackTrace();

        }

        long end = System.currentTimeMillis();

        System.out.println("入库总用时："+(end-datebegin)+"毫秒"+"  秒数为："+((end-datebegin)/1000.0));

    }

}

执行之前清除数据：TRUNCATE TABLE DATE_JH_INC_UDR_01;

测试结果：

java 操作Oracle 批量入库的问题

二、采用Oracle“自定义类型”定义表类型入库。

数据库类型准备：

　　--创建一个Object类型

　　CREATE OR REPLACE TYPE o_d_type AS OBJECT

　　(

　　  SERIAL_NUMBER    NVARCHAR2(16),

　　  OPR_TYPE         NVARCHAR2(10),

　　  SERV_CODE        NVARCHAR2(10),

　　  SP_CODE          NVARCHAR2(10),

　　  OPER_CODE        NVARCHAR2(20),

　　  INPUT_START_DATE NVARCHAR2(30),

　　  INPUT_END_DATE   NVARCHAR2(30),

　　  OPR_TIME         NVARCHAR2(30),

　　  EFF_TIME         NVARCHAR2(30),

　　  OPR_SOURCE       NVARCHAR2(10),

　　  THIRD_DN         NVARCHAR2(30),

　　  CHRG_TYPE        NVARCHAR2(10)

　　);

　　--创建一个Table类型——对象表

　　CREATE OR REPLACE TYPE t_d_type AS TABLE OF o_d_type;

　　

　　/*

　　 * 批量入库存储过程

　　 */

　　CREATE OR REPLACE PROCEDURE ARRAY_TO_IBOSS_UDR(V_1        IN T_D_TYPE, --table类型参数

　　                                               O_ERR      OUT VARCHAR2,

　　                                               SUCC_COUNT OUT NUMBER,

　　                                               FAIL_COUNT OUT NUMBER) AS

　　  I      INTEGER;

　　  V_TYPE O_D_TYPE; --object 类型变量

　　BEGIN

　　  I          := 0;

　　  SUCC_COUNT := 0;

　　  FAIL_COUNT := 0;

　　  WHILE I < V_1.COUNT LOOP

　　    BEGIN

　　      I      := I + 1;

　　      V_TYPE := V_1(I); --将table里的值赋给object

　　      INSERT INTO DATE_JH_INC_UDR_01

　　      VALUES

　　        (V_TYPE.SERIAL_NUMBER,

　　         V_TYPE.OPR_TYPE,

　　         V_TYPE.SERV_CODE,

　　         V_TYPE.SP_CODE,

　　         V_TYPE.OPER_CODE,

　　         TO_DATE(V_TYPE.INPUT_START_DATE, 'YYYY-MM-DD HH24:MI:SS'),

　　         TO_DATE(V_TYPE.INPUT_END_DATE, 'YYYY-MM-DD HH24:MI:SS'),

　　         TO_DATE(V_TYPE.OPR_TIME, 'YYYY-MM-DD HH24:MI:SS'),

　　         TO_DATE(V_TYPE.EFF_TIME, 'YYYY-MM-DD HH24:MI:SS'),

　　         V_TYPE.OPR_SOURCE,

　　         V_TYPE.THIRD_DN,

　　         V_TYPE.CHRG_TYPE);

　　      SUCC_COUNT := SUCC_COUNT + 1;

　　    EXCEPTION

　　      WHEN OTHERS THEN

　　        FAIL_COUNT := FAIL_COUNT + 1;

　　        O_ERR      := I || ':' || SQLERRM;

　　    END;

　　  END LOOP;

　　END;

Java代码：

import java.sql.CallableStatement;

import java.sql.DriverManager;

import java.util.ArrayList;

import java.util.List;

import oracle.jdbc.OracleConnection;

import oracle.sql.ARRAY;

public class Test {

    /**

     * @param args

     */

    @SuppressWarnings("unchecked")

    public static void main(String[] args) {

        System.out.println("----------这是使用Oracle“自定义类型”定义表类型入库--------");

        long datebegin = System.currentTimeMillis();

        try{

            int succcount=0;

            int failcount=0;

            long datebegin1 = System.currentTimeMillis();

            List s = new ArrayList();

            for (int i = 0; i < 10000; i++) {

                Object[] o1 = new Object[12];

                o1[0] = "0"+i;

                o1[1] = "0";

                o1[2] = "0";

                o1[3] = "0";

                o1[4] = "0";

                o1[5] = "20131226";

                o1[6] = "20131226";

                o1[7] = "20131226";

                o1[8] = "20131226";

                o1[9] = "0"+i;

                o1[10] = "0";

                o1[11] = "0";

                s.add(o1);

            }

            long end1 = System.currentTimeMillis();

            System.out.println("组装list用时："+(end1-datebegin1)+"毫秒");

            long date1 = System.currentTimeMillis();

            OracleConnection conn = null;

            CallableStatement cstmt = null;

            oracle.sql.ArrayDescriptor a = null;

            Class.forName("oracle.jdbc.driver.OracleDriver");

            conn = (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@"+"127.0.0.1"+":1521:"+"orcl", "test", "root");

            a = oracle.sql.ArrayDescriptor.createDescriptor("T_D_TYPE", conn);

            long date2 = System.currentTimeMillis();

            System.out.println("数据库连接准备用时："+(date2-date1)+"毫秒");

            long array1 = System.currentTimeMillis();

            ARRAY a_test = new ARRAY(a, conn, s.toArray());

            long array2 = System.currentTimeMillis();

            System.err.println("组装ARRAY用时："+(array2-array1)+"毫秒");

            long params1 = System.currentTimeMillis();

            cstmt = conn.prepareCall("{ call array_to_iboss_udr(?,?,?,?) }");

            cstmt.setObject(1, a_test);

            cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);

            cstmt.registerOutParameter(3, java.sql.Types.NUMERIC);

            cstmt.registerOutParameter(4, java.sql.Types.NUMERIC);

            long params2 = System.currentTimeMillis();

            System.out.println("设置参数用时："+(params2-params1)+"毫秒");

            long exe1 = System.currentTimeMillis();

            cstmt.execute();

            long exe2 = System.currentTimeMillis();

            System.err.println("执行用时："+(exe2-exe1)+"毫秒");

            long cl1 = System.currentTimeMillis();

            String error_info = cstmt.getString(2);

            succcount = succcount+cstmt.getInt(3);

            failcount = failcount+cstmt.getInt(4);

            if(error_info!=null)System.out.println(error_info);

            cstmt.close();

            conn.close();

            long cl2 = System.currentTimeMillis();

            System.out.println("获取返回参数及关闭用时："+(cl2-cl1)+"毫秒");

        }

        catch(Exception e){

            e.printStackTrace();

        }

        long end = System.currentTimeMillis();

        System.out.println("入库总用时："+(end-datebegin)+"毫秒"+"  秒数为："+((end-datebegin)/1000.0));

    }

　　}

执行之前清除数据：TRUNCATE TABLE DATE_JH_INC_UDR_01;

测试结果：

java 操作Oracle 批量入库的问题

三、采用“JDBCBATCH”进行批量入库。

　　数据库不用存储过程，就一张表放在那里就好

Java程序：

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.sql.Date;

import java.util.List;

import oracle.jdbc.OracleConnection;

public class Jdbc {

    /**

     * @param args

     * @throws ClassNotFoundException

     * @throws SQLException

     * @throws ParseException

     */

    public static void main(String[] args) throws ClassNotFoundException, SQLException, ParseException {

        System.out.println("----------这是使用JDBC入库--------");

        long datebegin = System.currentTimeMillis();

        List<Object[]> s = new ArrayList<Object[]>();

        for (int i = 0; i < 10000; i++) {

            Object[] o1 = new Object[12];

            o1[0] = "0"+i;

            o1[1] = "0";

            o1[2] = "0";

            o1[3] = "0";

            o1[4] = "0";

            o1[5] = "20131226";

            o1[6] = "20131226";

            o1[7] = "20131226";

            o1[8] = "20131226";

            o1[9] = "0"+i;

            o1[10] = "0";

            o1[11] = "0";

            s.add(o1);

        }

        long end1 = System.currentTimeMillis();

        System.out.println("组装list用时："+(end1-datebegin)+"毫秒");

        long date1 = System.currentTimeMillis();

        Class.forName("oracle.jdbc.driver.OracleDriver");

        OracleConnection conn = null;

        conn = (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@"+"127.0.0.1"+":1521:"+"orcl", "test", "root");

        String sql = "INSERT INTO DATE_JH_INC_UDR_01 VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

        PreparedStatement ps = conn.prepareStatement(sql);

        long date2 = System.currentTimeMillis();

        System.out.println("数据库连接准备用时："+(date2-date1)+"毫秒");

        long array1 = System.currentTimeMillis();

        //设置1w条提交一次

        /*final int batchSize = 10000;

        int count = 0;*/

        for (Object[] obj: s) {

            ps.setString(1, obj[0].toString());

            ps.setString(2, obj[1].toString());

            ps.setString(3, obj[2].toString());

            ps.setString(4, obj[3].toString());

            ps.setString(5, obj[4].toString());

            ps.setDate(6, getDate(obj[5].toString()));

            ps.setDate(7, getDate(obj[6].toString()));

            ps.setDate(8, getDate(obj[7].toString()));

            ps.setDate(9, getDate(obj[8].toString()));

            ps.setString(10, obj[9].toString());

            ps.setString(11, obj[10].toString());

            ps.setString(12, obj[11].toString());

            ps.addBatch();

            /*if(++count % batchSize == 0) {

                ps.executeBatch();

            }*/

        }

        long array2 = System.currentTimeMillis();

        System.err.println("组装SQL用时："+(array2-array1)+"毫秒");

        long params1 = System.currentTimeMillis();

        ps.executeBatch();

        long params2 = System.currentTimeMillis();

        System.out.println("执行用时："+(params2-params1)+"毫秒");

        long cl1 = System.currentTimeMillis();

        ps.close();

        conn.close();

        long cl2 = System.currentTimeMillis();

        System.out.println("关闭用时："+(cl2-cl1)+"毫秒");

        long end = System.currentTimeMillis();

        System.out.println("入库总用时："+(end-datebegin)+"毫秒"+"  秒数为："+((end-datebegin)/1000.0));

    }

    public static Date getDate(String str) throws ParseException{

        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");

        return new Date(df.parse(str).getTime());

    }

　　}

执行之前清除数据：TRUNCATE TABLE DATE_JH_INC_UDR_01;

测试结果：

java 操作Oracle 批量入库的问题

这让我灰常怀疑Oracle做这个数据接口出来干什么？
　　几个问题，想问问大家。
　　第一，对这种大批量的数据入库，各位采用的都是用什么方式+多线程？
　　第二，Oracle提供ARRAY跟TABLE跟OBJECT类型拿来入库的话，是不是性能真的像我测试这样？
　　第三，如果你们用的不是JDBC的Batch方法，并且比它更快的，贴出来让我学习学习吧！
　　第四，这里不考虑多线程问题，当然实际应用中多线程肯定要加上去的。
　　第五，向看过我Oracle的ARRAY，TABLE类型批量入库的并且还拿到生产环境上去使用的同学致以歉意，是我坑了你们......

转载地址：http://bbs.csdn.net/topics/390733080

相关文章