©2020-2024 ioDraw All rights reserved
Mybatis批量插入百万级数据量 注意事项及实现:
application.yml中 jdbc的url配置要将rewriteBatchedStatements=true
配置,如果不配置,mybatis-plus的saveBatch方法不生效.
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url:
jdbc:mysql://localhost:3306/dataBase?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8
username: xxx password: xxx
代码实现:
package com.yhh.studysys.test.entity; import
com.baomidou.mybatisplus.annotation.IdType; import
com.baomidou.mybatisplus.annotation.TableId; import java.io.Serializable;
import lombok.*; /** * <p> * 测试实体类 * </p> * * @author yhh * @since 2021-03-16
*/ @Data @EqualsAndHashCode(callSuper = false) @Builder @NoArgsConstructor
@AllArgsConstructor public class ExportExcel implements Serializable { private
static final long serialVersionUID = 1L; @TableId(value = "id", type =
IdType.AUTO) private Long id; private String field1; private String field2;
private String field3; private String field4; private String field5; private
String field6; private String field7; private String field8; private String
field9; private String field10; private String field11; private String field12;
private String field13; private String field14; private String field15; private
String field16; }
测试类:
//业务实现接口省略,mybatis-plus官方有生成工具 @Autowired private IExportExcelService
exportExcelService; @Test void contextLoads() { List<ExportExcel> list = new
ArrayList<>(); System.out.println("处理开始时间:" + new Date()); for(int i = 0 ; i <
1000000; i++){ ExportExcel excel = ExportExcel.builder() .field1("filed1:" + i)
.field2("filed2:" + i) .field3("filed3:" + i) .field4("filed4:" + i)
.field5("filed5:" + i) .field6("filed6:" + i) .field7("field7:" + i)
.field8("field8:" + i) .field9("field9:" + i) .field10("field10:" + i)
.field11("filed11:" + i) .field12("filed12:" + i) .field13("filed13:" + i)
.field14("filed14:" + i) .field15("filed15:" + i) .field16("filed16:" + i)
.build(); list.add(excel); } System.out.println("处理结束时间:" + new Date());
exportExcelService.saveBatch(list); System.out.println("插入结束时间:" + new Date());
}
处理日志,100万数据插入约1m43s左右,如果不配置rewriteBatchedStatements=true,时间将在半个小时开外,有兴趣可以试一下~
导出excel:引入easyExcel依赖
<dependency> <groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency>
导出实体类
package com.yhh.studysys.test.entity; import
com.alibaba.excel.annotation.ExcelProperty; import lombok.AllArgsConstructor;
import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor;
import java.io.Serializable; /** * <p> * excel导出实体类 * </p> * * @author yhh *
@since 2021-03-16 */ @Data @Builder @NoArgsConstructor @AllArgsConstructor
public class ExportEntity implements Serializable { private static final long
serialVersionUID = 1L; @ExcelProperty("ID") private Long id;
@ExcelProperty("field1") private String field1; @ExcelProperty("field2")
private String field2; @ExcelProperty("field3") private String field3;
@ExcelProperty("field4") private String field4; @ExcelProperty("field5")
private String field5; @ExcelProperty("field6") private String field6;
@ExcelProperty("field7") private String field7; @ExcelProperty("field8")
private String field8; @ExcelProperty("field9") private String field9;
@ExcelProperty("field10") private String field10; @ExcelProperty("field11")
private String field11; @ExcelProperty("field12") private String field12;
@ExcelProperty("field13") private String field13; @ExcelProperty("field14")
private String field14; @ExcelProperty("field15") private String field15;
@ExcelProperty("field16") private String field16; }
导出测试方法:每次10万条,分页查询数据,分为多个sheet页
@Test public void exportTest(){ System.out.println("处理开始时间:" + new Date());
int count = exportExcelService.count(); int max = 100000; String filePath =
"C:\\Users\\Howeyang\\Desktop\\text.xlsx"; ExcelWriter excelWriter =
EasyExcel.write(filePath,ExportEntity.class).build(); try{ for(int i = 0 ; i <
(count / max) + 1; i ++){ List<ExportEntity> records =
exportExcelService.page(new Page<>( i + 1, max)).getRecords().stream().map( k
-> { ExportEntity excel = new ExportEntity();
BeanUtils.copyProperties(k,excel); return excel;
}).collect(Collectors.toList()); WriteSheet writeSheet =
EasyExcel.writerSheet(i, "sheet" + i) .registerWriteHandler(new
LongestMatchColumnWidthStyleStrategy()) .build();
excelWriter.write(records,writeSheet); log.info("sheet:{}导出完成",i); } } finally
{ if(excelWriter != null){ excelWriter.finish(); } }
System.out.println("处理结束时间:" + new Date()); }
导出结果:
100万数据,1m45s 文件大小77M
300万数据量导出时间在13分钟左右.文件大小231MB.