原 easyexcel（四）：事务处理

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1259

备注：我们使用easyexcel读取数据的时候可能需要插入多张表，如何保证他们在同一个事务里面了。很简单，将逻辑代码全部写在我事务控制的service里面即可。

controller 代码如下：

package com.mszl.controller;

import java.text.ParseException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.mszl.entity.vo.InsertDataVO;
import com.mszl.service.DataDetailJmlService;
import com.mszl.utils.ReturnMsgUtils;

@RestController
@RequestMapping("/dataImport")
public class DataController {
	
    @Autowired
    private DataDetailJmlService dataDetailService;
	
    @PostMapping("/insertData")
    public ReturnMsgUtils insertData(@RequestBody InsertDataVO iv) throws ParseException {
        ReturnMsgUtils result=dataDetailService.readJmlData(iv);
        return result;
    }

}

IService 代码如下：

package com.mszl.service;
import com.mszl.entity.DataDetailJml;
import com.baomidou.mybatisplus.extension.service.IService;
import com.mszl.entity.vo.InsertDataVO;
import com.mszl.utils.ReturnMsgUtils;
public interface DataDetailJmlService extends IService<DataDetailJml> {

    ReturnMsgUtils readJmlData(InsertDataVO iv);

}

ServiceImpl 代码如下，注意这里有@Transactional ：

package com.mszl.service.impl;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.mszl.dao.*;
import com.mszl.entity.*;
import com.mszl.entity.vo.*;
import com.mszl.exception.BusinessException;
import com.mszl.service.DataDetailJmlService;
import com.mszl.utils.*;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.text.ParseException;
import java.time.LocalDateTime;
import java.util.*;

@Transactional
@Service
public class DataDetailJmlServiceImpl extends ServiceImpl<DataDetailJmlMapper, DataDetailJml> implements DataDetailJmlService {

    @Override
    public ReturnMsgUtils readJmlData(InsertDataVO iv) {
        InsertJmlDataListener dataListener=new InsertJmlDataListener(this, dictionaryService, iv);
        if(StringUtils.isNotBlank(iv.getPassWord())){
            EasyExcel.read(iv.getFileUrl(), ImportDataJmlVO.class, dataListener).password(iv.getPassWord()).sheet().doRead();
        } else{
            EasyExcel.read(iv.getFileUrl(), ImportDataJmlVO.class, dataListener).sheet().doRead();
        }
        ReturnMsgUtils result=dataListener.getMsg();
        return result;
    }


}

将你要在同一个事务里面处理的业务全部放在DataDetailJmlServiceImpl 里面处理。

监听类，代码如下：

package com.mszl.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisStopException;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.mszl.entity.vo.ImportDataJmlVO;
import com.mszl.entity.vo.ImportErrorInfoVO;
import com.mszl.entity.vo.InsertDataVO;
import com.mszl.service.impl.DataDetailJmlServiceImpl;
import com.mszl.service.impl.DictionaryServiceImpl;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
public class InsertJmlDataListener extends AnalysisEventListener<ImportDataJmlVO> {

    private ReturnMsgUtils msg;

    public ReturnMsgUtils getMsg() {
        return msg;
    }

    private static final int BATCH_COUNT = 5000;

    List<ImportDataJmlVO> list = new ArrayList<ImportDataJmlVO>();

    List<ImportErrorInfoVO> integerList=new ArrayList<ImportErrorInfoVO>();
    List<ImportErrorInfoVO> errorInfoList=new ArrayList<ImportErrorInfoVO>();

    private InsertDataVO iv;
    private DataDetailJmlServiceImpl dataDetailJmlServiceImpl;
    private DictionaryServiceImpl dictionaryService;

    public InsertJmlDataListener(DataDetailJmlServiceImpl dataDetailJmlServiceImpl, DictionaryServiceImpl dictionaryService, InsertDataVO iv) {
        this.dataDetailJmlServiceImpl = dataDetailJmlServiceImpl;
        this.dictionaryService = dictionaryService;
        this.iv = iv;
    }

    // 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
        throw exception;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        log.info("解析到一条头数据:{}", JSON.toJSONString(headMap));
    }

    @Override
    public void invoke(ImportDataJmlVO data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data, SerializerFeature.WriteMapNullValue, SerializerFeature.DisableCircularReferenceDetect));

        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            // 非数值提示
            if(integerList.size()>0){
                msg=new ReturnMsgUtils(BusinessUtils.CODE_201, BusinessUtils.JML_MSG_1007, integerList);
                throw new ExcelAnalysisStopException();
            }

            // 必填项提示
            if(errorInfoList.size()>0){
                msg=new ReturnMsgUtils(BusinessUtils.CODE_203, BusinessUtils.JML_MSG_1002, errorInfoList);
                throw new ExcelAnalysisStopException();
            }

            saveData();
            list.clear();
        }
    }

    // 这里也要保存数据，确保最后遗留的数据也存储到数据库
    @SneakyThrows
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 非数值提示[当BATCH_COUNT大于导入excel中的数据的时候easyexcel不执行invoke的方法，直接执行doAfterAllAnalysed方法]
        if(integerList.size()>0){
            msg=new ReturnMsgUtils(BusinessUtils.CODE_201, BusinessUtils.JML_MSG_1007, integerList);
            throw new ExcelAnalysisStopException();
        }

        // 必填项提示
        if(errorInfoList.size()>0){
            msg=new ReturnMsgUtils(BusinessUtils.CODE_203, BusinessUtils.JML_MSG_1002, errorInfoList);
            throw new ExcelAnalysisStopException();
        }

        dataDetailJmlServiceImpl.insertCompeteDataImport(iv); // 新增导入记录表
        saveData();
        log.info("所有数据解析完成！");
    }

    // 存储数据库
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        msg=dataDetailJmlServiceImpl.insertJmlData(list, iv);
        log.info("存储数据库成功！");
    }


}