1.controller

package com.ccbft.seecloud.security.text.controller;

import com.ccbft.seecloud.common.core.util.R;
import com.ccbft.seecloud.security.text.config.ResourceConfig;
import com.ccbft.seecloud.security.text.entity.SecuritySensitiveWordEntity;
import com.ccbft.seecloud.security.text.entity.SecuritySensitiveWordStgEntity;
import com.ccbft.seecloud.security.text.service.WordStgService;
import com.ccbft.seecloud.security.text.vo.invo.AddWordInVo;
import com.ccbft.seecloud.security.text.vo.invo.DeleteWordInVo;
import com.ccbft.seecloud.security.text.vo.invo.EditWordInVo;
import com.ccbft.seecloud.security.text.vo.invo.GetWordListInVo;
import com.ccbft.seecloud.security.text.vo.outvo.AddWordOutVo;
import com.ccbft.seecloud.security.text.vo.outvo.DeleteWordOutVo;
import com.ccbft.seecloud.security.text.vo.outvo.EditWordOutVo;
import com.github.pagehelper.PageInfo;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
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.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.validation.Valid;

@Slf4j
@RestController
@Api(value = "敏感词详情", tags = {"敏感词详情"})
@RequestMapping("/WordInfo")
public class WordInfoController {

    @Autowired
    private ResourceConfig resourceConfig;

    @Autowired
    com.ccbft.seecloud.security.text.service.WordInfoService WordInfoService;

    @Autowired
    private WordStgService wordStgService;

    @PostMapping(value = "/importSensitiveWord", produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiOperation(value = "敏感词导入 - fengwei", tags = {"fengwei"}, notes = "选中词库导入敏感词")
    public R<String> importSensitiveWord(@ApiParam(name = "file", value = "文件", required = true)
                                                 MultipartFile file,
                                         @ApiParam(name = "stgId", value = "获取敏感词仓库ID", required = true)
                                         @RequestParam String stgId) {
        if (null == file) {
            return R.failed("error：没有找到文件，导入失败");
        }
        if (!file.getOriginalFilename().endsWith(".xlsx")) {
            return R.failed("error：文件格式不正确！");
        }
        int size = Integer.parseInt(resourceConfig.getXlsxMaxSize());
        if (file.getSize() > size) {
            return R.failed("error：文件过大，导入失败");
        }
        SecuritySensitiveWordStgEntity wordByPk = wordStgService.getWordByPk(stgId);
        if (null == wordByPk) {
            return R.failed("error：该敏感词库不存在");
        }
        int affectCount = WordInfoService.importSensitiveWord(file, stgId);
        if (affectCount <= 0) {
            return R.failed("error：所获敏感词为空（可能包含重复数据）或含有超出150长度的敏感词");
        }
        return R.ok("success：词库" + stgId + "：成功导入" + affectCount + "条");
    }
}

2.service

package com.ccbft.seecloud.security.text.service;

import cn.hutool.core.lang.UUID;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.ccbft.seecloud.security.text.config.ResourceConfig;
import com.ccbft.seecloud.security.text.enmus.AppInfoEnum;
import com.ccbft.seecloud.security.text.entity.SecuritySensitiveWordEntity;
import com.ccbft.seecloud.security.text.listener.SensitiveWordData;
import com.ccbft.seecloud.security.text.listener.SensitiveWordListener;
import com.ccbft.seecloud.security.text.mapper.SecuritySensitiveWordMapper;
import com.ccbft.seecloud.security.text.mapper.SecuritySensitiveWordStgMapper;
import com.ccbft.seecloud.security.text.utils.ThreadPoolUtil;
import com.ccbft.seecloud.security.text.vo.invo.AddWordInVo;
import com.ccbft.seecloud.security.text.vo.invo.DeleteWordInVo;
import com.ccbft.seecloud.security.text.vo.invo.EditWordInVo;
import com.ccbft.seecloud.security.text.vo.invo.GetWordListInVo;
import com.ccbft.seecloud.security.text.vo.outvo.AddWordOutVo;
import com.ccbft.seecloud.security.text.vo.outvo.DeleteWordOutVo;
import com.ccbft.seecloud.security.text.vo.outvo.EditWordOutVo;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import io.jsonwebtoken.lang.Collections;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Slf4j
@Service
public class WordInfoService {

    @Resource
    SecuritySensitiveWordMapper securitySensitiveWordMapper;
    @Autowired
    CommonService commonService;

    @Resource
    private SecuritySensitiveWordStgMapper wordStgMapper;

    @Autowired
    private ResourceConfig resourceConfig;

    private static Integer affectCount = 0;

    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;
    @Autowired
    private TransactionDefinition transactionDefinition;

    public int importSensitiveWord(MultipartFile file, String stgId) {
        affectCount = 0;
        FileOutputStream fileOutputStream = null;
        InputStream inputStream = null;
        ExcelReader excelReader = null;
        try {
            // 1.获取文件流
            String temDirPath = String.format("%s/%s", resourceConfig.getXlxsPath(), "upload_temp");

            String tempDir = temDirPath + File.separator + file.getOriginalFilename();
            File outFile = new File(tempDir);
            // 2.判断文件夹是否存在，不存在则创建
            if (null != outFile.getParentFile()) {
                outFile.getParentFile().mkdirs();
            }
            // 3.创建输出流，文件输出保存到目录
            fileOutputStream = new FileOutputStream(outFile);
            inputStream = file.getInputStream();
            IOUtils.copy(inputStream, fileOutputStream);

            // 4.读取xlxs，并创建监听器读取其中的sheet
            SensitiveWordListener sensitiveWordListener = new SensitiveWordListener(stgId, this,
                    dataSourceTransactionManager, transactionDefinition);
            excelReader = EasyExcel.read(outFile, SensitiveWordData.class, sensitiveWordListener).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build(); //忽略第一行
            excelReader.read(readSheet);

            return affectCount;
        } catch (IOException e) {
            log.error("WordInfoService importSensitiveWord：文件解析失败",e);
            return 0;
        } finally {
            if (null != fileOutputStream) {
                try {
                    fileOutputStream.flush();
                    fileOutputStream.close();
                } catch (Exception ex) {
                    log.error("WordInfoService importSensitiveWord：文件关闭失败",ex);
                    ex.printStackTrace();
                }
            }
            if (null != excelReader) {
                excelReader.finish();
            }
        }
    }

    public void batchInsert(String stgId, List<SensitiveWordData> list,
                            DataSourceTransactionManager dataSourceTransactionManager,
                            TransactionStatus transactionStatus) {
        if (Collections.isEmpty(list)) {
            return;
        }
        Timestamp t = new Timestamp(new Date().getTime());
        List<SecuritySensitiveWordEntity> entityList = new ArrayList<>();

        List<String> wordList = securitySensitiveWordMapper.selectAllWordByStgId(stgId);
        for (SensitiveWordData data : list) {
            String sensitiveWord = data.getSensitiveWord();
            if (StringUtils.isEmpty(sensitiveWord) || wordList.contains(sensitiveWord)) {
                continue;
            }
            // 敏感词和描述长度都不能超过50
            if (sensitiveWord.length() > 150) {
                dataSourceTransactionManager.rollback(transactionStatus);
                log.error("WordInfoService batchInsert：导入失败：敏感词长度不能超过150");
                affectCount = -1;
                return;
            }
            String describe = data.getDescribe();
            if (StringUtils.isNoneBlank(describe) && describe.length() > 150) {
                dataSourceTransactionManager.rollback(transactionStatus);
                log.error("WordInfoService batchInsert：导入失败：敏感词描述长度不能超过150");
                affectCount = -2;
                return;
            }
            SecuritySensitiveWordEntity entity = new SecuritySensitiveWordEntity();
            entity.setWord(sensitiveWord);
            entity.setDescribe(describe);
            entity.setId(UUID.randomUUID().toString().trim());
            entity.setWordStgId(stgId);
            entity.setCreateTime(t);

            entityList.add(entity);
        }
        if (Collections.isEmpty(entityList)) {
            return;
        }
        affectCount += securitySensitiveWordMapper.insertForeach(entityList);
    }

}

3.excel列所映射的实体

package com.ccbft.seecloud.security.text.listener;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class SensitiveWordData {
    @ExcelProperty(index = 0)
    private String sensitiveWord;

    @ExcelProperty(index = 1)
    private String describe;
}

4.excel解析监听

package com.ccbft.seecloud.security.text.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ccbft.seecloud.security.text.service.WordInfoService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

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

@Slf4j
public class SensitiveWordListener extends AnalysisEventListener<SensitiveWordData> {

    private static final int BATCH_COUNT = 500;
    private List<SensitiveWordData> list = new ArrayList<>();

    //事务管理
    private DataSourceTransactionManager dataSourceTransactionManager;
    private DefaultTransactionDefinition transactionDefinition;
    private TransactionStatus transactionStatus = null;

    private WordInfoService service;
    private String stgId;

    public SensitiveWordListener(String stgId,WordInfoService service,
                                 DataSourceTransactionManager dataSourceTransactionManager,
                                 TransactionDefinition transactionDefinition) {
        this.stgId = stgId;
        this.service = service;

        this.dataSourceTransactionManager = dataSourceTransactionManager;
        this.transactionDefinition = new DefaultTransactionDefinition(transactionDefinition);
        //设置事务的隔离级别 ：未提交读写
        this.transactionDefinition.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
        // 手动开启事务
        this.transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
    }

    @Override
    public void invoke(SensitiveWordData sensitiveWord, AnalysisContext analysisContext) {
        boolean hasCompleted = transactionStatus.isCompleted();
        // 如果事务已经关闭
        if (hasCompleted){
            return;
        }
        // 1.将解析好的数据存储到list中
        list.add(sensitiveWord);
        // 2.判断list大小是否达到BATCH_COUNT，如果达到则存储到数据库中
        if (list.size() >= BATCH_COUNT) {
            service.batchInsert(stgId ,list, dataSourceTransactionManager, transactionStatus);
            log.info("SensitiveWordListener service.batchInsert：success");
            list.clear();
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        boolean hasCompleted = transactionStatus.isCompleted();
        if (hasCompleted){
            return;
        }
        service.batchInsert(stgId ,list, dataSourceTransactionManager, transactionStatus);
        log.info("SensitiveWordListener service.batchInsert：success");
        hasCompleted = transactionStatus.isCompleted();
        if (!hasCompleted){
            //提交事务
            dataSourceTransactionManager.commit(transactionStatus);
            log.info("SensitiveWordListener doAfterAllAnalysed：当前事务已提交");

        }
    }

}