和MySql的对比下，两者有数量级的差距。
表ddl：
CREATE TABLE tb04("ID" ,) not null primary key,"NAME" NVARCHAR2() not null,"AGE" ,) not null ,"CREATEDTIME" ) not null)
代码如下：
package com.hy;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.List;/** * 数据库连接参数 * @author horn1 * */class DbParam{ final String Driver = "oracle.jdbc.driver.OracleDriver"; final String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; final String User = "ufo"; final String Pswd = "1234";}class TypeField{ String type; String field;}/** * 多表批量插入器 * @author horn1 * */public class MultiTbBatchInserter { private final int BatchSize=150;// 经有限测试，150已经趋近本机最值 // 有三个表需要插入,如果是多个表，扩充数组即可 // PK:主键 CH：文字 DT：Datetime，还可以根据需要扩充代号，在getInsertSql函数中则根据代号来设置值 private final String[][] tableArray= { {"tb04:10000000","PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, //{"tb02:1000000","PK:ID","CH:SN","CH:NAME","CH:AGE","DT:CREATEDTIME"}, //{"tb03:1000000","PK:ID","CH:SN","CH:NAME","CH:AGE","CH:Address","DT:CREATEDTIME"}, }; /** * 批量插值 */ public void batchInsert() { DbParam dbParam=new DbParam(); Connection conn = null; Statement stmt = null; try{ Class.forName(dbParam.Driver).newInstance(); conn = DriverManager.getConnection(dbParam.DbUrl, dbParam.User, dbParam.Pswd); stmt = conn.createStatement(); System.out.println("Begin to access "+dbParam.DbUrl+" as "+dbParam.User+"..."); for(String[] innerArr:tableArray) { String tableName=innerArr[0].split(":")[0]; int count=Integer.parseInt(innerArr[0].split(":")[1]); System.out.println("准备向表"+tableName+"插入"+count+"条记录."); // 插值前先清空 truncateTable(tableName,conn,stmt); // 真正插入数据 insertTestDataTo(tableName,count,innerArr,conn,stmt); } } 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 n * @return */ private static String getDatetimeBefore(int n) { try { Calendar now = Calendar.getInstance(); now.add(Calendar.SECOND,-n*10);//日期减去n*10秒 Date newDate=now.getTime(); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String retval = sdf.format(newDate); return retval; } catch(Exception ex) { ex.printStackTrace(); return null; } } /** * 清空一个表的数据，注意此功能有破坏性，不可恢复，注意备份好数据 * @param tableName * @param conn * @param stmt * @throws SQLException */ private void truncateTable(String tableName,Connection conn,Statement stmt) throws SQLException{ String sql="truncate table "+tableName; stmt.execute(sql); System.out.println("truncated table:"+tableName); } /** * 向一个表插入数据 * @param tableName * @param count * @param innerArr * @param conn * @param stmt * @throws SQLException */ private void insertTestDataTo(String tableName,int count,String[] innerArr,Connection conn,Statement stmt) throws SQLException{ // 得到字段名和字段类型 List<TypeField> typefields=new ArrayList<TypeField>(); for(int i=1;i<innerArr.length;i++) { String temp=innerArr[i]; String[] arrTmp=temp.split(":"); TypeField tf=new TypeField(); tf.type=arrTmp[0]; tf.field=arrTmp[1]; typefields.add(tf); } List<String> fields=new ArrayList<String>(); List<String> values=new ArrayList<String>(); int index=0; for(TypeField tf:typefields) { fields.add(tf.field); values.add("''{"+index+"}''"); index++; } 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; sb.append(getInsertSql(tableName,typefields,index)); } sb.append(" select * from dual"); String sql = sb.toString(); //System.out.println("sql="+sql); stmt.executeUpdate(sql); System.out.println("#"+i+" "+BatchSize+" records inserted to "+tableName); } } /** * 得到批量插入语句 * @param tableName * @param typefields * @param index * @return */ private String getInsertSql(String tableName,List<TypeField> typefields,int index) { String currTime=getDatetimeBefore(index); StringBuilder sb=new StringBuilder(); sb.append(" INTO "+tableName+"("); List<String> fields=new ArrayList<String>(); for(TypeField tf:typefields) { fields.add(tf.field); } sb.append(String.join(",",fields)); sb.append(") values("); List<String> values=new ArrayList<String>(); for(TypeField tf:typefields) { if(tf.type.equals("PK")) { values.add("'"+String.valueOf(index)+"'"); }else if(tf.type.equals("CH")) { values.add("'0'"); }else if(tf.type.equals("DT")) { values.add("to_date('"+currTime+"','yyyy-MM-dd HH24:mi:ss')"); } } sb.append(String.join(",",values)); sb.append(")"); String insertSql=sb.toString(); return insertSql; } /** * 将秒转化为日时分秒 * @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; } public static void main(String[] args) { MultiTbBatchInserter mi=new MultiTbBatchInserter(); long startTime = System.currentTimeMillis(); mi.batchInsert(); long endTime = System.currentTimeMillis(); System.out.println("Time elapsed:" + sec2DHMS((endTime - startTime)/1000) ); }}
输出：
#66658 150 records inserted to tb04#66659 150 records inserted to tb04#66660 150 records inserted to tb04#66661 150 records inserted to tb04#66662 150 records inserted to tb04#66663 150 records inserted to tb04#66664 150 records inserted to tb04#66665 150 records inserted to tb04Time elapsed:1h2m15s
数据库记录：
–END– 2019年11月9日19:19:33
