【Oracle/Java】多表插删数据单多线程比较

源码下载：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