easypoi 结合mybatis-plus导入大批量数据

背景：spring boot+mybatis-plus项目需要通过接口导入50W数据。

excel解析，通过easypoi进行实体类映射。

Excel解析实体类：

@Data
public class ForecastUserImpExcel implements Serializable {
    private Long id;

    @Excel(name = "序号")
    private String sequenceNum;

    @Excel(name = "零售用户名称")
    private String clientName;

    @Excel(name = "户号")
    private String clientCode;

    @Excel(name = "日期",format = "yyyy-MM-dd")
    private Date generateDate;

    @Excel(name = "电能表id")
    private String electricityMeter;

    @Excel(name = "电能表冻结数值")
    private BigDecimal electricityVal;

    @Excel(name = "倍率")
    private BigDecimal times;

    @Excel(name = "是否参考表")
    private String isReference;

    private String year;

    private String month;

}

entity代码：

package com.siact.module.forecast.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;

import lombok.Data;
import lombok.EqualsAndHashCode;

import java.math.BigDecimal;
import java.util.Date;

/**
 * 预测用户导入数据
 *
 * @author xiela xiela@163.com
 * @since v1.0.0 2021-01-06
 */
@Data
@EqualsAndHashCode(callSuper=false)
@TableName("forecast_user_imp")
public class ForecastUserImpEntity {
	private static final long serialVersionUID = 1L;

    /**
     * id
     */
	private Long id;
    /**
     * 序号
     */
	private String sequenceNum;
    /**
     * 零售用户名称
     */
	private String clientName;
    /**
     * 户号
     */
	private String clientCode;
    /**
     * 日期
     */
	private Date generateDate;
    /**
     * 电表id
     */
	private String electricityMeter;
    /**
     * 电能表冻结数值
     */
	private BigDecimal electricityVal;
    /**
     * 倍数
     */
	private BigDecimal times;
    /**
     * 是否参考表
     */
	private String isReference;
    /**
     * 年度
     */
	private String year;
    /**
     * 月份
     */
	private String month;
}

controller代码：

   @Autowired
    private ForecastUserImpService forecastUserImpService;

    @PostMapping("import")
    public Result importExcel(@RequestParam String year, @RequestParam String month, MultipartFile file) {
        return forecastUserImpService.importExcel(year, month, file);
    }

service代码：

/**
  * forecast_user_imp 模板表名称，根据年度和月份动态拼接表名
  */
public Result importExcel(String year, String month, MultipartFile file) {
        //动态创建表
        baseDao.createTable("forecast_user_imp_" + year + "_" + month);
        long start = System.currentTimeMillis();
        try {
            ExcelUtils.importExcelBySax(file.getInputStream(), 0, 1,
            ForecastUserImpExcel.class,
            new IReadHandler<ForecastUserImpExcel> (){
                List<ForecastUserImpExcel> excelDtos = new ArrayList<>();
                @Override
                public void handler(ForecastUserImpExcel o) {
                    o.setYear(year);
                    o.setMonth(month);
                    excelDtos.add(o);
                    if(excelDtos.size() == 10000){
                        List<ForecastUserImpExcel> userImpExcels = deepCopy(excelDtos);
                        Thread thread = new Thread(() -> {
                            List<ForecastUserImpEntity> userImpDTOS = ConvertUtils.sourceToTarget(userImpExcels, ForecastUserImpEntity.class);
                            baseDao.insertCustomerMachineByBatch(userImpDTOS,"forecast_user_imp_" + year + "_" + month);
                        });
                        thread.start();
                        excelDtos = new ArrayList<>();
                    }
                }
                @Override
                public void doAfterAll() {
                    if(excelDtos.size() > 0){
                        List<ForecastUserImpExcel> userImpExcels = deepCopy(excelDtos);
                        Thread thread = new Thread(() -> {
                            List<ForecastUserImpEntity> userImpDTOS = ConvertUtils.sourceToTarget(userImpExcels, ForecastUserImpEntity.class);
                            baseDao.insertCustomerMachineByBatch(userImpDTOS,"forecast_user_imp_" + year + "_" + month);
                        });
                        thread.start();
                        excelDtos = new ArrayList<>();
                    }
                }
            });

        } catch (IOException e) {
            return new Result<>().error("导入错误！");
        }
        return new Result<>();
    }


    /**
     * list 深拷贝
     * @param srcList
     * @param <T>
     * @return
     */
    public static <T> List<T> deepCopy(List<T> srcList){
        ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
        try {
            ObjectOutputStream out = new ObjectOutputStream(byteOut);
            out.writeObject(srcList);
            ByteArrayInputStream byteIn = new ByteArrayInputStream(byteOut.toByteArray());
            ObjectInputStream inStream = new ObjectInputStream(byteIn);
            List<T> destList = (List<T>) inStream.readObject();
            return destList;
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

dao代码：

    void createTable(String tableName);

    int insertCustomerMachineByBatch(@Param("list") List<ForecastUserImpEntity> list,@Param("tableName") String tableName);

 

XML代码：

    <update id="createTable">
        create table if not Exists ${tableName} like forecast_user_imp
    </update>


    <insert id="insertCustomerMachineByBatch">
        insert into ${tableName}
            (`sequence_num`,
             `client_name`,
            `client_code`,
            `generate_date`,
            `electricity_meter`,
            `electricity_val`,
            `times`,
            `is_reference`,
            `year`,
            `month`)
        values
        <foreach collection="list" separator="," item="i">
            (
            #{i.sequenceNum},
            #{i.clientName},
            #{i.clientCode},
            #{i.generateDate},
            #{i.electricityMeter},
            #{i.electricityVal},
            #{i.times},
            #{i.isReference},
            #{i.year},
            #{i.month}
            )
        </foreach>
    </insert>

注意事项：

1.只支持xlsx格式的excel;

2.需要引入SAX的解析包;       
         <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.9.0</version>
        </dependency>
3.实体类的fixedIndex注解不能使用