JDBC批處理

有兩張表test和test1表結構，字段名稱，字段數據類型完全一致，如圖，test1中有數據1966088條，test表是一張空表。

1、首先不采用批處理，從test1表中查詢出所有數據，再存入test表。程序運行運行時間打印輸出。

import java.sql.*;


public class Test
{
private static String driver = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
private static String username = "scott";
private static String password = "tiger";
public static Connection getConnection()throws Exception{
Connection conn = null;
Class.forName(driver);
conn = DriverManager.getConnection(url,username,password);
return conn;
}
public static void main(String[] args){
long T1 = System.currentTimeMillis();
String sql1 = "select order_num from test1";
String sql2 = "insert into test (order_num) values (?)";
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
conn = getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql2);
rs = stmt.executeQuery(sql1);
int n_count = 0;
while(rs.next()){
pstmt.clearParameters();
pstmt.setInt(1,rs.getInt(1));
pstmt.executeUpdate();
n_count++;
}
conn.commit();
conn.setAutoCommit(true);
}catch (Exception e){
try{
conn.rollback();
}catch(Exception e1){
e.printStackTrace();
}
e.printStackTrace();
}finally{
if(rs != null){
try{
rs.close();
rs = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(stmt != null){
try{
stmt.close();
stmt = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(pstmt != null){
try{
pstmt.close();
pstmt = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(conn != null){
try{
conn.close();
conn = null;
}catch (Exception e){
e.printStackTrace();
}
}
}
long T2 = System.currentTimeMillis();
long T = T2-T1;
System.out.println("不使用批處理程序運行了"+T+"毫秒");
}

}

下面我們再清空test表，使用批處理完成同樣的事情：

import java.sql.*;
public class Test
{
private static String driver = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
private static String username = "scott";
private static String password = "tiger";
public static Connection getConnection()throws Exception{
Connection conn = null;
Class.forName(driver);
conn = DriverManager.getConnection(url,username,password);
return conn;
}
public static void main(String[] args){
long T1 = System.currentTimeMillis();
String sql1 = "select order_num from test1";
String sql2 = "insert into test (order_num) values (?)";
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
conn = getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql2);
rs = stmt.executeQuery(sql1);
while(rs.next()){

pstmt.setInt(1,rs.getInt(1));
pstmt.addBatch();
}
pstmt.executeBatch();

pstmt.clearBatch();

conn.commit();
conn.setAutoCommit(true);
}catch (Exception e){
try{
conn.rollback();
}catch(Exception e1){
e.printStackTrace();
}
e.printStackTrace();
}finally{
if(rs != null){
try{
rs.close();
rs = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(stmt != null){
try{
stmt.close();
stmt = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(pstmt != null){
try{
pstmt.close();
pstmt = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(conn != null){
try{
conn.close();
conn = null;
}catch (Exception e){
e.printStackTrace();
}
}
}
long T2 = System.currentTimeMillis();
long T = T2-T1;
System.out.println("使用批處理程序運行了"+T+"毫秒");
}
}

重新編譯運行結果發生：

然后我再while循環中增加計數器，每5000條執行一次pstmt.executeBatch();

import java.sql.*;
public class Test
{
private static String driver = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
private static String username = "scott";
private static String password = "tiger";
public static Connection getConnection()throws Exception{
Connection conn = null;
Class.forName(driver);
conn = DriverManager.getConnection(url,username,password);
return conn;
}
public static void main(String[] args){
long T1 = System.currentTimeMillis();
String sql1 = "select order_num from test1";
String sql2 = "insert into test (order_num) values (?)";
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int count = 0;
try{
conn = getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql2);
rs = stmt.executeQuery(sql1);
while(rs.next()){

pstmt.setInt(1,rs.getInt(1));
pstmt.addBatch();
count++;
if(count%5000==0){
pstmt.executeBatch();

pstmt.clearBatch();

}
}
pstmt.executeBatch();

pstmt.clearBatch();

conn.commit();
conn.setAutoCommit(true);
}catch (Exception e){
try{
conn.rollback();
}catch(Exception e1){
e.printStackTrace();
}
e.printStackTrace();
}finally{
if(rs != null){
try{
rs.close();
rs = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(stmt != null){
try{
stmt.close();
stmt = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(pstmt != null){
try{
pstmt.close();
pstmt = null;
}catch (Exception e){
e.printStackTrace();
}
}
if(conn != null){
try{
conn.close();
conn = null;
}catch (Exception e){
e.printStackTrace();
}
}
}
long T2 = System.currentTimeMillis();
long T = T2-T1;
System.out.println("使用批處理程序運行了"+T+"毫秒");
}
}

執行結果為：

總結：

1、通過比較可以得出結論使用批處理比不使用批處理程序執行速度要快很多（批處理：29224毫秒相當于29秒，不使用批處理：355859毫秒相當于356秒，不使用批處理耗時是使用批處理耗時的12倍）。

2、使用批處理同樣要小心，要防止數據過大，內存溢出，可以考慮對所有數據在循環再分批處理，循環結束后再對剩余的數據再進行一次批處理。

3、還有一個需要小心的地方是，使用了批處理執行，就不要再使用pstmt.executeUpdate()了，因為這樣相當于重復執行，而且還會報錯。

pstmt.clearBatch();