【Oracle/Java】给十六张表各插入十万条数据 单线程耗时半小时 多线程耗时一刻钟
测试机Oracle版本：
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
单线程插值程序：
package com.hy.insert.singlethread; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; 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; import org.apache.log4j.Logger; import com.hy.DBParam; import com.hy.TableHandler; /** * 此类用于向各表批量插入数据(单线程模式) * @author 逆火 * * 2019年11月16日 下午6:33:01 */ public class BatchInserter { private static Logger log = Logger.getLogger(TableHandler.class); private final int BatchSize=250;// 一次性插入记录数 private final int TotalInsert=100000;// 单表插入总记录数 // 要插入的表数组 private final String[][] tableArray= { {"TestTB01:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB02:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB03:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB04:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB05:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB06:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB07:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB08:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB09:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB10:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB11:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB12:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB13:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB14:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB15:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB16:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, }; /** * 批量插入 */ public void batchInsert() { 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+"..."); int index=1; for(String[] innerArr:tableArray) { long startTime = System.currentTimeMillis(); String tableName=innerArr[0].split(":")[0]; int count=Integer.parseInt(innerArr[0].split(":")[1]); truncateTable(tableName,conn,stmt); insertDataToTable(index,tableName,count,innerArr,conn,stmt); if(isAllInserted(count,tableName,stmt)) { long endTime = System.currentTimeMillis(); log.info("#"+index+" "+count+" records were inserted to table:'" + tableName + "' used " + sec2DHMS(startTime,endTime) ); index++; } } } catch (Exception e) { System.out.print(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { log.error("Can't close stmt/conn because of " + e.getMessage()); } } } /** * judge if all records are inserted * @param count * @param table * @param stmt * @return * @throws SQLException */ private boolean isAllInserted(int count,String table,Statement stmt) throws SQLException { String sql="SELECT COUNT (*) as cnt FROM "+table; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int cnt = rs.getInt("cnt"); return cnt==count; } return false; } /** * get datetime n seconds before * @param n * @param interval * @return */ private static String getDatetimeBefore(int n,int interval) { try { Calendar now = Calendar.getInstance(); now.add(Calendar.SECOND,-n*interval);//鏃ユ湡鍑忓幓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; } } /** * delete all data in a table quickly * @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); } /** * Insert date to a table * @param tbSN * @param tableName * @param count * @param innerArr * @param conn * @param stmt * @throws SQLException */ private void insertDataToTable(int tbSN,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++; } int interval=2*365*24*60*60/count;// 涓ゅ勾鐨勭鏁伴櫎浠ユ�讳釜鏁板嵆涓洪棿闅� 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,interval)); } sb.append(" select * from dual"); String sql = sb.toString(); //long startTime = System.currentTimeMillis(); stmt.executeUpdate(sql); //long endTime = System.currentTimeMillis(); //System.out.println("#"+tbSN+"-"+i+" "+BatchSize+" records inserted to '"+tableName+"' used " + sec2DHMS(startTime,endTime)); } } /** * get insert sql * @param tableName * @param typefields * @param index * @return */ private String getInsertSql(String tableName,List<TypeField> typefields,int index,int interval) { String currTime=getDatetimeBefore(index,interval); 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)+"'"); if(tableName.contains("DELIVERY_INFO_HISTORY")) { values.add("'0'"); }else { values.add("'"+String.valueOf(index)+"'"); } }else if(tf.type.equals("CH")) { values.add("'0'"); }else if(tf.type.equals("US")) { values.add("'heyang'"); }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; } /** * change seconds to DayHourMinuteSecond format * @param stratMs * @param endMs * @return */ private static String sec2DHMS(long stratMs,long endMs) { String retval = null; long secondCount=(endMs-stratMs)/1000; 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; } protected static final class TypeField{ String type; String field; } public static void main(String[] args) { BatchInserter mi=new BatchInserter(); long startTime = System.currentTimeMillis(); mi.batchInsert(); long endTime = System.currentTimeMillis(); System.out.println("Time elapsed:" + sec2DHMS(startTime,endTime) ); } }
输出：
Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... truncated table:TestTB01 2019-11-16 19:23:13,914 INFO[main]-#1 100000 records were inserted to table:'TestTB01' used 1m49s truncated table:TestTB02 2019-11-16 19:25:02,880 INFO[main]-#2 100000 records were inserted to table:'TestTB02' used 1m48s truncated table:TestTB03 2019-11-16 19:26:52,597 INFO[main]-#3 100000 records were inserted to table:'TestTB03' used 1m49s truncated table:TestTB04 2019-11-16 19:28:41,700 INFO[main]-#4 100000 records were inserted to table:'TestTB04' used 1m49s truncated table:TestTB05 2019-11-16 19:30:31,205 INFO[main]-#5 100000 records were inserted to table:'TestTB05' used 1m49s truncated table:TestTB06 2019-11-16 19:32:23,090 INFO[main]-#6 100000 records were inserted to table:'TestTB06' used 1m51s truncated table:TestTB07 2019-11-16 19:34:11,442 INFO[main]-#7 100000 records were inserted to table:'TestTB07' used 1m48s truncated table:TestTB08 2019-11-16 19:36:00,564 INFO[main]-#8 100000 records were inserted to table:'TestTB08' used 1m49s truncated table:TestTB09 2019-11-16 19:37:48,173 INFO[main]-#9 100000 records were inserted to table:'TestTB09' used 1m47s truncated table:TestTB10 2019-11-16 19:39:37,677 INFO[main]-#10 100000 records were inserted to table:'TestTB10' used 1m49s truncated table:TestTB11 2019-11-16 19:41:25,135 INFO[main]-#11 100000 records were inserted to table:'TestTB11' used 1m47s truncated table:TestTB12 2019-11-16 19:43:12,554 INFO[main]-#12 100000 records were inserted to table:'TestTB12' used 1m47s truncated table:TestTB13 2019-11-16 19:45:01,320 INFO[main]-#13 100000 records were inserted to table:'TestTB13' used 1m48s truncated table:TestTB14 2019-11-16 19:46:49,677 INFO[main]-#14 100000 records were inserted to table:'TestTB14' used 1m48s truncated table:TestTB15 2019-11-16 19:48:37,682 INFO[main]-#15 100000 records were inserted to table:'TestTB15' used 1m48s truncated table:TestTB16 2019-11-16 19:50:26,808 INFO[main]-#16 100000 records were inserted to table:'TestTB16' used 1m49s Time elapsed:29m2s
总共花了近半个小时。
多线程程序：
线程管理者类：
package com.hy.insert.multithread; import java.text.MessageFormat; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; public class InsertManager { private static Logger log = Logger.getLogger(InsertManager.class); private final int TotalInsert=100000;// 单表插入总记录数 private List<InsertJobInfo> jobInfos; private long startTime;// Start time // 要插入的表数组 private final String[][] tableArray= { {"TestTB01:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB02:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB03:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB04:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB05:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB06:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB07:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB08:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB09:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB10:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB11:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB12:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB13:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB14:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB15:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, {"TestTB16:"+TotalInsert,"PK:ID","CH:NAME","CH:AGE","DT:CREATEDTIME"}, }; public void batchInsert() { startTime=System.currentTimeMillis(); jobInfos=new ArrayList<InsertJobInfo>(); int index=1; for(String[] innerArr:tableArray) { String tableName=innerArr[0].split(":")[0]; int count=Integer.parseInt(innerArr[0].split(":")[1]); new InsertThread(index,tableName,count,innerArr,this).start(); index++; } } /** * Thread report manager "job done." * @param tbSN * @param tableName * @param timeElasped */ public void reportFinished(String tbSN,String tableName,String timeElasped) { jobInfos.add(new InsertJobInfo(tbSN,tableName,timeElasped)); if(jobInfos.size()==tableArray.length) { long endTime = System.currentTimeMillis(); log.info(">>> Insert jobs finished.( time elapsed： " + sec2DHMS(startTime,endTime)+") <<<"); log.info("------------ Details ------------"); for(InsertJobInfo jobInfo:jobInfos) { String raw="{0},{1},{2}"; Object[] arr={jobInfo.tbSn,jobInfo.tableName,jobInfo.timeElapsed}; String line=MessageFormat.format(raw, arr); log.info(line); } log.info("------------ Details ------------"); }else { log.info(jobInfos.size()+" inserters completed their jobs."); } } /** * change seconds to DayHourMinuteSecond format * @param stratMs * @param endMs * @return */ private static String sec2DHMS(long stratMs,long endMs) { String retval = null; long secondCount=(endMs-stratMs)/1000; 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; } protected static final class InsertJobInfo{ String tbSn; String tableName; String timeElapsed; public InsertJobInfo(String tbSn,String tableName,String timeElapsed) { this.tbSn=tbSn; this.tableName=tableName; this.timeElapsed=timeElapsed; } } public static void main(String[] args) { InsertManager im=new InsertManager(); im.batchInsert(); } }
线程类：
package com.hy.insert.multithread; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; 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; import org.apache.log4j.Logger; import com.hy.DBParam; public class InsertThread extends Thread{ private static Logger log = Logger.getLogger(InsertThread.class); private final int BatchSize=250;// 一次性插入记录数 private int tableIndex; private String tableName; private int count; private String[] innerArr; private InsertManager manager; public InsertThread(int tableIndex,String tableName,int count,String[] innerArr,InsertManager mng) { this.tableIndex=tableIndex; this.tableName=tableName; this.count=count; this.innerArr=innerArr; this.manager=mng; } public void run() { Connection conn = null; Statement stmt = null; try{ long startTime = System.currentTimeMillis(); Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); stmt = conn.createStatement(); log.info("Begin to access "+DBParam.DbUrl+" as "+DBParam.User+"..."); truncateTable(tableName,conn,stmt); insertDataToTable(tableIndex,tableName,count,innerArr,conn,stmt); if(isAllInserted(count,tableName,stmt)) { long endTime = System.currentTimeMillis(); String timeElasped=sec2DHMS(startTime,endTime); log.info("#"+tableIndex+" "+count+" records were inserted to table:'" + tableName + "' used " + timeElasped ); manager.reportFinished(String.valueOf(tableIndex), tableName, timeElasped); } } catch (Exception e) { System.out.print(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { log.error("Can't close stmt/conn because of " + e.getMessage()); } } } /** * judge if all records are inserted * @param count * @param table * @param stmt * @return * @throws SQLException */ private boolean isAllInserted(int count,String table,Statement stmt) throws SQLException { String sql="SELECT COUNT (*) as cnt FROM "+table; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int cnt = rs.getInt("cnt"); return cnt==count; } return false; } /** * get datetime n seconds before * @param n * @param interval * @return */ private static String getDatetimeBefore(int n,int interval) { try { Calendar now = Calendar.getInstance(); now.add(Calendar.SECOND,-n*interval);//鏃ユ湡鍑忓幓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; } } /** * delete all data in a table quickly * @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); log.info("truncated table:"+tableName); } /** * Insert date to a table * @param tbSN * @param tableName * @param count * @param innerArr * @param conn * @param stmt * @throws SQLException */ private void insertDataToTable(int tbSN,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++; } int interval=2*365*24*60*60/count;// 涓ゅ勾鐨勭鏁伴櫎浠ユ�讳釜鏁板嵆涓洪棿闅� 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,interval)); } sb.append(" select * from dual"); String sql = sb.toString(); // long startTime = System.currentTimeMillis(); stmt.executeUpdate(sql); //long endTime = System.currentTimeMillis(); //log.info("#"+tbSN+"-"+i+" "+BatchSize+" records inserted to '"+tableName+"' used " + sec2DHMS(startTime,endTime)); } } /** * get insert sql * @param tableName * @param typefields * @param index * @return */ private String getInsertSql(String tableName,List<TypeField> typefields,int index,int interval) { String currTime=getDatetimeBefore(index,interval); 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)+"'"); if(tableName.contains("DELIVERY_INFO_HISTORY")) { values.add("'0'"); }else { values.add("'"+String.valueOf(index)+"'"); } }else if(tf.type.equals("CH")) { values.add("'0'"); }else if(tf.type.equals("US")) { values.add("'heyang'"); }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; } /** * change seconds to DayHourMinuteSecond format * @param stratMs * @param endMs * @return */ private static String sec2DHMS(long stratMs,long endMs) { String retval = null; long secondCount=(endMs-stratMs)/1000; 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; } protected static final class TypeField{ String type; String field; } }
输出：
2019-11-16 21:27:27,782 INFO[Thread-4]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-2]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-0]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-13]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-12]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-6]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-3]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-1]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-10]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-11]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-8]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-14]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-5]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-7]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-15]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:27,782 INFO[Thread-9]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-16 21:27:28,258 INFO[Thread-14]-truncated table:TestTB15 2019-11-16 21:27:28,258 INFO[Thread-6]-truncated table:TestTB07 2019-11-16 21:27:28,258 INFO[Thread-4]-truncated table:TestTB05 2019-11-16 21:27:28,263 INFO[Thread-0]-truncated table:TestTB01 2019-11-16 21:27:28,263 INFO[Thread-1]-truncated table:TestTB02 2019-11-16 21:27:29,146 INFO[Thread-7]-truncated table:TestTB08 2019-11-16 21:27:29,166 INFO[Thread-3]-truncated table:TestTB04 2019-11-16 21:27:29,205 INFO[Thread-15]-truncated table:TestTB16 2019-11-16 21:27:29,253 INFO[Thread-11]-truncated table:TestTB12 2019-11-16 21:27:29,323 INFO[Thread-10]-truncated table:TestTB11 2019-11-16 21:27:29,385 INFO[Thread-2]-truncated table:TestTB03 2019-11-16 21:27:30,082 INFO[Thread-8]-truncated table:TestTB09 2019-11-16 21:27:30,338 INFO[Thread-12]-truncated table:TestTB13 2019-11-16 21:27:30,654 INFO[Thread-13]-truncated table:TestTB14 2019-11-16 21:27:30,654 INFO[Thread-9]-truncated table:TestTB10 2019-11-16 21:27:30,815 INFO[Thread-5]-truncated table:TestTB06 2019-11-16 21:42:56,391 INFO[Thread-8]-#9 100000 records were inserted to table:'TestTB09' used 15m29s 2019-11-16 21:42:56,392 INFO[Thread-8]-1 inserters completed their jobs. 2019-11-16 21:42:57,036 INFO[Thread-9]-#10 100000 records were inserted to table:'TestTB10' used 15m29s 2019-11-16 21:42:57,037 INFO[Thread-9]-2 inserters completed their jobs. 2019-11-16 21:43:02,055 INFO[Thread-7]-#8 100000 records were inserted to table:'TestTB08' used 15m34s 2019-11-16 21:43:02,055 INFO[Thread-7]-3 inserters completed their jobs. 2019-11-16 21:43:03,279 INFO[Thread-15]-#16 100000 records were inserted to table:'TestTB16' used 15m36s 2019-11-16 21:43:03,279 INFO[Thread-15]-4 inserters completed their jobs. 2019-11-16 21:43:09,027 INFO[Thread-10]-#11 100000 records were inserted to table:'TestTB11' used 15m41s 2019-11-16 21:43:09,028 INFO[Thread-10]-5 inserters completed their jobs. 2019-11-16 21:43:09,759 INFO[Thread-13]-#14 100000 records were inserted to table:'TestTB14' used 15m42s 2019-11-16 21:43:09,759 INFO[Thread-13]-6 inserters completed their jobs. 2019-11-16 21:43:09,835 INFO[Thread-4]-#5 100000 records were inserted to table:'TestTB05' used 15m42s 2019-11-16 21:43:09,835 INFO[Thread-4]-7 inserters completed their jobs. 2019-11-16 21:43:10,074 INFO[Thread-6]-#7 100000 records were inserted to table:'TestTB07' used 15m42s 2019-11-16 21:43:10,074 INFO[Thread-6]-8 inserters completed their jobs. 2019-11-16 21:43:11,530 INFO[Thread-14]-#15 100000 records were inserted to table:'TestTB15' used 15m44s 2019-11-16 21:43:11,530 INFO[Thread-14]-9 inserters completed their jobs. 2019-11-16 21:43:12,124 INFO[Thread-3]-#4 100000 records were inserted to table:'TestTB04' used 15m44s 2019-11-16 21:43:12,124 INFO[Thread-3]-10 inserters completed their jobs. 2019-11-16 21:43:12,187 INFO[Thread-5]-#6 100000 records were inserted to table:'TestTB06' used 15m45s 2019-11-16 21:43:12,188 INFO[Thread-5]-11 inserters completed their jobs. 2019-11-16 21:43:13,037 INFO[Thread-11]-#12 100000 records were inserted to table:'TestTB12' used 15m45s 2019-11-16 21:43:13,037 INFO[Thread-11]-12 inserters completed their jobs. 2019-11-16 21:43:13,671 INFO[Thread-0]-#1 100000 records were inserted to table:'TestTB01' used 15m46s 2019-11-16 21:43:13,671 INFO[Thread-0]-13 inserters completed their jobs. 2019-11-16 21:43:13,742 INFO[Thread-12]-#13 100000 records were inserted to table:'TestTB13' used 15m46s 2019-11-16 21:43:13,765 INFO[Thread-12]-14 inserters completed their jobs. 2019-11-16 21:43:14,588 INFO[Thread-2]-#3 100000 records were inserted to table:'TestTB03' used 15m47s 2019-11-16 21:43:14,588 INFO[Thread-2]-15 inserters completed their jobs. 2019-11-16 21:43:15,438 INFO[Thread-1]-#2 100000 records were inserted to table:'TestTB02' used 15m48s 2019-11-16 21:43:15,438 INFO[Thread-1]->>> Insert jobs finished.( time elapsed： 15m48s) <<< 2019-11-16 21:43:15,438 INFO[Thread-1]------------- Details ------------ 2019-11-16 21:43:15,439 INFO[Thread-1]-9,TestTB09,15m29s 2019-11-16 21:43:15,439 INFO[Thread-1]-10,TestTB10,15m29s 2019-11-16 21:43:15,439 INFO[Thread-1]-8,TestTB08,15m34s 2019-11-16 21:43:15,439 INFO[Thread-1]-16,TestTB16,15m36s 2019-11-16 21:43:15,439 INFO[Thread-1]-11,TestTB11,15m41s 2019-11-16 21:43:15,439 INFO[Thread-1]-14,TestTB14,15m42s 2019-11-16 21:43:15,439 INFO[Thread-1]-5,TestTB05,15m42s 2019-11-16 21:43:15,439 INFO[Thread-1]-7,TestTB07,15m42s 2019-11-16 21:43:15,439 INFO[Thread-1]-15,TestTB15,15m44s 2019-11-16 21:43:15,439 INFO[Thread-1]-4,TestTB04,15m44s 2019-11-16 21:43:15,439 INFO[Thread-1]-6,TestTB06,15m45s 2019-11-16 21:43:15,440 INFO[Thread-1]-12,TestTB12,15m45s 2019-11-16 21:43:15,440 INFO[Thread-1]-1,TestTB01,15m46s 2019-11-16 21:43:15,440 INFO[Thread-1]-13,TestTB13,15m46s 2019-11-16 21:43:15,440 INFO[Thread-1]-3,TestTB03,15m47s 2019-11-16 21:43:15,440 INFO[Thread-1]-2,TestTB02,15m48s 2019-11-16 21:43:15,440 INFO[Thread-1]------------- Details ------------
这回只花了一刻钟，是单线程版本的一半，而且整体时间等于诸线程最长时间，这是线程的优势。
线程里Connection和Statement必须自己创建，独享一份，这是需要注意的地方。
明天再比比看删除方案。
--END-- 2019年11月16日22:01:07
