【Jdbc/Oracle】一个用于创建大表并填充批量数据的类 给31字段表填充千万数据约需30-40分钟

这个类是我用来做数据迁移测试的，里面把删表、建表、充值都融合到了一起，字段采用f1,f2,f3...fn的方式递增，字段类型除id外都是nvarchar2，填充的都是固定数据，大家拿下去后可以根据自己的需求修改。

速度方面，在我的老伙计T440p上，

如果是31个字段，一千万条数据，用BATCH_SIZE控制十万提交一次，大约需要38分钟；

同样是31个字段，一千万条数据，控制 五十万提交一次，大约需要33分钟。

下面是代码：

package com.hy.lab.inject;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class BigtableInjecter {
    //-- 以下为连接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";
    private static final int    BATCH_SIZE=100000;

    /**
     * 创建一张大表
     * @param tableName 表名
     * @param colCnt 除id之外的列数
     * @param rowCnt 记录数
     * @return
     */
    public boolean buildTable(String tableName,int colCnt,int rowCnt){
        List columns=new ArrayList<>();
        for(int i=0;i){
            String columnName="F"+(i+1);
            columns.add(columnName);
        }

        // Create Table sql
        StringBuilder sb=new StringBuilder();
        sb.append(String.format("create table %s(",tableName));
        sb.append("    id number(12),");
        for(String column:columns){
            sb.append(String.format("%s nvarchar2(20),",column));
        }
        sb.append("    primary key(id)");
        sb.append(")");
        String createTableSql=sb.toString();
        //System.out.println(createTableSql);

        // Create Insert Sql
        String[] asks=new String[colCnt];
        for(int i=0;i){
            asks[i]="?";
        }
        String insertSql=String.format("insert into %s(id,%s) values(?,%s)",tableName,String.join(",",columns),String.join(",",asks));

        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;

        try{
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);
            stmt = conn.createStatement();

            // drop table if exists
            if(isTableExist(tableName,conn)){
                System.out.println(String.format("发现表%s已存在",tableName));
                String dropSql=String.format("drop table %s",tableName);
                stmt.execute(dropSql);
                System.out.println(String.format("表%s已删除",tableName));
            }else{
                System.out.println(String.format("发现表%s不存在,无需删除",tableName));
            }

            // create table
            stmt.execute(createTableSql);
            System.out.println(String.format("表%s已建成",tableName));

            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(insertSql);
            final String FIX_STRING="ABCDEFGHIJ0123456789";

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

                pstmt.addBatch();

                count++;
                if(count>BATCH_SIZE){
                    pstmt.executeBatch();
                    conn.commit();
                    count=0;

                    submitCount++;
                    System.out.println(String.format("第%-3d次提交",submitCount));
                }
            }

            pstmt.executeBatch();
            conn.commit();
            submitCount++;
            System.out.println(String.format("第%-3d次提交",submitCount));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                pstmt.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return true;
    }

    private boolean isTableExist(String tablename,Connection conn) throws Exception{
        DatabaseMetaData meta= conn.getMetaData();

        ResultSet set=meta.getTables(null,null,tablename.toUpperCase(),null);
        if(set.next()){
            return true;
        }

        return false;
    }

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

        BigtableInjecter injecter=new BigtableInjecter();
        injecter.buildTable("emp_30",30,10000000);

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

    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;
    }
}

输出：

C:\Java17\bin\java.exe "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.3.2\lib\idea_rt.jar=60327:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.3.2\bin" -Dfile.encoding=UTF-8 -classpath C:\Users\ufo\IdeaProjects\dataMigration2\target\classes;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot-starter\2.6.4\spring-boot-starter-2.6.4.jar;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot\2.6.4\spring-boot-2.6.4.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-context\5.3.16\spring-context-5.3.16.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-aop\5.3.16\spring-aop-5.3.16.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-beans\5.3.16\spring-beans-5.3.16.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-expression\5.3.16\spring-expression-5.3.16.jar;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot-autoconfigure\2.6.4\spring-boot-autoconfigure-2.6.4.jar;C:\Users\ufo\.m2\repository\org\springframework\boot\spring-boot-starter-logging\2.6.4\spring-boot-starter-logging-2.6.4.jar;C:\Users\ufo\.m2\repository\ch\qos\logback\logback-classic\1.2.10\logback-classic-1.2.10.jar;C:\Users\ufo\.m2\repository\ch\qos\logback\logback-core\1.2.10\logback-core-1.2.10.jar;C:\Users\ufo\.m2\repository\org\slf4j\slf4j-api\1.7.32\slf4j-api-1.7.32.jar;C:\Users\ufo\.m2\repository\org\apache\logging\log4j\log4j-to-slf4j\2.17.1\log4j-to-slf4j-2.17.1.jar;C:\Users\ufo\.m2\repository\org\apache\logging\log4j\log4j-api\2.17.1\log4j-api-2.17.1.jar;C:\Users\ufo\.m2\repository\org\slf4j\jul-to-slf4j\1.7.36\jul-to-slf4j-1.7.36.jar;C:\Users\ufo\.m2\repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-core\5.3.16\spring-core-5.3.16.jar;C:\Users\ufo\.m2\repository\org\springframework\spring-jcl\5.3.16\spring-jcl-5.3.16.jar;C:\Users\ufo\.m2\repository\org\yaml\snakeyaml\1.29\snakeyaml-1.29.jar;C:\Users\ufo\.m2\repository\com\alibaba\fastjson\1.2.48\fastjson-1.2.48.jar;C:\Users\ufo\.m2\repository\com\fasterxml\jackson\core\jackson-databind\2.12.4\jackson-databind-2.12.4.jar;C:\Users\ufo\.m2\repository\com\fasterxml\jackson\core\jackson-annotations\2.12.4\jackson-annotations-2.12.4.jar;C:\Users\ufo\.m2\repository\com\fasterxml\jackson\core\jackson-core\2.12.4\jackson-core-2.12.4.jar;C:\Users\ufo\.m2\repository\com\oracle\database\jdbc\ojdbc8\21.1.0.0\ojdbc8-21.1.0.0.jar;C:\Users\ufo\.m2\repository\com\oracle\database\nls\orai18n\21.1.0.0\orai18n-21.1.0.0.jar com.hy.lab.inject.BigtableInjecter
发现表emp_30已存在
表emp_30已删除
表emp_30已建成
第1  次提交
第2  次提交
第3  次提交
第4  次提交
第5  次提交
第6  次提交
第7  次提交
第8  次提交
第9  次提交
第10 次提交
第11 次提交
第12 次提交
第13 次提交
第14 次提交
第15 次提交
第16 次提交
第17 次提交
第18 次提交
第19 次提交
第20 次提交
第21 次提交
第22 次提交
第23 次提交
第24 次提交
第25 次提交
第26 次提交
第27 次提交
第28 次提交
第29 次提交
第30 次提交
第31 次提交
第32 次提交
第33 次提交
第34 次提交
第35 次提交
第36 次提交
第37 次提交
第38 次提交
第39 次提交
第40 次提交
第41 次提交
第42 次提交
第43 次提交
第44 次提交
第45 次提交
第46 次提交
第47 次提交
第48 次提交
第49 次提交
第50 次提交
第51 次提交
第52 次提交
第53 次提交
第54 次提交
第55 次提交
第56 次提交
第57 次提交
第58 次提交
第59 次提交
第60 次提交
第61 次提交
第62 次提交
第63 次提交
第64 次提交
第65 次提交
第66 次提交
第67 次提交
第68 次提交
第69 次提交
第70 次提交
第71 次提交
第72 次提交
第73 次提交
第74 次提交
第75 次提交
第76 次提交
第77 次提交
第78 次提交
第79 次提交
第80 次提交
第81 次提交
第82 次提交
第83 次提交
第84 次提交
第85 次提交
第86 次提交
第87 次提交
第88 次提交
第89 次提交
第90 次提交
第91 次提交
第92 次提交
第93 次提交
第94 次提交
第95 次提交
第96 次提交
第97 次提交
第98 次提交
第99 次提交
第100次提交
Time elapsed:38m51s375ms

Process finished with exit code 0

执行完后表中数据量：

SQL> select count(*) from emp_30;

  COUNT(*)
----------
  10000000

END