【java/oralce/sql】往一张仅有id，名称，创建时间三个字段的表中插入百万数据需要多久？1分26秒

代码下载：https://files.cnblogs.com/files/xiandedanteng/fastfilltable20191222.rar

表testtb18的结构如下：

CREATE TABLE testtb18(    id NUMBER not null primary key,    name NVARCHAR2() not null,    createtime ) not null)

三个字段，正好是常用的number，nvarcha2，timestamp类型。

用java程序创建这表表的代码如下：

/** * 数据库连接参数 * @author 逆火 * * 2019年11月16日 上午8:09:24 */public final class DBParam {    public final static String Driver = "oracle.jdbc.driver.OracleDriver";    ::orcl";    public final static String User = "ufo";    ";}

package com.hy.fastfilltable;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.apache.log4j.Logger;// Used to create a table in oraclepublic class TableCreater {    private static Logger log = Logger.getLogger(TableCreater.class);    private final String table="testtb18";    public boolean createTable() {        Connection conn = null;        Statement stmt = null;        try{            Class.forName(DBParam.Driver).newInstance();            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);            stmt = conn.createStatement();            String createTableSql=getCreateTbSql(table);            stmt.execute(createTableSql);            if(isTableExist(table,stmt)==true) {                log.info("Table:'"+table+"' created.");                return true;            }        } catch (Exception e) {            System.out.print(e.getMessage());        } finally {            try {                stmt.close();                conn.close();            } catch (SQLException e) {                System.out.print("Can't close stmt/conn because of " + e.getMessage());            }        }        return false;    }    /**     * Get a table's ddl     * @param table     * @return     */    private String getCreateTbSql(String table) {        StringBuilder sb=new StringBuilder();        sb.append("CREATE TABLE "+table);        sb.append("(");        sb.append("id NUMBER not null primary key,");        sb.append("name NVARCHAR2(60) not null,");        sb.append("createtime TIMESTAMP (6) not null");        sb.append(")");        return sb.toString();    }    // Execute a sql    //private int executeSql(String sql,Statement stmt) throws SQLException {    //    return stmt.executeUpdate(sql);    //}    // If a table exists    private boolean isTableExist(String table,Statement stmt) throws SQLException {        String sql="SELECT COUNT (*) as cnt FROM ALL_TABLES WHERE table_name = UPPER('"+table+"')";        ResultSet rs = stmt.executeQuery(sql);        while (rs.next()) {            int count = rs.getInt("cnt");            return count==1;        }        return false;    }    // Entry point    public static void main(String[] args) {        TableCreater tc=new TableCreater();        tc.createTable();    }}

现在我想就往这张表里添值，到一百万条记录，可以这么做：

package com.hy.fastfilltable;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.text.DecimalFormat;import org.apache.log4j.Logger;import com.hy.DBParam;public class FastTableFiller {    private static Logger log = Logger.getLogger(FastTableFiller.class);    private final String Table="testtb18";    private final int Total=1000000;    public boolean fillTable() {        Connection conn = null;        Statement stmt = null;        try{            Class.forName(DBParam.Driver).newInstance();            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);            conn.setAutoCommit(false);            stmt = conn.createStatement();            long startMs = System.currentTimeMillis();            clearTable(stmt,conn);            fillDataInTable(stmt,conn);            long endMs = System.currentTimeMillis();            log.info("It takes "+ms2DHMS(startMs,endMs)+" to fill "+toEastNumFormat(Total)+" records to table:'"+Table+"'.");        } catch (Exception e) {            e.printStackTrace();        } finally {            try {                stmt.close();                conn.close();            } catch (SQLException e) {                System.out.print("Can't close stmt/conn because of " + e.getMessage());            }        }        return false;    }    private void clearTable(Statement stmt,Connection conn) throws SQLException {        stmt.executeUpdate("truncate table "+Table);        conn.commit();        log.info("Cleared table:'"+Table+"'.");    }    private void fillDataInTable(Statement stmt,Connection conn) throws SQLException {        StringBuilder sb=new StringBuilder();        sb.append(" Insert into "+Table);        sb.append(" select rownum,dbms_random.string('*',50),sysdate from dual ");        sb.append(" connect by level<="+Total);        sb.append(" order by dbms_random.random");        String sql=sb.toString();        stmt.executeUpdate(sql);        conn.commit();    }    // 将整数在万分位以逗号分隔表示    public static String toEastNumFormat(long number) {        DecimalFormat df = new DecimalFormat("#,####");        return df.format(number);    }    // change seconds to DayHourMinuteSecond format    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 {            retval = seconds + "s";        }        return retval + ms;    }    // Entry point    public static void main(String[] args) {        FastTableFiller f=new FastTableFiller();        f.fillTable();    }}

执行效果还不错：

2019-12-22 15:21:02,412 INFO[main]-Cleared table:'testtb18'.2019-12-22 15:22:28,669 INFO[main]-It takes 1m26s268ms to fill 100,0000 records to table:'testtb18'.

注意：插一千万数据就会报oom异常，怎么解决请大家自行考虑，我暂时没这样的需求。

再看看表中情况;