SpringBoot+EasyExcel+Mybatis+H2实现导入

SpringBoot+EasyExcel+Mybatis+H2实现导入

1.准备工作

• Spring Boot
• Easy Excel
• Mybatis
• H2数据库
• 工具类-生成随机字符串，返回结果集

1.1 依赖管理

<?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.2.6.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.geekmice</groupId><artifactId>fifth</artifactId><version>0.0.1-SNAPSHOT</version><name>fifth</name><description>fifth</description><properties><java.version>8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency><!--mysql connector--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope><version>8.0.28</version></dependency><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><!--swagger--><dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-spring-boot-starter</artifactId><version>2.0.7</version><exclusions><exclusion><artifactId>springfox-spring-webmvc</artifactId><groupId>io.springfox</groupId></exclusion><exclusion><artifactId>swagger-models</artifactId><groupId>io.swagger</groupId></exclusion></exclusions></dependency><!--接口平台--><dependency><groupId>io.springfox</groupId><artifactId>springfox-boot-starter</artifactId><version>3.0.0</version></dependency><!--easyexcel--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency><!--h2--><dependency><groupId>com.h2database</groupId><artifactId>h2</artifactId><scope>runtime</scope></dependency><!--swagger--><dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-spring-boot-starter</artifactId><version>2.0.7</version><exclusions><exclusion><artifactId>springfox-spring-webmvc</artifactId><groupId>io.springfox</groupId></exclusion><exclusion><artifactId>swagger-models</artifactId><groupId>io.swagger</groupId></exclusion></exclusions></dependency><!--接口平台--><dependency><groupId>io.springfox</groupId><artifactId>springfox-boot-starter</artifactId><version>3.0.0</version></dependency></dependencies></project>

1.2 配置信息properties

# mybatis
mybatis.mapper-locations=classpath:mapper/*.xml
# mybatis??
mybatis.type-aliases-package=com.geekmice.fifth.entity# h2 
spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=saspring.servlet.multipart.max-file-size=500MB
spring.servlet.multipart.max-request-size=500MB
spring.servlet.multipart.enabled=true

1.3 H2数据库

H2是开源的轻量级关系型数据库，支持内存模式、嵌入式部署及服务端模式。其特点包括：

• 兼容性：支持SQL:2011SQ**L:2011标准，部分兼容MySQL/PostgreSQL语法
• 高性能：内存模式下读写速度可达106106次/秒级别
• 零配置：单一JAR包（约2.3MB2.3MB）即可运行

1.4 Spring Boot 基础概念

1. 核心特性
   Spring Boot 通过自动配置和约定大于配置的原则简化 Spring 应用开发2。
   • 自动配置：根据类路径依赖自动配置 Bean（如引入spring-boot-starter-web自动配置 Tomcat）
   • 内嵌服务器：默认集成 Tomcat（可切换为 Jetty 或 Undertow）
   • 独立运行：通过main方法直接启动应用
2. 核心注解 @SpringBootApplication
   该注解整合了：
   • @SpringBootConfiguration：标记为配置类
   • @EnableAutoConfiguration：启用自动配置
   • @ComponentScan：自动扫描当前包及子包的组件

1.5 Mybatis

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。以下为你介绍一些 MyBatis 的基础知识：

核心概念

1. SqlSessionFactory：这是 MyBatis 的核心对象，它是一个工厂类，用于创建SqlSession对象。
2. SqlSession：这是一个会话对象，类似于 JDBC 中的Connection，它提供了执行 SQL 语句的方法。
3. Mapper 接口：这是一个 Java 接口，用于定义 SQL 方法。MyBatis 会自动为这个接口生成实现类。
4. Mapper XML 文件：用于编写 SQL 语句，也可以使用注解来替代。

1.6 EasyExcel

EasyExcel 是阿里巴巴开源的一个操作 Excel 的框架，它具有简单易用、节省内存等优点。下面为你详细介绍 EasyExcel：

核心概念

• 读取 Excel：从 Excel 文件里读取数据，支持多种数据格式以及自定义读取逻辑。
• 写入 Excel：把数据写入到 Excel 文件，能自定义表头、样式等。
• 模型映射：借助 Java 对象和 Excel 表格进行映射，达成数据的自动读写。

2.生成Excel数据

工具类-随机字符串

package com.geekmice.fifth.util;
import java.util.concurrent.ThreadLocalRandom;public class ThreadLocalRandomStringGenerator {private static final String CHARACTERS = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";public static String generateRandomString(int length) {StringBuilder sb = new StringBuilder(length);for (int i = 0; i < length; i++) {int index = ThreadLocalRandom.current().nextInt(CHARACTERS.length());sb.append(CHARACTERS.charAt(index));}return sb.toString();}public static void main(String[] args) {String randomString = generateRandomString(10);System.out.println("使用 ThreadLocalRandom 生成的字符串: " + randomString);}
}

编写生成Excel的java文件

package com.geekmice.fifth.util;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Builder;
import lombok.Data;import java.util.ArrayList;
import java.util.Date;
import java.util.List;@Data
@Builder
class ProductDetail {/*** 产品id*/@ExcelIgnoreprivate Integer id;/*** 产品详情*/@ExcelProperty(value = "产品详情", index = 0)private String description;/*** 产品规格*/@ExcelProperty(value = "产品规格", index = 1)private String specifications;/*** 备注*/@ExcelProperty(value = "备注", index = 2)private String notes;/*** 订单时间*/@ExcelProperty(value = "订单时间", index = 3)private Date orderTime;@ExcelProperty(value = "产品id", index = 4)private Integer productId;
}public class DataExportEasyExcel {public static void main(String[] args) {generateData(10000);generateData(100000);generateData(1000000);}/*** rowCount 代表生成数据的行数*/public static void generateData(int rowCount) {// 准备数据List<ProductDetail> dataList = new ArrayList<>();for (int i = 0; i < rowCount; i++) {dataList.add(ProductDetail.builder().description(ThreadLocalRandomStringGenerator.generateRandomString(10)).specifications(ThreadLocalRandomStringGenerator.generateRandomString(4)).notes(ThreadLocalRandomStringGenerator.generateRandomString(7)).orderTime(new Date()).productId(i).build());}String fileName = null;if (rowCount == 10000) {fileName = "D:\\Files\\Idea\\applicationscenario\\fifth\\src\\main\\resources\\static\\ExcelData1w.xlsx";} else if (rowCount == 100000) {fileName = "D:\\Files\\Idea\\applicationscenario\\fifth\\src\\main\\resources\\static\\ExcelData10w.xlsx";} else if (rowCount == 1000000) {fileName = "D:\\Files\\Idea\\applicationscenario\\fifth\\src\\main\\resources\\static\\ExcelData100w.xlsx";}/*** 这行代码使用EasyExcel库的write方法来创建一个Excel文件，并将dataList中的产品数据写入到Excel文件的"sheet1"工作表中。* fileName参数指定了生成的Excel文件的保存路径和名称*/EasyExcel.write(fileName, ProductDetail.class).sheet("sheet1").doWrite(dataList);}}

3.导入功能并且存入数据库

3.1 返回结果集R

package com.geekmice.fifth.util;import lombok.*;
import org.springframework.http.HttpStatus;import java.io.Serializable;@ToString
@NoArgsConstructor
@AllArgsConstructor
public class R<T> implements Serializable {private static final long serialVersionUID = 1L;@Getter@Setterprivate int code;@Getter@Setterprivate String msg;@Getter@Setterprivate T data;public static <T> R<T> ok() {return restResult(null, HttpStatus.OK.value(), HttpStatus.OK.getReasonPhrase());}public static <T> R<T> ok(T data) {return restResult(data, HttpStatus.OK.value(), HttpStatus.OK.getReasonPhrase());}public static <T> R<T> ok(T data, String msg) {return restResult(data, HttpStatus.OK.value(), msg);}public static <T> R<T> failed() {return restResult(null, HttpStatus.INTERNAL_SERVER_ERROR.value(), HttpStatus.INTERNAL_SERVER_ERROR.getReasonPhrase());}public static <T> R<T> failed(String msg) {return restResult(null, HttpStatus.INTERNAL_SERVER_ERROR.value(), msg);}public static <T> R<T> failed(T data) {return restResult(data, HttpStatus.INTERNAL_SERVER_ERROR.value(), HttpStatus.INTERNAL_SERVER_ERROR.getReasonPhrase());}public static <T> R<T> failed(T data, String msg) {return restResult(data, HttpStatus.INTERNAL_SERVER_ERROR.value(), msg);}public static <T> R<T> restResult(T data, int code, String msg) {R<T> apiResult = new R<>();apiResult.setCode(code);apiResult.setData(data);apiResult.setMsg(msg);return apiResult;}}

3.2 实体类javabean

package com.geekmice.fifth.entity;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Builder;
import lombok.Data;import java.util.Date;@Data
public class ProductDetailExcel {/*** 产品id*/@ExcelIgnoreprivate Integer id;/*** 产品详情*/@ExcelProperty(value = "产品详情", index = 0)private String description;/*** 产品规格*/@ExcelProperty(value = "产品规格", index = 1)private String specifications;/*** 备注*/@ExcelProperty(value = "备注", index = 2)private String notes;/*** 订单时间*/@ExcelProperty(value = "订单时间", index = 3)private Date orderTime;@ExcelProperty(value = "产品id", index = 4)private Integer productId;
}

3.3 dao数据层

package com.geekmice.fifth.dao;import com.geekmice.fifth.entity.ProductDetail;
import com.geekmice.fifth.entity.ProductDetailExcel;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;@Mapper
public interface ProductDetailDao {int insertBatch(@Param("list") List<ProductDetailExcel> cachedStudentList);void truncate();
}

3.4 dao映射类

<?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.geekmice.fifth.dao.ProductDetailDao"><insert id="insertBatch" ><foreach collection="list" item="item" separator=";">insert into product_detail (specifications, description, notes, order_time,product_id)values(#{item.specifications}, #{item.description}, #{item.notes}, #{item.orderTime},#{item.productId})</foreach></insert></mapper>

3.5 服务层

package com.geekmice.fifth.service;import com.geekmice.fifth.entity.ProductDetail;
import org.springframework.web.multipart.MultipartFile;import java.io.File;
import java.io.IOException;
import java.util.List;/*** @author geekmice* @description: ProductDetailService 服务层* @date 2020/4/10*/
public interface ProductDetailService {/*** 读取临时文件并保存到数据库中* @param tempFile 临时文件*/void readAndSave(MultipartFile tempFile) throws IOException;}package com.geekmice.fifth.service.impl;import com.alibaba.excel.EasyExcel;
import com.geekmice.fifth.dao.ProductDetailDao;
import com.geekmice.fifth.entity.ProductDetail;
import com.geekmice.fifth.entity.ProductDetailExcel;
import com.geekmice.fifth.listener.ProductDetailListener;
import com.geekmice.fifth.service.ProductDetailService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;
import java.util.List;@Service
@RequiredArgsConstructor
public class ProductDetailImpl implements ProductDetailService {private final ProductDetailDao productDetailDao;@Overridepublic void readAndSave(MultipartFile file) throws IOException {productDetailDao.truncate();EasyExcel.read(file.getInputStream(), ProductDetailExcel.class, new ProductDetailListener(productDetailDao)).sheet().doRead();}}

3.6 控制层

package com.geekmice.fifth.controller;import com.alibaba.excel.EasyExcel;
import com.geekmice.fifth.dao.ProductDetailDao;
import com.geekmice.fifth.entity.ProductDetail;
import com.geekmice.fifth.entity.ProductDetailExcel;
import com.geekmice.fifth.listener.ProductDetailListener;
import com.geekmice.fifth.service.ProductDetailService;
import com.geekmice.fifth.util.R;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;/*** @description: ProductDetailController 商品详情控制器* @author: pmb* @date 2021/4/16* @time 10:30*/
@RestController
@RequestMapping(value = "/productDetail")
@RequiredArgsConstructor
@Slf4j
public class ProductDetailController {private final ProductDetailService productDetailService;/*** @description: 导入Excel数据*/@PostMapping(value = "/importExcel")public R importExcel(@RequestParam("file") MultipartFile file) {try {productDetailService.readAndSave(file);} catch (Exception e) {log.error("error msg 【{}】", e);e.printStackTrace();}return R.ok();}}

3.7 监听器

package com.geekmice.fifth.listener;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.geekmice.fifth.dao.ProductDetailDao;
import com.geekmice.fifth.entity.ProductDetailExcel;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;
import java.util.List;@Slf4j
@RequiredArgsConstructor
public class ProductDetailListener extends AnalysisEventListener<ProductDetailExcel> {private final ProductDetailDao productDetailDao;private static final int BATCH_SIZE = 1000;private List<ProductDetailExcel> cachedStudentList = new ArrayList<>();@Overridepublic void invoke(ProductDetailExcel student, AnalysisContext analysisContext) {
//        log.info("开始保存数据...");cachedStudentList.add(student);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (cachedStudentList.size() >= BATCH_SIZE) {saveData();cachedStudentList.clear();}}private void saveData() {
//        log.info("{}条数据，开始存储数据库！", cachedStudentList.size());productDetailDao.insertBatch(cachedStudentList);
//        log.info("存储数据库成功！");}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 所有数据解析完成后的操作// 例如：将数据保存到数据库saveData();
//        System.out.println("所有数据解析完成！");}public List<ProductDetailExcel> getCachedStudentList() {return cachedStudentList;}
}

3.8 postman & 数据库情况