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三条是刚才插入的,一会再试试百万条到底需要多长时间。
参考资料: