java 批量入库oracle_java 操作Oracle 批量入库的问题

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 tableDATE_JH_INC_UDR_01

(

SERIAL_NUMBERVARCHAR2(16) not null,

OPR_TYPEVARCHAR2(10) not null,

SERV_CODEVARCHAR2(10) not null,

SP_CODEVARCHAR2(10) not null,

OPER_CODEVARCHAR2(20) not null,

INPUT_START_DATE DATE,

INPUT_END_DATE DATE,

OPR_TIME DATE,

EFF_TIME DATEnot null,

OPR_SOURCEVARCHAR2(10),

THIRD_DNVARCHAR2(30),

CHRG_TYPEVARCHAR2(10)

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

alter tableDATE_JH_INC_UDR_01add 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)ASIINTEGER;BEGINI:=0;

SUCC_COUNT:=0;

FAIL_COUNT:=0;WHILE I

SUCC_COUNT:=SUCC_COUNT+1;

EXCEPTIONWHEN OTHERS THENFAIL_COUNT:=FAIL_COUNT+1;

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

Java代码：

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

importjava.sql.CallableStatement;importjava.sql.DriverManager;importjava.util.ArrayList;importjava.util.List;importoracle.jdbc.OracleConnection;importoracle.sql.ARRAY;public classAutoMcasDataDeal {

@SuppressWarnings("unchecked")public static void main(String[] args) throwsException

{

System.out.println("----------这是使用Oracle“基本类型”定义表类型入库--------");long datebegin =System.currentTimeMillis();try{int succcount=0;int failcount=0;long datebegin1 =System.currentTimeMillis();

List s1= newArrayList();

List s2= newArrayList();

List s3= newArrayList();

List s4= newArrayList();

List s5= newArrayList();

List s6= newArrayList();

List s7= newArrayList();

List s8= newArrayList();

List s9= newArrayList();

List s10= newArrayList();

List s11= newArrayList();

List s12= newArrayList();

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= newARRAY(a, conn, s1.toArray());

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

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

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

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

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

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

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

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

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

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

ARRAY m_test= newARRAY(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));

}

}

View Code

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

测试结果：

1394874583_999355.png

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

数据库类型准备：

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

--创建一个Object类型

CREATE OR REPLACE TYPE o_d_type ASOBJECT

(

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 OFo_d_type;/** 批量入库存储过程*/

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

O_ERR OUT VARCHAR2,

SUCC_COUNT OUTNUMBER,

FAIL_COUNT OUTNUMBER) ASIINTEGER;

V_TYPE O_D_TYPE;--object 类型变量

BEGINI := 0;

SUCC_COUNT := 0;

FAIL_COUNT := 0;WHILE I < V_1.COUNTLOOPBEGINI := I + 1;

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

INSERT INTODATE_JH_INC_UDR_01VALUES(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;

EXCEPTIONWHEN OTHERS THENFAIL_COUNT := FAIL_COUNT + 1;

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

View Code

Java代码：

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

importjava.sql.CallableStatement;importjava.sql.DriverManager;importjava.util.ArrayList;importjava.util.List;importoracle.jdbc.OracleConnection;importoracle.sql.ARRAY;public classTest {/***@paramargs*/@SuppressWarnings("unchecked")public static voidmain(String[] args) {

System.out.println("----------这是使用Oracle“自定义类型”定义表类型入库--------");long datebegin =System.currentTimeMillis();try{int succcount=0;int failcount=0;long datebegin1 =System.currentTimeMillis();

List s= newArrayList();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= newARRAY(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));

}

}

View Code

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

测试结果：

1394874737_626003.png

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

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

Java程序：

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;importjava.text.ParseException;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.sql.Date;importjava.util.List;importoracle.jdbc.OracleConnection;public classJdbc {/***@paramargs

*@throwsClassNotFoundException

*@throwsSQLException

*@throwsParseException*/

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

System.out.println("----------这是使用JDBC入库--------");long datebegin =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-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) throwsParseException{

SimpleDateFormat df= new SimpleDateFormat("yyyyMMdd");return newDate(df.parse(str).getTime());

}

}

View Code

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

测试结果：

1394874790_491680.png

这让我灰常怀疑Oracle做这个数据接口出来干什么？

几个问题，想问问大家。

第一，对这种大批量的数据入库，各位采用的都是用什么方式+多线程？

第二，Oracle提供ARRAY跟TABLE跟OBJECT类型拿来入库的话，是不是性能真的像我测试这样？

第三，如果你们用的不是JDBC的Batch方法，并且比它更快的，贴出来让我学习学习吧！

第四，这里不考虑多线程问题，当然实际应用中多线程肯定要加上去的。

第五，向看过我Oracle的ARRAY，TABLE类型批量入库的并且还拿到生产环境上去使用的同学致以歉意，是我坑了你们......

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

版权声明：本文为weixin_28893597原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。