使用springboot+mybatisPlus+easyExcel+MySQL读取并储存，60000+条数据，15秒
闲话少说，直接开摆，记录一下最近用到的知识。
| 测试耗时（ms） |
|---|
| 读取6万条数据储存到MySQL，连续测试了五次，综合来看（14.88）约 15秒 |
| No.1 耗时：17702，行数：60000 |
| No.2 耗时：15099，行数：60000 |
| No.3 耗时：14355，行数：60000 |
| No.4 耗时：13673，行数：60000 |
| No.5 耗时：13848，行数：60000 |
1、Pom.xml配置文件
easyExcel我在这里使用的是3.1.1版本，大家可以根据自己需求选择
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<!-- 下方是我的全部配置文件内容... --><?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.7</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.xiawb</groupId><artifactId>demo</artifactId><version>0.0.1-SNAPSHOT</version><name>demo</name><description>Demo project for Spring Boot</description><properties><java.version>17</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- mybatis-plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.3</version></dependency><!-- springboot热部署 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><!-- mysql --><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency><!-- lombok --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- fastjson --><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>2.0.22</version></dependency><!-- easyexcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency><!-- 解析时间格式 @jsonformat --><dependency><groupId>com.fasterxml.jackson.core</groupId><artifactId>jackson-databind</artifactId><version>2.14.1</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins></build></project>
2、先来看看 怎么读取excel，储存
2.1、定义实体类
注意 --> 我这里Excel表头字段和数据库字段都是对应的
如果字段不对应，请加上 @TableField(value = “你的数据库字段名”)
…先来看看 ExcelSheetVo.java
import java.util.Date;import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;import lombok.Data;
import lombok.EqualsAndHashCode;@Data
@EqualsAndHashCode
@TableName("power")
public class ExcelSheetVo {// 电表位置@ExcelProperty("电表位置")// 这里作为不一致的演示// @TableField(value = "数据库字段")private String plantLocation;// 倍率@ExcelProperty("倍率")private String multiplier;// 日期时间@ExcelProperty("日期时间")@JsonFormat(pattern = "yyyy-MM-dd")private Date date;// 时段@ExcelProperty("时段")private String timeInterval;// 电表数值@ExcelProperty("电表数值")private Double meterValue;// 电量数值@ExcelProperty("电量数值")private Double powerValue;// 百分比@ExcelProperty("百分比")private Double percentage;
}
2.2、创建ExcelSheetVoMapper.xml
再来看看 ExcelSheetVoMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiawb.demo.mapper.ExcelSheetVoMapper"><insert id="saveList">insert into power(plantLocation, multiplier, date, timeInterval, meterValue, powerValue, percentage) values<foreach collection="ExcelSheetVoList" index="index" item="item" separator=",">(#{item.plantLocation}, #{item.multiplier}, #{item.date}, #{item.timeInterval}, #{item.meterValue}, #{item.powerValue}, #{item.percentage})</foreach></insert>
</mapper>
2.3、创建ExcelSheetVoMapper接口
再来看看 ExcelSheetVoMapper.java
…
批量储存–> saveList
…
import java.util.List;import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiawb.demo.model.ExcelSheetVo;/*** 创建于2023/01/12* 创建人：xiawb* */
@Mapper
public interface ExcelSheetVoMapper extends BaseMapper<ExcelSheetVo> {/*** @param list*/Integer saveList(@Param("ExcelSheetVoList") List<ExcelSheetVo> list);
}
2.3、定义excelService接口
再来看看 ExcelService.java
import java.io.IOException;import javax.servlet.http.HttpServletResponse;import org.springframework.web.multipart.MultipartFile;public interface ExcelService {/*** 导出电量信息* @param response* @throws IOException*/public void excelExport(HttpServletResponse response);/*** 导入电量信息* @param file* @throws IOException*/public void excelImport(MultipartFile file) throws IOException;}
2.4、实现excelService接口
…
暂时没有导出的需求，所以就没有实现
…
再来看看 ExcelServiceImpl.java
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;import javax.servlet.http.HttpServletResponse;import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;import com.alibaba.excel.EasyExcel;
import com.xiawb.demo.model.ExcelSheetVo;
import com.xiawb.demo.server.ExcelListener;
import com.xiawb.demo.server.ExcelService;import lombok.Getter;
import lombok.extern.slf4j.Slf4j;/*** @author xiawb* @version 1.0.0* @ClassName ExcelServiceImpl* @Description TODO* @createTime 2023.01.12 16:37*/
@Getter
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {// 记录插入数据库行数private Integer resultRow;/*** 导出电量表信息* @param response* @throws IOException*/@Overridepublic void excelExport(HttpServletResponse response) {}/*** 导入电量表信息到 MySQL* @param file* @throws IOException*/@Overridepublic void excelImport(MultipartFile file) throws IOException{long startTime = new Date().getTime();InputStream inputStream = file.getInputStream();ExcelListener<ExcelSheetVo> eListener = new ExcelListener<ExcelSheetVo>();EasyExcel.read(inputStream, ExcelSheetVo.class, eListener)// 设置sheet,默认读取第一个.sheet("电量统计")// 设置标题所在行数.headRowNumber(1)// 异步读取.doReadSync();resultRow = eListener.getCounts().get();long endTime = new Date().getTime();log.info("耗时：{}，行数：{}",endTime - startTime,eListener.getCounts().get());}
}
2.5、实现ExcelListener监听
…
再来看看 ExcelListener.java
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.xiawb.demo.mapper.ExcelSheetVoMapper;
import com.xiawb.demo.model.ExcelSheetVo;import lombok.Getter;
import lombok.extern.slf4j.Slf4j;@Slf4j
@Getter
@Component
public class ExcelListener<T> extends AnalysisEventListener<T> {// excel行数private final AtomicInteger counts = new AtomicInteger();// 每次Excel最多10000行数据存入 MySQLprivate final Integer MAX_COUNT = 10000;// 临时储存 10000 条数据的Excel列表Listprivate List<ExcelSheetVo> tempExcelData = new ArrayList<ExcelSheetVo>();private static ExcelSheetVoMapper excelSheetVoMapper;@Autowiredpublic void setPowerMapper(ExcelSheetVoMapper excelSheetVoMapper){ExcelListener.excelSheetVoMapper = excelSheetVoMapper;}@Overridepublic void invoke(Object arg0, AnalysisContext arg1) {// TODO Auto-generated method stubtempExcelData.add((ExcelSheetVo) arg0);log.info("Excel导入数据行数：{}", counts.incrementAndGet());if(tempExcelData.size() >= MAX_COUNT){excelSheetVoMapper.saveList(tempExcelData);tempExcelData.clear();}}@Overridepublic void doAfterAllAnalysed(AnalysisContext arg0) {// 在 Excel 获取结束后调用// 如果这里不做判断，MySQL会报错，判断是否还有剩余的数据没有进行储存if(!(counts.get() % MAX_COUNT == 0)){excelSheetVoMapper.saveList(tempExcelData);tempExcelData.clear();}}}
3、再来看看 怎么实现controller层
3.1、实现Excel上传
…
再来看看 ExcelReportTo.java
import java.io.IOException;import javax.servlet.http.HttpServletResponse;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;import com.xiawb.demo.result.ResultApi;
import com.xiawb.demo.server.impl.ExcelServiceImpl;@RestController
public class ExcelReportTo {@Autowiredprivate ExcelServiceImpl excelServiceImpl;/*** 导出用户信息* @param response* @throws IOException*/@GetMapping("/user/excelExport")public void excelExport(HttpServletResponse response) throws IOException {excelServiceImpl.excelExport(response);}/*** 导入用户信息* @param file* @return* @throws IOException*/@PostMapping("/user/excelImport")public ResultApi<Integer> excelImport(@RequestParam("file") MultipartFile file) throws IOException {excelServiceImpl.excelImport(file);return ResultApi.success(excelServiceImpl.getResultRow());}}
ResultApi.success(excelServiceImpl.getResultRow())
返回接口 可以自己定义完结说骚话，好了说完了，最后欢迎大家留言
…
