开启批量插入
- 数据库连贯url中减少一个rewriteBatchedStatements=true（开启批量插入）
2.用foreach拼接sql插入语句
环境配置
- 环境阐明：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();}