[oracle/java/sql]用于上十万批量数据插入Oracle表的Java程序

程序下载：https://files.cnblogs.com/files/xiandedanteng/LeftInnerNotExist20191222.rar

原理：Oracle的Insert all语法，说明文献：

代码：

1.DBParam

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";
}

2.TableRecordInserter

package com.hy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import org.apache.log4j.Logger;

// Used to insert ten thousands of records to table 
public class TableRecordInserter {
    private static Logger log = Logger.getLogger(TableCreater.class);
    
    private final String Table="delivery_history07";// 表名在这里改
    private final int Total=500000;// 插入数据总量在这里改
    
    public boolean fillTable() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            
            long startMs = System.currentTimeMillis();
            clearTable(stmt,conn);
            List<String> insertSqls=generateInsertSqlList();
            betachInsert(insertSqls,stmt,conn);
            long endMs = System.currentTimeMillis();
            log.info("It takes "+ms2DHMS(startMs,endMs)+" to fill "+Total+" records.");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
        
        return false;
    }
    
    private void clearTable(Statement stmt,Connection conn) throws SQLException {
        stmt.executeUpdate("truncate table "+Table);
        conn.commit();
        log.info("Cleared table:'"+Table+"'.");
    }
    
    private int betachInsert(List<String> insertSqls,Statement stmt,Connection conn) throws SQLException {
        int inserted=0;
        final int BatchSize=250;
        int count=insertSqls.size();
        int 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(insertSqls.get(index));
            }
            
            sb.append(" select * from dual");
            String sql = sb.toString();
            
            int n=stmt.executeUpdate(sql);
            inserted+=n;
            conn.commit();
            
            log.info("#"+i+" inserted " +n+" records.");
        }
        
        return inserted;
    }

    // 具体如何插值需要根据你的业务修改，产生本地化SQL
    private List<String> generateInsertSqlList() {
        List<String> sqlList=new ArrayList<String>();
        int index=0;
        do {
            int orderNoRange=getRandom(1,100);// 调整order_no，L89
            int orderNo=index*1000+orderNoRange;
            for(int i=0;i<orderNoRange;i++) {
                int shipper_code=getShipperCode();
                
                String insertSql=getInsertSql(index,orderNo,shipper_code);
                sqlList.add(insertSql);
                
                index++;
            }
        }while(index<Total);
        
        log.info("generated "+sqlList.size()+" insert sqls.");
        
        return sqlList;
    }
    
    // get partial insert sql，这里字段名需要修改
    private String getInsertSql(int id,int orderNo,int shipperCode) {
        String raw=" INTO {0}(id,name, order_no,shipper_code,createtime) values(''{1}'',''{2}'',''{3}'',''{4}'',sysdate) ";
        
        String ids=String.valueOf(id);
        String name="N_"+ids;
        
        Object[] arr={Table,ids,name,String.valueOf(orderNo),String.valueOf(shipperCode)};
        
        return MessageFormat.format(raw, arr);
    }
    
    // get a random shipper-code
    private int getShipperCode() {
        int[] arr= {1111,2222,3333,4444,5555,6666,7777,8888,9999,1010,2020,3030,4040,5050,6060,7070,8080,9090,1011,2022,3033,4044,5055,6066,7077,8088,9099,1811,2822,3833,4844,5855,6866,7877,8888,9899};// 调整shipper_code，L120
        int seed=getRandom(0,arr.length-1);
        return arr[seed];
    }
    
    // get a random integer between min and max
    public static int getRandom(int min, int max){
        Random random = new Random();
        int rnd = random.nextInt(max) % (max - min + 1) + min;
        return rnd;
    }
    
    // change seconds to DayHourMinuteSecond format
    private static String ms2DHMS(long startMs, long endMs) {
        String retval = null;
        long secondCount = (endMs - startMs) / 1000;
        String ms = (endMs - startMs) % 1000 + "ms";

        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 + ms;
    }

    // Entry point
    public static void main(String[] args) {
        TableRecordInserter tri=new TableRecordInserter();
        tri.fillTable();
    }
}

--END-- 2019年12月22日13:53:10