[Java]在JAVA中使用Oracle的INSERT ALL语法进行批量插入

Oracle也提供了类似MySQL的批量插入语法，只是稍微别扭些，具体代码如下：

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;

public class BatchInserter {
    
    // 连接到数据库的四大属性
    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 = "XXXXX";
    
    public static void batchInsert() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DRIVER).newInstance();
            conn = DriverManager.getConnection(DBURL, USER, PSWD);
            stmt = conn.createStatement();
            
            StringBuilder sb=new StringBuilder();
            sb.append("INSERT ALL ");
            sb.append("   INTO firsttb(NAME, age,createdtime) values('E1','22',sysdate)");
            sb.append("   INTO firsttb(NAME, age,createdtime) values('E2','32',sysdate)");
            sb.append("   INTO firsttb(NAME, age,createdtime) values('E3','42',sysdate)");
            sb.append("select * from dual");
            String sql = sb.toString();
            stmt.executeUpdate(sql);
            
            sql = "select id,name,age,createdtime from firsttb";
            ResultSet rs = stmt.executeQuery(sql);

            int index = 0;
            while (rs.next()) {
                index++;

                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String cdate = rs.getString("createdtime");

                String raw = "#{0},{1},{2},{3},{4}";
                Object[] arr = { index, id, name, age, cdate };
                String outStr = MessageFormat.format(raw, arr);
                System.out.println(outStr);
            }
        } 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 args
     */
    public static void main(String[] args) {
        batchInsert();
    }
}

输出：

#1,1,ANDY,20,2019-11-09 09:19:10
#2,2,Bill,30,2019-11-09 09:19:39
#3,3,Cindy,40,2019-11-09 09:19:45
#4,4,E1,22,2019-11-09 10:25:54
#5,5,E2,32,2019-11-09 10:25:54
#6,6,E3,42,2019-11-09 10:25:54

前三条是原有的，4,5,6三条是刚才插入的，一会再试试百万条到底需要多长时间。

 

参考资料：

https://www.oschina.net/question/234345_51170

续篇：https://www.cnblogs.com/heyang78/p/15230310.html

END