在處理大量日志數(shù)據(jù)庫入庫時，為了提高速度我們經(jīng)常用的策略就是批量入庫，而不是單條數(shù)據(jù)提交。

為了最大程度上發(fā)揮Oracle的性能，可以考慮采用存儲過程、type組合來實現(xiàn)批量入庫。

1、創(chuàng)建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、創(chuàng)建表

create table T_OBJECT
(
  ID  NUMBER,
  DAY DATE,
  STR VARCHAR2(100)
)

3、創(chuàng)建存儲過程，收入?yún)?shù)就是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());
  }
 }

}
我們也可以在存儲過程中做一些其它邏輯運算，速度要提高不少。

 

備注：如果在入庫的過程中發(fā)現(xiàn)字符串的值沒有入進去，請檢查有沒有加載該類庫nls_charset12.jar