使用JDBC连接Oracle数据库进行批量数据导入的时候,特别是大批量数据连续插入(百万级或以上),如何提高效率呢?
在JDBC编程接口中有两个方法特别值得注意:
(1)void addBatch() throws SQLException
Adds a set of parameters to this
PreparedStatement
object's batch of commands.
(2)int[] executeBatch() throws SQLException
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The
int
elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch.
通过使用addBatch()和executeBatch()这一对方法可以实现批量处理数据。
不过值得注意的是,首先需要在
数据库链接中设置手动提交,connection.setAutoCommit(false),然后在执行Statement之后执行
connection.commit()。
详细步骤如下:
(1)获取数据库连接
1》Oracle数据库连接
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import com.test.jdbc.IDBConnection;
/**
* 取得Oracle数据库连接
* */
public class OracleDBConnection implements IDBConnection {
//DB Driver
private static final String DBDRIVER="oracle.jdbc.driver.OracleDriver";
//DB URL
private static final String DBURL="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
//DB User
private static final String DBUSER="xxtmanage";
//DB Password
private static final String DBPASSWORLD="xxtinterface";
//DB Connection
private Connection conn=null;
//structor
public OracleDBConnection() {//在构造方法中进行数据库连接
System.out.println("OracleDBConnection.OracleDBConnection()");
try {
// 加载驱动程序
Class.forName(DBDRIVER);
//连接数据库
this.conn=DriverManag