在处理大量日志数据库入库时,为了提高速度我们经常用的策略就是批量入库,而不是单条数据提交。
为了最大程度上发挥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
|
|