【JDBC/Oracle】大量数据插表的最快方式：PreparedStatement的batch操作，对oracle表实验后，发现百万数据只用22秒，千万数据只用138秒！

【实验硬件环境】

T440p

【数据库环境】

Oracle10g，win版

【目标表】

create table emp3(
id number(12),
name nvarchar2(20),
age number(3),
primary key(id)
)

【百万程序】

package com.hy.lab;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class BatchInserter {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public void insert(int count){
        Connection conn = null;
        PreparedStatement pstmt = null;

        try{
            String sql="insert into emp3(id,name,age) values(?,?,?)";

            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);

            for(int i=0;i<count;i++){
                pstmt.setLong(1,i);
                pstmt.setString(2,i+"");
                pstmt.setInt(3,i % 100);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static String ms2DHMS(long startMs, long endMs) {
        String retval = null;
        long secondCount = (endMs - startMs) / 1000;
        String ms = (endMs - startMs) % 1000 + "ms";

        long days = secondCount / (60 * 60 * 24);
        long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);
        long minutes = (secondCount % (60 * 60)) / 60;
        long seconds = secondCount % 60;

        if (days > 0) {
            retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            retval = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            retval = minutes + "m" + seconds + "s";
        } else if(seconds > 0) {
            retval = seconds + "s";
        }else {
            return ms;
        }

        return retval + ms;
    }

    public static void main(String[] args){
        long startMs=System.currentTimeMillis();

        BatchInserter bit=new BatchInserter();
        bit.insert(1000000);

        long endMs=System.currentTimeMillis();
        System.out.println("Time elapsed:"+ms2DHMS(startMs,endMs));
    }
}

【千万程序】

注意，如果直接把上面的参数扩大到千万，会有oom异常，因此我改写了参数，将百万插了十次。

package com.hy.lab;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class BatchInserter2 {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public void insert(int from,int to){
        Connection conn = null;
        PreparedStatement pstmt = null;

        try{
            String sql="insert into emp3(id,name,age) values(?,?,?)";

            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);

            for(int i=from;i<to;i++){
                pstmt.setLong(1,i);
                pstmt.setString(2,i+"");
                pstmt.setInt(3,i % 100);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static String ms2DHMS(long startMs, long endMs) {
        String retval = null;
        long secondCount = (endMs - startMs) / 1000;
        String ms = (endMs - startMs) % 1000 + "ms";

        long days = secondCount / (60 * 60 * 24);
        long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);
        long minutes = (secondCount % (60 * 60)) / 60;
        long seconds = secondCount % 60;

        if (days > 0) {
            retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            retval = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            retval = minutes + "m" + seconds + "s";
        } else if(seconds > 0) {
            retval = seconds + "s";
        }else {
            return ms;
        }

        return retval + ms;
    }

    public static void main(String[] args){
        long startMs=System.currentTimeMillis();

        BatchInserter2 bit=new BatchInserter2();
        for(int i=0;i<10;i++){
            int start=i*1000000;
            int end=(i+1)*1000000;
            bit.insert(start,end);
        }

        long endMs=System.currentTimeMillis();
        System.out.println("Time elapsed:"+ms2DHMS(startMs,endMs));
    }
}

【后记】

这种JDBC原生PreparedStatement批量操作大批数据的方式，比oracle自己的批量插入语句和dbms这种方式快出两个数量级，又具有普适性，很快且便利，建议采用！

参考资料：

https://blog.csdn.net/weixin_30898555/article/details/112126797

END