mybatis 百万数据插入

开启批量插入

1. 数据库连接url中增加一个rewriteBatchedStatements=true（开启批量插入）

2.用foreach拼接sql插入语句

环境配置

1. 环境说明：WIN10+16G内存+i7-4770U3.40GHz+JDK8+MySQL8.0+

   上代码

@Test

public void monitor(){
    //循环次数
    int count = 100;
    //每次插入条数
    int size = 10000;
    Calendar calendar = Calendar.getInstance();
    //calendar的月从0开始算
    calendar.set(2011,05,01,0,0,0);
    long dayTime = calendar.getTimeInMillis();
    long sumTime = 0L;
    for (int i = 0; i < count; i++) {
        List<Monitor> monitors = new ArrayList<Monitor>();
        for (int j = 0; j < size; j++) {
            Monitor monitor = new Monitor();
            monitor.setId(IdWorker.getId());
            monitor.setBdjm("铸造二期3#进线");
            monitor.setBdz("00000000023");
            monitor.setRqsj(new Date(dayTime));
            dayTime += 300_000L; // 5*60*1000为5分钟
            monitor.setYgdl(RandomUtil.getRandomNumber(1,10));
            monitor.setWgdl(RandomUtil.getRandomNumber(1,10));
            monitor.setFwgdl(RandomUtil.getRandomNumber(1,10));
            monitor.setFygdl(RandomUtil.getRandomNumber(1,10));
            monitor.setOnexxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setThreexxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setTwoxxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setFourxxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setAa(RandomUtil.getRandomNumber(1,10));
            monitor.setAv(RandomUtil.getRandomNumber(1,10));
            monitor.setBa(RandomUtil.getRandomNumber(1,10));
            monitor.setBv(RandomUtil.getRandomNumber(1,10));
            monitor.setCa(RandomUtil.getRandomNumber(1,10));
            monitor.setCv(RandomUtil.getRandomNumber(1,10));
            monitor.setGlcs(RandomUtil.getRandomNumber(1,10));
            monitor.setSzgl(RandomUtil.getRandomNumber(1,10));
            monitor.setWggl(RandomUtil.getRandomNumber(1,10));
            monitor.setAxgl(RandomUtil.getRandomNumber(1,10));
            monitor.setAxglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setAxwgl(RandomUtil.getRandomNumber(1,10));
            monitor.setBxgl(RandomUtil.getRandomNumber(1,10));
            monitor.setBxglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setBxwgl(RandomUtil.getRandomNumber(1,10));
            monitor.setCxgl(RandomUtil.getRandomNumber(1,10));
            monitor.setCxglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setCxwgl(RandomUtil.getRandomNumber(1,10));
            monitor.setYggl(RandomUtil.getRandomNumber(1,10));
            monitor.setDwpl(RandomUtil.getRandomNumber(1,10));
            monitor.setUabUaAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setUbAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setUcbUcAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setZglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setDlbph(RandomUtil.getRandomNumber(1,10));
            monitor.setIaAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setIbAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setIcAngle(RandomUtil.getRandomNumber(1,10));
            monitors.add(monitor);
        }
        Instant inst1 = Instant.now();
        monitorService.batchInsert(monitors);
        Instant inst2 = Instant.now();
        sumTime += ChronoUnit.MILLIS.between(inst1, inst2);
    }
    System.out.println( formatTime(sumTime) );
}
public static String formatTime(Long ms) {
    Integer ss = 1000;
    Integer mi = ss * 60;
    Integer hh = mi * 60;
    Integer dd = hh * 24;

    Long day = ms / dd;
    Long hour = (ms - day * dd) / hh;
    Long minute = (ms - day * dd - hour * hh) / mi;
    Long second = (ms - day * dd - hour * hh - minute * mi) / ss;
    Long milliSecond = ms - day * dd - hour * hh - minute * mi - second * ss;

    StringBuffer sb = new StringBuffer();
    if(day > 0) {
        sb.append(day+"天");
    }
    if(hour > 0) {
        sb.append(hour+"小时");
    }
    if(minute > 0) {
        sb.append(minute+"分");
    }
    if(second > 0) {
        sb.append(second+"秒");
    }
    if(milliSecond > 0) {
        sb.append(milliSecond+"毫秒");
    }
    return sb.toString();
}