Excel文件导入导出，SpringBoot整合EasyExcel批量导入导出，采用的JDBC+EasyExcel（附带整个Demo）

目录

0.为什么mybatis的foreach比JDBC的addBatch慢

1.引入依赖

2.Controller层

3.Service层

4.Utils工具类

5.自定义监听器

6.实体类

7Mapper层

---

不用Mybatis的原因就是因为在大量数据插入的时候jdbc性能比mybatis好

1. 首先分批读取Excel中的数据 这一点EasyExcel有自己的解决方案 2.其次就是DB里插入，怎么去插入这20w条数据 当然不能一条一条循环，应该批量插入20w条数据 3.使用JDBC+事务的批量操作将数据插入到数据库

整个Demo连接，打开下载即可，包含数据库表

整个Demo连接，打开下载即可，包含数据库表

 

整个Demo连接，打开下载即可，包含数据库表​​​​​​整个Demo连接，打开下载即可，包含数据库表

整个Demo连接，打开下载即可，包含数据库表

0.为什么mybatis的foreach比JDBC的addBatch慢

1. ORM 框架开销：MyBatis 的 foreach 操作涉及到将对象数据转换为 SQL 语句的过程，在这个过程中需要进行对象到 SQL 的映射、动态 SQL 的解析等操作，这些额外的操作会增加开销。

2. 数据库连接管理：MyBatis 在执行 foreach 操作时，会频繁地获取和释放数据库连接，而数据库连接的获取和释放是一个相对耗时的操作，特别在百万级数据的情况下，这种开销可能会积累导致性能下降。

3. SQL 语句生成：MyBatis 的 foreach 操作在执行过程中会生成大量的 SQL 语句，这可能会导致数据库的缓存失效、重新编译查询计划等，从而影响性能。

4. 批量插入优化：JDBC 的 addBatch 可以直接利用底层数据库的批量插入功能，而 MyBatis 的 foreach 操作在某些数据库上可能不能充分利用数据库的批量插入优化。

1.引入依赖

<dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.7</version>
</dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.2</version>
</dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>2.0.42</version>
</dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version>
</dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.18</version>
</dependency>
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version>
</dependency>
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version>
</dependency>
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-tomcat</artifactId><scope>provided</scope>
</dependency>
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope>
</dependency>

2.Controller层

@Slf4j
@RestController
@RequestMapping("excel")
public class ExcelController {@Autowiredprivate UserService userService;//导出@GetMapping("/exportExcel")public String exportExcel(HttpServletRequest request, HttpServletResponse response){String file="D:";long startTime = System.currentTimeMillis();log.debug("-------------开始插入-------------------");userService.exportInspectionPlan(request,response);return "ok";}//导入@PostMapping("/importExcel")public void importExcel(MultipartFile multipartFile) throws IOException {if (multipartFile.isEmpty()) {return;}// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭// 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行EasyExcel.read(multipartFile.getInputStream(), ExportPlanInformationVo.class,new PageReadListener<ExportPlanInformationVo>(dataList -> {for (ExportPlanInformationVo user : dataList) {//将导入的数据用mybatisPlus一个个添加进数据库System.out.println(user);}})).sheet("现场巡视计划报表").doRead();}/*** 1. 首先分批读取Excel中的数据* 这一点EasyExcel有自己的解决方案** 2.其次就是DB里插入，怎么去插入这20w条数据* 当然不能一条一条循环，应该批量插入20w条数据* 同样不能选择Mybatis的批量插入，因为效率低** 3.使用JDBC+事务的批量操作将数据插入到数据库* *///批量导入@PostMapping("/batchImportExcel")public void batchImportExcel(MultipartFile file) throws IOException {if (BeanUtil.isEmpty(file)){log.debug("传入的文件不能为空！");return ;}if (!Objects.requireNonNull(file.getOriginalFilename()).endsWith("xls") && !file.getOriginalFilename().endsWith("xlsx")){log.debug("请上传Excel文件！");return ;}CommonExportListenerDto commonExportListenerDto = new CommonExportListenerDto();EasyExcel.read(file.getInputStream(),commonExportListenerDto).doReadAll();}
}

3.Service层

@Service
@Slf4j
public class UserService {
//@Resourceprivate IndMapper indMapper;//文件导出public void exportInspectionPlan(HttpServletRequest request, HttpServletResponse response) {//以上需要根据自己的业务去做数据处理   这里就不做展示try {//给文件命名String fileName = "ExcelTest";// 设置响应头response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("UTF-8");// 设置防止中文名乱码fileName = URLEncoder.encode(fileName, "utf-8");// 文件下载方式(附件下载还是在当前浏览器打开)response.setHeader("Content-disposition", "attachment;filename=" +fileName + ".xlsx");//向excel表格写入数据EasyExcel.write(response.getOutputStream(), ExportPlanInformationVo.class).sheet("下方导航").doWrite(getAll());
/*//下载到指定路径String fileUrl = "D://ExcelTest.xlsx";//向excel表格写入数据EasyExcel.write(fileUrl, ExportPlanInformationVo.class).sheet("下方导航").doWrite(getAll());
*/} catch (Exception e) {log.error("出现错误 {}", e);}}public List<ExportPlanInformationVo> getAll(){//根据业务逻辑获取数据
//        List<ExportPlanInformationVo> all = indMapper.getAll();return  indMapper.getAll();}}

4.Utils工具类

import java.sql.*;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;public class JDBCUtil {private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false";private static String username = "root";private static String password = "196713";private static String driverName = "com.mysql.jdbc.Driver";/*** 获取连接对象** @return 连接对象*/public static Connection getConnection() {Connection conn = null;try {// 1. 注册驱动Class.forName(driverName);// 2. 获取连接对象conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}return conn;}/*** 释放资源** @param connection 连接对象* @param statement  预编译执行对象* @param resultSet  结果集*/public static void releaseResources(Connection connection, PreparedStatement statement, ResultSet resultSet) {// 释放资源if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}}public static void insertBatch(List<Map<Object, Object>> dataList, String sql) {Connection conn = null;PreparedStatement pstm = null;try {conn = getConnection();//如果需要开启事务此处需要将自动提交关闭// conn.setAutoCommit(false);//预编译sqlpstm = (PreparedStatement) conn.prepareStatement(sql);for (Map<Object, Object> map : dataList) {//此处类型判断不完整后续可以借鉴jdk自行封装拦截器for (int i = 1; i <= map.size(); i++) {Object o = map.get(i-1);if (BeanUtil.isEmpty(o)) {pstm.setString(i, null);continue;}if (o instanceof String) {pstm.setString(i, o.toString());continue;}if (o instanceof Integer) {pstm.setInt(i, Integer.parseInt(o.toString()));continue;}if (o instanceof LocalDateTime) {pstm.setDate(i, new Date(System.currentTimeMillis()));continue;}if (o instanceof Boolean) {pstm.setBoolean(i, Boolean.parseBoolean(o.toString()));}}//添加到同一个批处理中pstm.addBatch();}//执行批处理pstm.executeBatch();//如果需要开启事务此处需要手动提交事务//conn.commit();} catch (Exception e) {e.printStackTrace();}}}

5.自定义监听器

@Data
@Slf4j
public class CommonExportListenerDto extends AnalysisEventListener<Map<Object, Object>> {/*** 表头数据（存储所有的表头数据）*/private List<Map<Integer, String>> headList = new ArrayList<>();/** 数据体*/private List<Map<Object, Object>> dataList = new ArrayList<>();/*** 存储全部表头数据* @param headMap* @param context*/@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {headList.add(headMap);}/*** 每一条数据解析都会来调用* @param data* @param context*/@Overridepublic void invoke(Map<Object, Object> data, AnalysisContext context) {dataList.add(data);if (dataList.size() >= 3) {saveData();// 存储完成清理 listdataList = ListUtils.newArrayListWithExpectedSize(2000);}}/*** 所有数据解析完成之后的操作* @param analysisContext*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {saveData();}private void saveData() {log.info("{}条数据，开始存储数据库！", dataList.size());//批量导入JDBCUtil.insertBatch(dataList,"INSERT INTO ind (a,b,c,d,e) VALUES(?,?,?,?,?);");log.info("存储数据库成功！");}
}

6.实体类

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽
@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 11)
public class ExportPlanInformationVo  implements Serializable {// 1. 如果不想某个字段在excel中出现  可以加  @ExcelIgnore注解@ExcelProperty(value = "a")private String a;//    @Dict(code = "inspectionType", fieldName = "inspectionTypeName")@ExcelProperty(value = "b")private String b;@ExcelProperty(value = "c")private String c;@ExcelProperty(value = "d")private String d;@ExcelProperty(value = "c")private String e;}

7Mapper层

@Mapper
public interface IndMapper {@Select("select * from ind")List<ExportPlanInformationVo> getAll();}