源码下载：https://files.cnblogs.com/files/xiandedanteng/OracleAccessComparison20191117.rar
做这个比较工程初衷是：我在单位试验一个单线程删21张表和多线程删21张表比较方案，发现单线程从八百五十万数据需要5分钟上下，多线程（一张表一个线程，为此还把MaxActive调大了）却需要9分钟左右，尤其是删其中两张两百万级别的大表的两个线程总不结束。这个结果是和我以往的多线程比单线程快的观念是违背的，我想了想，这个有违常识的结果是因为单位的Oracle是建立在虚拟机上的有关，在家里的实际环境测试结果该符合常识。于是就建立了这个工程，从而证实常识还是对的。一般情况确实是多线程比单线程快，特定环境的另当别论。
下面把代码贴出来以供日后参考，如果它对你也有帮助那就再好不过了。
数据库连接参数类：
package com.hy; /** * 数据库连接参数 * @author 逆火 * * 2019年11月16日 上午8:09:24 */ public final class DBParam { public final static String Driver = "oracle.jdbc.driver.OracleDriver"; public final static String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; public final static String User = "ufo"; public final static String Pswd = "1234"; }
用于建立十六章表的类：
package com.hy; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; /** * 此类用于管理表的创建和销毁 * @author 逆火 * * 2019年11月16日 下午5:40:22 */ public class TableHandler { private static Logger log = Logger.getLogger(TableHandler.class); private String[] tablenames= { "TestTB01", "TestTB02", "TestTB03", "TestTB04", "TestTB05", "TestTB06", "TestTB07", "TestTB08", "TestTB09", "TestTB10", "TestTB11", "TestTB12", "TestTB13", "TestTB14", "TestTB15", "TestTB16", }; /** * Get the create table ddl of a table * @param table * @return */ private String getCreateTbSql(String table) { StringBuilder sb=new StringBuilder(); sb.append("CREATE TABLE "+table); sb.append("("); sb.append(""ID" NUMBER(8,0) not null primary key,"); sb.append(""NAME" NVARCHAR2(60) not null,"); sb.append(""AGE" NUMBER(3,0) DEFAULT 0 not null ,"); sb.append(""CREATEDTIME" TIMESTAMP (6) not null"); sb.append(")"); return sb.toString(); } /** * Judge if a table is exist * @param table * @param stmt * @return * @throws SQLException */ private boolean isTableExist(String table,Statement stmt) throws SQLException { String sql="SELECT COUNT (*) as cnt FROM ALL_TABLES WHERE table_name = UPPER('"+table+"')"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int count = rs.getInt("cnt"); return count==1; } return false; } /** * Crate tables */ public void createTables() { Connection conn = null; Statement stmt = null; try{ Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); stmt = conn.createStatement(); int i=0; for(String table:tablenames) { i++; String sql=getCreateTbSql(table); stmt.executeUpdate(sql); if(isTableExist(table,stmt)) { log.info("#"+i+" "+table+" created."); } } } 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()); } } } /** * Remove all the tables */ public void dropTables() { Connection conn = null; Statement stmt = null; try{ Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); stmt = conn.createStatement(); int i=0; for(String table:tablenames) { i++; String sql="drop table "+table; stmt.executeUpdate(sql); if(isTableExist(table,stmt)==false) { log.info("#"+i+" "+table+" dropped."); } } } 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()); } } } /** * Kick start * @param args */ public static void main(String[] args) { TableHandler th=new TableHandler(); th.createTables(); } }
单线程插表类：
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) ); } }
多线程插表管理器类：
package com.hy.insert.multithread; import java.text.MessageFormat; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; /** * 多线程批量插入管理者 * @author 逆火 * * 2019年11月17日 上午9:19:09 */ 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; } /** * 成员内部类，用来做数据结构 * @author 逆火 * * 2019年11月17日 上午9:22:04 */ 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; } } /** * start point * @param args */ 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; /** * 删单表线程 * @author 逆火 * * 2019年11月17日 上午9:24:39 */ public class InsertThread extends Thread{ private static Logger log = Logger.getLogger(InsertThread.class); private final int BatchSize=250;// 一次性插入记录数 private int tableIndex;// 表序号 private String tableName;// tablename private int count;// record count will be inserted private String[] innerArr;// array contains field type and names private InsertManager manager;// reference to InsertManager /** * Constructor * @param tableIndex * @param tableName * @param count * @param innerArr * @param mng */ 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; } /** * Run body here */ 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; } /** * Inner class,used for inner data structure * @author 逆火 * * 2019年11月17日 上午9:27:47 */ protected static final class TypeField{ String type; String field; } }
多线程插表输出：
2019-11-17 09:19:26,915 INFO[Thread-3]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,915 INFO[Thread-10]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-13]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-9]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-15]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-5]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-12]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-7]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-11]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,916 INFO[Thread-8]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,915 INFO[Thread-4]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,915 INFO[Thread-1]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,915 INFO[Thread-0]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,915 INFO[Thread-14]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,915 INFO[Thread-6]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:26,915 INFO[Thread-2]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 09:19:27,373 INFO[Thread-2]-truncated table:TestTB03 2019-11-17 09:19:27,377 INFO[Thread-11]-truncated table:TestTB12 2019-11-17 09:19:27,380 INFO[Thread-10]-truncated table:TestTB11 2019-11-17 09:19:27,380 INFO[Thread-14]-truncated table:TestTB15 2019-11-17 09:19:27,439 INFO[Thread-6]-truncated table:TestTB07 2019-11-17 09:19:27,439 INFO[Thread-15]-truncated table:TestTB16 2019-11-17 09:19:27,439 INFO[Thread-3]-truncated table:TestTB04 2019-11-17 09:19:27,440 INFO[Thread-12]-truncated table:TestTB13 2019-11-17 09:19:27,439 INFO[Thread-9]-truncated table:TestTB10 2019-11-17 09:19:27,440 INFO[Thread-1]-truncated table:TestTB02 2019-11-17 09:19:27,440 INFO[Thread-8]-truncated table:TestTB09 2019-11-17 09:19:27,439 INFO[Thread-4]-truncated table:TestTB05 2019-11-17 09:19:27,441 INFO[Thread-13]-truncated table:TestTB14 2019-11-17 09:19:27,671 INFO[Thread-7]-truncated table:TestTB08 2019-11-17 09:19:27,677 INFO[Thread-5]-truncated table:TestTB06 2019-11-17 09:19:30,180 INFO[Thread-0]-truncated table:TestTB01 2019-11-17 09:35:42,104 INFO[Thread-14]-#15 100000 records were inserted to table:'TestTB15' used 16m15s 2019-11-17 09:35:42,105 INFO[Thread-14]-1 inserters completed their jobs. 2019-11-17 09:35:50,789 INFO[Thread-9]-#10 100000 records were inserted to table:'TestTB10' used 16m24s 2019-11-17 09:35:50,789 INFO[Thread-9]-2 inserters completed their jobs. 2019-11-17 09:35:51,143 INFO[Thread-2]-#3 100000 records were inserted to table:'TestTB03' used 16m25s 2019-11-17 09:35:51,144 INFO[Thread-2]-3 inserters completed their jobs. 2019-11-17 09:35:54,732 INFO[Thread-6]-#7 100000 records were inserted to table:'TestTB07' used 16m28s 2019-11-17 09:35:54,733 INFO[Thread-6]-4 inserters completed their jobs. 2019-11-17 09:36:01,647 INFO[Thread-1]-#2 100000 records were inserted to table:'TestTB02' used 16m35s 2019-11-17 09:36:01,647 INFO[Thread-1]-5 inserters completed their jobs. 2019-11-17 09:36:01,905 INFO[Thread-11]-#12 100000 records were inserted to table:'TestTB12' used 16m35s 2019-11-17 09:36:01,905 INFO[Thread-11]-6 inserters completed their jobs. 2019-11-17 09:36:02,715 INFO[Thread-0]-#1 100000 records were inserted to table:'TestTB01' used 16m36s 2019-11-17 09:36:02,715 INFO[Thread-0]-7 inserters completed their jobs. 2019-11-17 09:36:04,088 INFO[Thread-10]-#11 100000 records were inserted to table:'TestTB11' used 16m37s 2019-11-17 09:36:04,088 INFO[Thread-10]-8 inserters completed their jobs. 2019-11-17 09:36:04,246 INFO[Thread-15]-#16 100000 records were inserted to table:'TestTB16' used 16m38s 2019-11-17 09:36:04,246 INFO[Thread-15]-9 inserters completed their jobs. 2019-11-17 09:36:06,874 INFO[Thread-7]-#8 100000 records were inserted to table:'TestTB08' used 16m40s 2019-11-17 09:36:06,874 INFO[Thread-7]-10 inserters completed their jobs. 2019-11-17 09:36:08,301 INFO[Thread-13]-#14 100000 records were inserted to table:'TestTB14' used 16m42s 2019-11-17 09:36:08,301 INFO[Thread-13]-11 inserters completed their jobs. 2019-11-17 09:36:08,474 INFO[Thread-3]-#4 100000 records were inserted to table:'TestTB04' used 16m42s 2019-11-17 09:36:08,474 INFO[Thread-3]-12 inserters completed their jobs. 2019-11-17 09:36:08,546 INFO[Thread-4]-#5 100000 records were inserted to table:'TestTB05' used 16m42s 2019-11-17 09:36:08,546 INFO[Thread-4]-13 inserters completed their jobs. 2019-11-17 09:36:09,036 INFO[Thread-8]-#9 100000 records were inserted to table:'TestTB09' used 16m42s 2019-11-17 09:36:09,036 INFO[Thread-8]-14 inserters completed their jobs. 2019-11-17 09:36:09,283 INFO[Thread-5]-#6 100000 records were inserted to table:'TestTB06' used 16m43s 2019-11-17 09:36:09,284 INFO[Thread-5]-15 inserters completed their jobs. 2019-11-17 09:36:11,038 INFO[Thread-12]-#13 100000 records were inserted to table:'TestTB13' used 16m44s 2019-11-17 09:36:11,039 INFO[Thread-12]->>> Insert jobs finished.( time elapsed： 16m44s) <<< 2019-11-17 09:36:11,039 INFO[Thread-12]------------- Details ------------ 2019-11-17 09:36:11,040 INFO[Thread-12]-15,TestTB15,16m15s 2019-11-17 09:36:11,040 INFO[Thread-12]-10,TestTB10,16m24s 2019-11-17 09:36:11,040 INFO[Thread-12]-3,TestTB03,16m25s 2019-11-17 09:36:11,040 INFO[Thread-12]-7,TestTB07,16m28s 2019-11-17 09:36:11,040 INFO[Thread-12]-2,TestTB02,16m35s 2019-11-17 09:36:11,040 INFO[Thread-12]-12,TestTB12,16m35s 2019-11-17 09:36:11,040 INFO[Thread-12]-1,TestTB01,16m36s 2019-11-17 09:36:11,040 INFO[Thread-12]-11,TestTB11,16m37s 2019-11-17 09:36:11,040 INFO[Thread-12]-16,TestTB16,16m38s 2019-11-17 09:36:11,040 INFO[Thread-12]-8,TestTB08,16m40s 2019-11-17 09:36:11,041 INFO[Thread-12]-14,TestTB14,16m42s 2019-11-17 09:36:11,041 INFO[Thread-12]-4,TestTB04,16m42s 2019-11-17 09:36:11,041 INFO[Thread-12]-5,TestTB05,16m42s 2019-11-17 09:36:11,041 INFO[Thread-12]-9,TestTB09,16m42s 2019-11-17 09:36:11,041 INFO[Thread-12]-6,TestTB06,16m43s 2019-11-17 09:36:11,041 INFO[Thread-12]-13,TestTB13,16m44s 2019-11-17 09:36:11,041 INFO[Thread-12]------------- Details ------------
单线程删表类：
package com.hy.delete.singlethread; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; import com.hy.DBParam; /** * 多表单线程删除 * Single thread table deleter * @author 逆火 * * 2019年11月17日 上午8:42:41 */ public class BatchDeleter { private static Logger log = Logger.getLogger(BatchDeleter.class); // Commit size private static final int commitSize=10000; private String[] tablenames= { "TestTB01", "TestTB02", "TestTB03", "TestTB04", "TestTB05", "TestTB06", "TestTB07", "TestTB08", "TestTB09", "TestTB10", "TestTB11", "TestTB12", "TestTB13", "TestTB14", "TestTB15", "TestTB16", }; /** * 批量插入 */ public void batchDelete(String expiredDate) { 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 table:tablenames) { int total=delete(index,table,expiredDate,conn,stmt); log.info("#"+index+" "+total+" records deleted from table:'"+table+"'."); index++; } } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { log.error("Can't close stmt/conn because of " + e.getMessage()); } } } /** * 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; } /** * 按日期删一张表的记录 * @param tableIndex * @param table * @param expiredDate * @param conn * @param stmt * @throws SQLException */ private int delete(int tableIndex,String table,String expiredDate,Connection conn,Statement stmt) throws SQLException { int totalDeleted=0; int expiredCount=0; do { String sql="delete from "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum<'"+commitSize+"' "; int deleted=stmt.executeUpdate(sql); //log.info("#"+tableIndex+" "+deleted+" records deleted from table:'"+table+"'."); totalDeleted+=deleted; expiredCount=queryExpiredCount(table,expiredDate,stmt); }while(expiredCount>0); return totalDeleted; } /** * 查询过期记录数量 * @param table * @param expiredDate * @param conn * @param stmt * @return * @throws SQLException */ private int queryExpiredCount(String table,String expiredDate,Statement stmt) throws SQLException { String sql="SELECT COUNT (*) as cnt FROM "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum<10 "; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int count = rs.getInt("cnt"); return count; } return 0; } public static void main(String[] args) { long startTime = System.currentTimeMillis(); BatchDeleter bd=new BatchDeleter(); bd.batchDelete("2019-07-17"); long endTime = System.currentTimeMillis(); log.info("Time elapsed:" + sec2DHMS(startTime,endTime) ); } }
多线程删表管理器类：
package com.hy.insert.multithread; import java.text.MessageFormat; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; /** * 多线程批量插入管理者 * @author 逆火 * * 2019年11月17日 上午9:19:09 */ 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; } /** * 成员内部类，用来做数据结构 * @author 逆火 * * 2019年11月17日 上午9:22:04 */ 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; } } /** * start point * @param args */ 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; /** * 删单表线程 * @author 逆火 * * 2019年11月17日 上午9:24:39 */ public class InsertThread extends Thread{ private static Logger log = Logger.getLogger(InsertThread.class); private final int BatchSize=250;// 一次性插入记录数 private int tableIndex;// 表序号 private String tableName;// tablename private int count;// record count will be inserted private String[] innerArr;// array contains field type and names private InsertManager manager;// reference to InsertManager /** * Constructor * @param tableIndex * @param tableName * @param count * @param innerArr * @param mng */ 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; } /** * Run body here */ 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; } /** * Inner class,used for inner data structure * @author 逆火 * * 2019年11月17日 上午9:27:47 */ protected static final class TypeField{ String type; String field; } }
多线程删表结果：
2019-11-17 11:10:04,728 INFO[Thread-2]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,728 INFO[Thread-4]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-11]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-10]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-14]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-9]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-5]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-7]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-13]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-12]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-15]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-8]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-3]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,728 INFO[Thread-1]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,729 INFO[Thread-6]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:04,728 INFO[Thread-0]-Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo... 2019-11-17 11:10:30,721 INFO[Thread-14]-#15 83075 records deleted from table:'TestTB15'. 2019-11-17 11:10:30,725 INFO[Thread-14]-1 deleters completed their jobs. 2019-11-17 11:10:31,798 INFO[Thread-12]-#13 83075 records deleted from table:'TestTB13'. 2019-11-17 11:10:31,799 INFO[Thread-12]-2 deleters completed their jobs. 2019-11-17 11:10:33,649 INFO[Thread-9]-#10 83075 records deleted from table:'TestTB10'. 2019-11-17 11:10:33,649 INFO[Thread-9]-3 deleters completed their jobs. 2019-11-17 11:10:33,684 INFO[Thread-7]-#8 83075 records deleted from table:'TestTB08'. 2019-11-17 11:10:33,684 INFO[Thread-7]-4 deleters completed their jobs. 2019-11-17 11:10:35,740 INFO[Thread-0]-#1 83075 records deleted from table:'TestTB01'. 2019-11-17 11:10:35,740 INFO[Thread-0]-5 deleters completed their jobs. 2019-11-17 11:10:36,836 INFO[Thread-13]-#14 83075 records deleted from table:'TestTB14'. 2019-11-17 11:10:36,836 INFO[Thread-13]-6 deleters completed their jobs. 2019-11-17 11:10:36,932 INFO[Thread-8]-#9 83075 records deleted from table:'TestTB09'. 2019-11-17 11:10:36,932 INFO[Thread-8]-7 deleters completed their jobs. 2019-11-17 11:10:37,011 INFO[Thread-2]-#3 83075 records deleted from table:'TestTB03'. 2019-11-17 11:10:37,011 INFO[Thread-2]-8 deleters completed their jobs. 2019-11-17 11:10:38,479 INFO[Thread-5]-#6 83075 records deleted from table:'TestTB06'. 2019-11-17 11:10:38,479 INFO[Thread-5]-9 deleters completed their jobs. 2019-11-17 11:10:40,565 INFO[Thread-11]-#12 83075 records deleted from table:'TestTB12'. 2019-11-17 11:10:40,565 INFO[Thread-10]-#11 83075 records deleted from table:'TestTB11'. 2019-11-17 11:10:40,565 INFO[Thread-4]-#5 83075 records deleted from table:'TestTB05'. 2019-11-17 11:10:40,566 INFO[Thread-10]-11 deleters completed their jobs. 2019-11-17 11:10:40,566 INFO[Thread-11]-10 deleters completed their jobs. 2019-11-17 11:10:40,567 INFO[Thread-4]-12 deleters completed their jobs. 2019-11-17 11:10:40,633 INFO[Thread-15]-#16 83075 records deleted from table:'TestTB16'. 2019-11-17 11:10:40,633 INFO[Thread-15]-13 deleters completed their jobs. 2019-11-17 11:10:41,558 INFO[Thread-3]-#4 83075 records deleted from table:'TestTB04'. 2019-11-17 11:10:41,558 INFO[Thread-3]-14 deleters completed their jobs. 2019-11-17 11:10:42,127 INFO[Thread-6]-#7 83075 records deleted from table:'TestTB07'. 2019-11-17 11:10:42,127 INFO[Thread-6]-15 deleters completed their jobs. 2019-11-17 11:10:42,229 INFO[Thread-1]-#2 83075 records deleted from table:'TestTB02'. 2019-11-17 11:10:42,230 INFO[Thread-1]->>> Delete jobs finished.( time elapsed： 38s) <<< 2019-11-17 11:10:42,230 INFO[Thread-1]------------- Details ------------ 2019-11-17 11:10:42,231 INFO[Thread-1]-#,table,deleted,time elapsed 2019-11-17 11:10:42,234 INFO[Thread-1]-15,TestTB15,83075 2019-11-17 11:10:42,235 INFO[Thread-1]-13,TestTB13,83075 2019-11-17 11:10:42,235 INFO[Thread-1]-10,TestTB10,83075 2019-11-17 11:10:42,235 INFO[Thread-1]-8,TestTB08,83075 2019-11-17 11:10:42,236 INFO[Thread-1]-1,TestTB01,83075 2019-11-17 11:10:42,236 INFO[Thread-1]-14,TestTB14,83075 2019-11-17 11:10:42,237 INFO[Thread-1]-9,TestTB09,83075 2019-11-17 11:10:42,237 INFO[Thread-1]-3,TestTB03,83075 2019-11-17 11:10:42,238 INFO[Thread-1]-6,TestTB06,83075 2019-11-17 11:10:42,238 INFO[Thread-1]-12,TestTB12,83075 2019-11-17 11:10:42,239 INFO[Thread-1]-11,TestTB11,83075 2019-11-17 11:10:42,239 INFO[Thread-1]-5,TestTB05,83075 2019-11-17 11:10:42,241 INFO[Thread-1]-16,TestTB16,83075 2019-11-17 11:10:42,260 INFO[Thread-1]-4,TestTB04,83075 2019-11-17 11:10:42,262 INFO[Thread-1]-7,TestTB07,83075 2019-11-17 11:10:42,262 INFO[Thread-1]-2,TestTB02,83075 2019-11-17 11:10:42,262 INFO[Thread-1]------------- Details ------------
--END-- 2019年11月17日11:51:07