java批量执行存储过程_java调用存储过程实现批量入库

在处理大量日志数据库入库时，为了提高速度我们经常用的策略就是批量入库，而不是单条数据提交。

为了最大程度上发挥Oracle的性能，可以考虑采用存储过程、type组合来实现批量入库。

1、创建type

CREATE OR REPLACE TYPE type_object AS OBJECT

(

ID  NUMBER,

DAY DATE,

STR VARCHAR2(100)

)

CREATE OR REPLACE TYPE ARRAY_object

AS table OF type_object

2、创建表

create table T_OBJECT

(

ID  NUMBER,

DAY DATE,

STR VARCHAR2(100)

)

3、创建存储过程，收入参数就是ARRAY_object类型。

create or replace procedure p_batch_insert(i_object in array_object) is

begin

insert into t_object

(id, day, str)

select id, day, str

from the (select cast(i_object as array_object) from dual);

end p_batch_insert;

4、测试的JAVA程序

package com;

import java.sql.*;

import com.timesten.jdbc.TimesTenDataSource;

import oracle.jdbc.OraclePreparedStatement;

public class Test4 {

public static void main(String arg[]) {

try {

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

//Class.forName("com.timesten.jdbc.TimesTenDriver");

String url="jdbc:oracle:thin:@192.168.6.100:1521:logstat1";

// String url = "jdbc:timesten:direct:dsn=ttdemo;uid=logstat;pwd=logstat;ORACLEID=logstat_214;OraclePWD=logstat";

Connection con = DriverManager.getConnection(url, "logstat","logstat");

/*TimesTenDataSource ds = new TimesTenDataSource();

ds.setUrl(url);

Connection con = ds.getConnection();

*/

PreparedStatement  pstmt = null;

//oracle.jdbc.OraclePreparedStatement pstmt = null;

String sql = "{call p_batch_insert(?)}";

pstmt = con.prepareCall(sql);

//pstmt =(OraclePreparedStatement) con.prepareCall(sql);

Object[][]  object1=new Object[100][3];

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

object1[i][0]=new Long(i);

object1[i][1]=new java.sql.Date(new java.util.Date().getTime());

String str = "this is a test"+i;

object1[i][2]= str;

}

oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("ARRAY_OBJECT",con);

oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc,con,object1);

pstmt.setArray(1, array);

pstmt.executeUpdate();

//pstmt.executeBatch();

//con.commit();

//con.setAutoCommit(true);

} catch (Exception e) {

e.printStackTrace();

//System.out.println(e.toString());

}

}

}

我们也可以在存储过程中做一些其它逻辑运算，速度要提高不少。

备注：如果在入库的过程中发现字符串的值没有入进去，请检查有没有加载该类库nls_charset12.jar