SpringBoot百万行Excel导入MySQL实践

最新推荐文章于 2024-09-05 23:05:40 发布
最新推荐文章于 2024-09-05 23:05:40 发布
阅读量1.2k 收藏 27
点赞数 29
分类专栏： Java开发实战 文章标签： spring boot mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_15752347/article/details/141498317
版权

在公司开发时，客户说需要支持大数据量excel导入，所以打算写一篇文章记录下思路和优化过程。

一、前期准备

  1. 首先我们选用的肯定是阿里出品的EasyExcel，对比poi和jxl占内存更少
    easyexcel官方网站
  2. 准备测试的数据库和excel文件，已经和代码一起上传到gitee仓库
    项目代码
  3. 修改mysql的max_allowed_packet
    解决MySQL的PacketTooBigException异常问题
  4. 修改了tomcat上传文件的默认限制，因为文件可能过大，会报错
server:
  port: 8888
  maxHttpHeaderSize: 102400
  servlet:
    context-path: /api
  error:
    include-exception: false
    include-message: always
spring:
  servlet:
    multipart:
      max-file-size: 100MB
      max-request-size: 100MB

在这里插入图片描述

  1. 开启MyBatis-Plus的批量插入功能，如果不需要请忽略
    Mybatis-Plus自定义批量插入的实现方法

二、依赖引入

        <!-- easyexcel依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.4</version>
        </dependency>

        <!-- hutool依赖 -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.16</version>
        </dependency>

        <!-- mysql依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
            <scope>runtime</scope>
        </dependency>

        <!--MyBatis plus配置-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

三、创建所需工具类

  • MultipartFileToFileUtils
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;

/**
 * 文件流工具  将传入的MultipartFile类型转为File类型，Controller接收到的是MultipartFile类型，EasyExcel.read方法所需要的是File类型。
 */
public class MultipartFileUtil {
    private final static String STATIC_PATH = "d:/upload/file/";

    public static File multipartFileToFile(MultipartFile file) throws Exception {
        File toFile = null;
        if (file.getSize() > 0) {
            InputStream ins;
            ins = file.getInputStream();
            toFile = new File(STATIC_PATH+file.getName());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    //获取流文件
    private static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = Files.newOutputStream(file.toPath());
            int bytesRead;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}

四、创建业务层代码

1.实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("sys_user")
public class User {

    @ExcelProperty("id")
    private Long id;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("身份证号码")
    private String idCard;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("性别")
    private String sex;

    @ExcelProperty("备注")
    private String remark;
}

2. Mapper层

如果不使用MyBatis-Plus的批量插入功能，正常继承BaseMapper就好

import com.demo.config.GemBaseMapper;
import com.demo.eneity.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends GemBaseMapper<User> {

}

3. service层

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import com.alibaba.excel.EasyExcel;
import com.demo.eneity.User;
import com.demo.excel.SimpleThreadListener;
import com.demo.utils.MultipartFileUtil;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

@Service
@AllArgsConstructor
@Slf4j
public class UserService  {
    public String importUserList(MultipartFile file) throws Exception {
        TimeInterval timer = DateUtil.timer();
        //不分片单线程插入
        EasyExcel.read(MultipartFileUtil.multipartFileToFile(file), User.class,new SimpleThreadListener()).sheet().doRead();

        //分片单线程插入
//        EasyExcel.read(MultipartFileUtil.multipartFileToFile(file), User.class,new CutDataListener()).sheet().doRead();

        //多线程
//        EasyExcel.read(MultipartFileUtil.multipartFileToFile(file), User.class,new MultiThreadListener()).sheet().doRead();

        log.info("导入成功,花费时间为{}毫秒", timer.interval());
        return "导入成功";
    }
}

4. controller层

import com.demo.service.UserService;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;

@RestController
@RequestMapping("/user")
public class UserController {

    @Resource
    UserService userService;

    @PostMapping("/import")
    public String importUserList(@RequestParam("file") MultipartFile file) throws Exception {
        return userService.importUserList(file);
    }

}

五、创建事件监听器

这里有三个版本的事件监听器，分别为单线程事件监听器、分片事件监听器、多线程事件监听器

1. 单线程事件监听器

import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.demo.eneity.User;
import com.demo.mapper.UserMapper;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

@Getter
@Component
@Slf4j
public class SimpleThreadListener extends AnalysisEventListener<User> {

    private List<User> list = Collections.synchronizedList(new ArrayList<>());

    public SimpleThreadListener() {

    }

    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        if (user != null) {
            list.add(user);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("解析完毕，开始插入");
        UserMapper userMapper = SpringUtil.getBean(UserMapper.class);
        userMapper.insertBatchSomeColumn(list);
    }
}

2. 分片事件监听器

import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.demo.eneity.User;
import com.demo.mapper.UserMapper;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Getter
@Setter
@Component
@Slf4j
public class CutDataListener extends AnalysisEventListener<User> {

    private List<User> list = new ArrayList<>();

    public CutDataListener() {

    }

    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        if (user != null) {
            list.add(user);
        }
        //分批插入，大于10w执行一次
        if(list.size() >= 100000) {
            saveData();
            list.clear();
        }
    }


    /**
     * 保存数据到db
     */
    private void saveData() {
        UserMapper userMapper = SpringUtil.getBean(UserMapper.class);
        userMapper.insertBatchSomeColumn(list);
        list.clear();
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        list.clear();
    }
}

3. 多线程事件监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.demo.eneity.User;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.*;

@Getter
@Component
@Slf4j
public class MultiThreadListener extends AnalysisEventListener<User> {

    private List<User> list = Collections.synchronizedList(new ArrayList<>());
    private final static int CORE_POOL_SIZE = 5;//核心线程数
    private final static int MAX_POOL_SIZE = 10;//最大线程数
    private final static int QUEUE_CAPACITY = 100;//队列大小
    private final static long KEEP_ALIVE_TIME = 1L;//存活时间

    public MultiThreadListener() {

    }

    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        if (user != null) {
            list.add(user);
        }
    }



    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("解析完毕，开始插入新数据");
        //创建一个新的线程池
        ExecutorService executorService = new ThreadPoolExecutor(CORE_POOL_SIZE,
                MAX_POOL_SIZE,
                KEEP_ALIVE_TIME,
                TimeUnit.MINUTES,
                new ArrayBlockingQueue<>(QUEUE_CAPACITY),
                new ThreadPoolExecutor.CallerRunsPolicy());
        //设置每个线程处理数据的数量
        int singleTreadDealCount = 1000;
        //要提交到线程池的线程数量
        int threadSize = (list.size() / singleTreadDealCount) + 1;
        //开始位置
        int startIndex = 0;
        //结束位置
        int endIndex = 0;
        //初始化闭锁,数量为线程数量
        CountDownLatch countDownLatch = new CountDownLatch(threadSize);
        for (int i = 0; i < threadSize; i++) {
            //最后一个的结束位置是数组的大小
            if ((i + 1) == threadSize) {
                startIndex = i * singleTreadDealCount;
                endIndex = list.size();
            } else {
                startIndex = i * singleTreadDealCount;
                endIndex = (i + 1) * singleTreadDealCount;
            }
            //创建自定义线程任务类,执行run方法
            UserThread thread = new UserThread(startIndex,endIndex,list,countDownLatch);
            executorService.execute(thread);
        }
        try {
            //当前线程开始等待
            countDownLatch.await();
        }catch (InterruptedException e){
            e.printStackTrace();
        }
        //通过countDownLatch控制所有线程都执行完,再关闭线程池
        executorService.shutdown();
        list.clear();
    }
}

4.创建多线程监控器用的线程任务类

import cn.hutool.extra.spring.SpringUtil;
import com.demo.eneity.User;
import com.demo.mapper.UserMapper;

import java.util.List;
import java.util.concurrent.CountDownLatch;


public class UserThread implements Runnable {

    private int startIndex;

    private int endIndex;

    private List<User> list;

    private CountDownLatch count;

    private UserMapper userMapper;

    public UserThread(int startIndex, int endIndex, List<User> list, CountDownLatch count) {
        this.startIndex = startIndex;
        this.endIndex = endIndex;
        this.list = list;
        this.count = count;
    }

    @Override
    public void run() {
        try {
            List<User> newList = list.subList(startIndex, endIndex);
            //防止空插入
            if (newList.size() > 0) {
                UserMapper userMapper = SpringUtil.getBean(UserMapper.class);
                userMapper.insertBatchSomeColumn(newList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //计数减一
            count.countDown();
        }
    }
}

实现Callable或者Runable或者继承Thread都行，这里实现Runable，重写run方法。然后根据传入位置区间，通过subList方法分割，执行批量插入方法进行数据的入库。在finally中执行coutDown，是为了防止插入时出现异常

注：关于CountDownLatch的详解，可以看我的这篇文章
浅谈CountDownLatch 和 CyclicBarrier

六、测试三种方法的效率

1.单线程

什么操作都不处理的情况下，耗时88秒
在这里插入图片描述
在这里插入图片描述

2.分片单线程

我们在UserService开启分片单线程的方法，然后清空整个数据库
在这里插入图片描述
在这里插入图片描述

重启调用接口，总耗时79秒
在这里插入图片描述
在这里插入图片描述

3.多线程

我们同样在UserService开启多线程的方法，然后清空整个数据库，重启调用接口，总耗时39秒，数据插入也正常
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
速度相对于单线程来说差不多快了一倍的速度

每天吃八顿
关注
专栏目录
【二十五】springboot使用EasyExcel和线程池实现多线程导入Excel数据
weixin_56995925的博客
07-23 1万+
springboot使用EasyExcel和线程池实现多线程导入Excel数据
SpringBoot实现Excel导入导出，好用到爆，POI可以扔掉了
m0_64867839的博客
12-16 623
@ApiOperation(value = “导出会员列表Excel”) @RequestMapping(value = “/exportMemberList”, method = RequestMethod.GET) public void exportMemberList(ModelMap map, HttpServletRequest request, HttpServletResponse response) { List memberList = LocalJsonUtil.getListFrom
百万Excel数据导入如何设计？
最新发布
qq_30939943的博客
09-05 1012
百万Excel数据导入如何设计？
【Excel】使用 SpringBoot 实现 Excel 文件的导入与导出
热门推荐
sco5282的博客
11-14 2万+
为了大家能更好地理解这篇博文，在看之前，建议去看看我的上一篇博文：【File】使用 SpringBoot 实现文件的上传与下载 这两篇博文互有联系：文件的导入就是文件的上传；文件的导出就是文件的下载。 至于为什么是 导入/导出 Excel 文件呢？因为 Excel 文件用的频率更多吧。顺便来复习下通过 EasyExcel 来操作 Excel 文件。 ...
springboot导入excel表格
07-12 2599
1、入口： @Log(title = "员工线下培训记录导入", businessType = BusinessType.IMPORT) @PostMapping("/importData") public AjaxResult importData(MultipartFile file) throws Exception { //获取文件名： String filename= file.getOriginalFilename(); if(file
springboot导入Excel数据
royal1235的博客
05-08 5367
1.导入Excel一般都会给一个Excel数据模板，模板下载请看本博客另一篇模板下载文章 2.数据库数据导入效果图 3.前台简陋图 4,需要引入mybatis、mysql、poi、io等依赖 <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactI...
spring boot中，excel表格内容导入
qq_63178344的博客
08-15 496
先将表格导入，如果文件不是xlsx或者xls则直接结束，file将为空值，后续会判断打印"请上传正确格式文件！"，格式正确则继续操作。方法是获取每行的列数，i=1则跳过了标题行0，然后将表中对应列名通过set方法分配到，设定好的实体对象中，方便处理进入数据库。方法，传入读取的数据列表，进行数据解析和导入。转换如果发生异常则打印，全部没问题则返回成功。接口调用，post方法。
Springboot上传excel并将表格数据导入或更新mySql数据库的过程
08-27
Spring Boot 上传 Excel 并将表格数据导入或更新 MySQL 数据库的过程 Spring Boot 上传 Excel 并将表格数据导入或更新 MySQL 数据库的过程是指使用 Spring Boot 框架，通过上传 Excel 文件，并将其中的表格数据导入...
springboot实现excel文件导入导出mysql
qq_44364813的博客
09-26 895
springboot编写excel文件导入导出mysql:全部流程
springboot上传excel导入到数据库完整demo(后端代码)
06-08
可以使用SpringBoot的`@SpringBootTest`注解和JUnit进行单元测试，模拟HTTP请求，验证Excel导入功能是否按预期工作。 6. **项目结构**： - 一个标准的SpringBoot项目通常包括`src/main/java`下的`main`和`test`...
SpringBoot开发接口实现excel导入数据并存储在数据库中
04-27
相关博客：https://blog.csdn.net/m0_51197424/article/details/124454553 开发技术：SpringBoot MyBatis MySQL 实现内容：开发接口实现导入Excel数据后，将数据存储到数据库中
springboot实现将Excel导入数据库
04-12
springboot实现将Excel导入数据库。springboot+mybatis实现，简单易懂。
springboot导入Excel文件并入库
10-16
springboot开的excel导入数据，并把数据插入到数据库操作
springboot实现Excel文件导入与导出功能
12-19
springboot实现Excel文件导入与导出功能, 本次任务主要实现两个功能，一是如何将Excel文件导入后台服务器，解析文件中的内容，并且存入数据库中。二是如何将后台数据库中的数据导出为Excel表格。经过调研，实现此功能常使用的是POI技术。代码是在以前的项目上修改的，注释掉的内容请忽略。
SpringBoot使用poi实现Excel导入与导出
03-04
poi版本3.17 这是一个针对于Excel进行操作的工具类，文档可生成 xsl与xlsx为后缀的Excel
SpringBoot使用EasyPoi将Mysql数据库中的数据导入到Excel文件中并把Excel文件中的数据导入到Mysql数据库中
12-21
在本文中，我们将探讨如何使用SpringBoot集成EasyPoi库，实现从MySQL数据库中导出数据到Excel文件，以及从Excel文件中导入数据到MySQL数据库的功能。首先，我们需要理解EasyPoi是一个Java处理Excel的开源工具，它...
Springboot excel导入功能
tracy9171的博客
03-29 832
Springboot excel导入功能
springboot实现将excel数据导入数据库
m0_47883844的博客
06-02 1918
直接就能用
SpringBoot上传Excel并导入MySQL数据库的安全配置实践
"这篇文档主要介绍了如何在SpringBoot应用中实现Excel文件的上传，并将表格数据导入或更新MySQL数据库，同时结合Swagger来设置多种安全配置。文档内容涉及到OpenAPI规范的使用，包括安全定义和全局安全配置，以及...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

每天吃八顿

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值