【JDBC】大批量表填充实验，使用preparestatement的addBatch方式批量填充含15字段表，插入一千二百万静态数据共计耗时17m23s524ms

硬件环境：Thinkpad t440p 4G

软件环境：Oracle11g

运行环境：Idea控制台程序

建表：

create table bulk63(
    id number(20),
    f01 nvarchar2(20),
    f02 nvarchar2(20),
    f03 nvarchar2(20),
    f04 nvarchar2(20),
    f05 nvarchar2(20),
    f06 nvarchar2(20),
    f07 nvarchar2(20),
    f08 nvarchar2(20),
    f09 nvarchar2(20),
    f10 nvarchar2(20),
    f11 nvarchar2(20),
    f12 nvarchar2(20),
    f13 nvarchar2(20),
    f14 nvarchar2(20),
    primary key(id)
);

批量大小：两万五千

耗时：17m23s524ms

 

代码：

 

package com.hy.lab.bulk;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 单表大数据量填充器
 * 用于估算向一个15列的表填充一千二百万数据所需要的时间
 */
public class TableBulkFiller {
    private static final int BATCH_SIZE=25000;

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            String user = "luna";
            String pass = "1234";
            conn = DriverManager.getConnection(url, user, pass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void main(String[] args){
        final String tb="bulk63";
        List<String> colNames=new ArrayList<>();
        List<String> asks=new ArrayList<>();
        for(int i=1;i<15;i++){
            String colName=String.format("f%02d",i);
            colNames.add(colName);
            asks.add("?");
        }

        // insert into bulk63(id,f01,f02,f03,f04,f05,f06,f07,f08,f09,f10,f11,f12,f13,f14) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        String insertSql=String.format("insert into %s(id,%s) values(?,%s)",tb,String.join(",",colNames),String.join(",",asks));
        //System.out.println(insertSql);

        long startMs=System.currentTimeMillis();

        try(Connection conn=getConnection();
            PreparedStatement pstmt = conn.prepareStatement(insertSql)){
            conn.setAutoCommit(false);
            final String FIX_STRING="ABCDEFGHIJ0123456789";

            int count=0;

            for(int i=0;i<12000000;i++){
                pstmt.setLong(1,i);
                for(int j=0;j<14;j++){
                    pstmt.setString(j+2,FIX_STRING);
                }

                pstmt.addBatch();

                count++;
                if(count>BATCH_SIZE){
                    pstmt.executeBatch();
                    conn.commit();
                    count=0;
                    System.out.print("-");
                }
            }

            pstmt.executeBatch();
            conn.commit();

            long endMs=System.currentTimeMillis();
            System.out.println("\nTime elapsed:"+ ms2DHMS(startMs,endMs));
        }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;
    }
}

输出：

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Time elapsed:17m23s524ms

附图：

 

END