【Oracle/Java】以Insert ALL方式向表中插入百万条记录，耗时9分17秒

由于按一千条一插程序长期无反应，之后改为百条一插方式，运行完发现插入百万记录需要9m17s，虽然比MySQL效率差，但比单条插入已经好不少了。

对Oracle的批量插入语法不明的请参考：https://www.cnblogs.com/xiandedanteng/p/11806720.html

代码如下：

package com.hy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;

/**
 * 百万数据插入Oracle表中
 * @author horn1
 *
 */
public class MillionInserter {
    
    // 连接到数据库的四大属性
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DBURL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "system";
    private static final String PSWD = "XXXX";
    
    public void batchInsert(int count) {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            long startTime = System.currentTimeMillis();
            
            Class.forName(DRIVER).newInstance();
            conn = DriverManager.getConnection(DBURL, USER, PSWD);
            stmt = conn.createStatement();
            
            int BatchSize=100;
            int index=0;
            int times=count/BatchSize;
            for(int i=0;i<times;i++) {
                StringBuilder sb=new StringBuilder();
                sb.append("INSERT ALL ");
                
                for(int j=0;j<BatchSize;j++) {
                    index=i*BatchSize+j;
                    String name="M"+index;
                    int age=j % 100;
                    
                    String raw=" INTO firsttb(NAME, age,createdtime) values(''{0}'',''{1}'',sysdate) ";
                    Object[] arr={name,age};
                    String particialSql=MessageFormat.format(raw, arr);
                    sb.append(particialSql);
                }
                
                sb.append("select * from dual");
                String sql = sb.toString();
                stmt.executeUpdate(sql);
                System.out.println("#"+i+" "+BatchSize+" records inserted");
            }
            
            long endTime = System.currentTimeMillis();
            System.out.println("Time elapsed:" + sec2DHMS((endTime - startTime)/1000) );
            
            String sql = "select count(*) as cnt from firsttb";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                String cnt = rs.getString("cnt");
                System.out.println("当前记录数："+cnt);
            }            
            
        } 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());
            }
        }
    }
    
    /**
         * 将秒转化为日时分秒
     * @param secondCount
     * @return
     */
    private static String sec2DHMS(long secondCount) {
        String retval = null;

        long days = secondCount / (60 * 60 * 24);
        long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);
        long minutes = (secondCount % (60 * 60)) / 60;
        long seconds = secondCount % 60;
        
        String strSeconds="";
        if(seconds!=0) {
            strSeconds=seconds + "s";
        }

        if (days > 0) {
            retval = days + "d" + hours + "h" + minutes + "m" + strSeconds;
        } else if (hours > 0) {
            retval = hours + "h" + minutes + "m" + strSeconds;
        } else if (minutes > 0) {
            retval = minutes + "m" + strSeconds;
        } else {
            retval = strSeconds;
        }

        return retval;
    }
    
    /**
     * 执行点
     * @param args
     */
    public static void main(String[] args) {
        MillionInserter mi=new MillionInserter();
        mi.batchInsert(1000000);
    }
}

输出：

#9987 100 records inserted
#9988 100 records inserted
#9989 100 records inserted
#9990 100 records inserted
#9991 100 records inserted
#9992 100 records inserted
#9993 100 records inserted
#9994 100 records inserted
#9995 100 records inserted
#9996 100 records inserted
#9997 100 records inserted
#9998 100 records inserted
#9999 100 records inserted
Time elapsed:9m17s
当前记录数：1000000

到此真正掌握了Oracle的Insert ALL语法，并确认了其真实效率，今天又取得了一个小小的突破，重走长征路又迈出了小小却坚实的一步。

表的表结构请见：https://www.cnblogs.com/xiandedanteng/p/11691113.html

--END-- 2019年11月9日11:19:01