java 操作大数据量的Excel

最新推荐文章于 2024-08-16 17:32:00 发布

ZNineSun

最新推荐文章于 2024-08-16 17:32:00 发布

阅读量2k

点赞数

分类专栏： # springBoot 文章标签： java excel EasyExcel springBoot 批量插入

本文链接：https://blog.csdn.net/zhiyikeji/article/details/118387846

版权

springBoot 专栏收录该内容

75 篇文章 18 订阅

订阅专栏

1.需求分析

对于百万级数据量的数据从excel中读取和写入到数据库，主要耗费时间的地方就在读取excel，解析文件信息，以及频繁的和数据库建立连接所产生的耗时，所以我们从以下几个方向去解决：
EasyExcel + 缓存数据库查询操作 + 批量插入

EasyExcel用于解决对于excel的操作所花费的时间，下面是开发人员对其的评价：
缓存数据库查询操作
以空间换时间
逐行查询数据库校验的时间成本主要在来回的网络IO中，优化方法也很简单。将参加校验的数据全部缓存到HashMap 中。直接到 HashMap 去命中。
批量插入
MySQL insert 语句支持使用 values (),(),() 的方式一次插入多行数据，通过 mybatis foreach 结合 java 集合可以实现批量插入
结合网上一些对 innodb_buffer_pool_size 描述我猜是因为过长的 SQL 在写操作的时候由于超过内存阈值，发生了磁盘交换。限制了速度，另外测试服务器的数据库性能也不怎么样，过多的插入他也处理不过来。所以最终采用每次 1000 条插入。
每次 1000 条插入后，为了榨干数据库的 CPU，那么网络IO的等待时间就需要利用起来，这个需要多线程来解决，而最简单的多线程可以使用并行流来实现

下面我们开始进行具体的实现

2.EasyExcel的引入

EasyExcel开发手册

2.1 添加maven依赖

 <!-- https://mvnrepository.com/artifact/apache-xerces/xercesImpl -->
        <dependency>
            <groupId>apache-xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.9.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>

我们excel表的样式如下所示：
在这里插入图片描述

那么我们建立起与之对应的实体类

public class DateDetailInfo extends Model {
    @ExcelProperty("id")
    private Integer originId;
    @ExcelProperty("gateway_id")
    private String gatewayId;
    @ExcelProperty("ear_tag_id")
    private String earTagId;
    @ExcelProperty("temp")
    private String temp;
    @ExcelProperty("step_count")
    private Integer stepCount;
    @ExcelProperty("battery_power")
    private Integer batteryPower;
    @ExcelProperty("master_signal")
    private Integer masterSignal;
    @ExcelProperty("slave_signal")
    private Integer slaveSignal;
    @ExcelProperty("version")
    private String version;
    @ExcelProperty("upload_time")
    private Date uploadTime;
    @ExcelProperty("update_time")
    private Date updateTime;
    @ExcelProperty("create_time")
    private Date createTime;


}

其中ExcelProperty对应的就是我们的列名，更多详细的描述可以自行查看开发手册，由于文章篇幅限制，我把getter和setter方法删除了，自己可以自行添加进去

2.2 创建监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.huateng.pig.mapper.DateDetailInfoMapper;
import com.huateng.pig.po.DateDetailInfo;
import lombok.extern.slf4j.Slf4j;

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

@Slf4j
public class DateDetailInfoListerner extends AnalysisEventListener<DateDetailInfo> {
    private DateDetailInfoMapper dateDetailInfoMapper;
    /**
     * 每隔1000条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    List<DateDetailInfo> list = new ArrayList<DateDetailInfo>();

    public DateDetailInfoListerner(DateDetailInfoMapper dateDetailInfoMapper) {
        this.dateDetailInfoMapper = dateDetailInfoMapper;
    }

    @Override
    public void invoke(DateDetailInfo dateDetailInfo, AnalysisContext analysisContext) {

        list.add(dateDetailInfo);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        int saveNum = dateDetailInfoMapper.addBatches(list);
        log.info("成功存储" + saveNum + "数据库成功！");
    }

}

其中对应的DateDetailInfoMapper如下：

public interface DateDetailInfoMapper{
    int addBatches(List<DateDetailInfo> list);
}

对应的xml文件代码如下：

 <insert id="addBatches">
        INSERT INTO date_detail_info
        (origin_id,gateway_id, ear_tag_id, temp, step_count,battery_power,master_signal,slave_signal,version,
        upload_time, update_time, create_time)
        VALUES
        <foreach collection="list" item="dateDetailInfo" separator=",">
            (#{dateDetailInfo.originId}, #{dateDetailInfo.gatewayId}, #{dateDetailInfo.earTagId},#{dateDetailInfo.temp},
            #{dateDetailInfo.stepCount},
            #{dateDetailInfo.batteryPower},#{dateDetailInfo.masterSignal},
            #{dateDetailInfo.slaveSignal},#{dateDetailInfo.version}, #{dateDetailInfo.uploadTime},
            #{dateDetailInfo.updateTime},#{dateDetailInfo.createTime})
        </foreach>
    </insert>

具体使用：

ExcelReader excelReader = EasyExcel.read(new file("D:\ninesun\test.sls"), DateDetailInfo.class, new DateDetailInfoListerner(dateDetailInfoMapper)).build();
                ReadSheet readSheet = EasyExcel.readSheet(0).build();
                excelReader.read(readSheet);
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();