標(biāo)題: java調(diào)用存儲(chǔ)過程實(shí)現(xiàn)批量入庫 [打印本頁] 作者: olivenan 時(shí)間: 2008-07-24 16:27 標(biāo)題: java調(diào)用存儲(chǔ)過程實(shí)現(xiàn)批量入庫 作者： olivenan@hotmail.com
在處理大量日志數(shù)據(jù)庫入庫時(shí)，為了提高速度我們經(jīng)常用的策略就是批量入庫，而不是單條數(shù)據(jù)提交。
為了最大程度上發(fā)揮Oracle的性能，可以考慮采用存儲(chǔ)過程、type組合來實(shí)現(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)建存儲(chǔ)過程，收入?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、測(cè)試的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
}
我們也可以在存儲(chǔ)過程中做一些其它邏輯運(yùn)算，速度要提高不少。