一、程序需求
从web页面上导入一个excel文件，此文件中有数万条以上的关于商户的交易记录数据；
1、一次性导入到后台后需要存一个总的导入记录，以及导入数据的记录明细，导入记录与导入数据记录为一对多的关系；
2、记录明细内容中还需要根据excel的行数据计算出商户交易的积分信息；
3、如果商户表中没有导入的商户，则插入商户表数据。
4、如果客户表中没有导入的客户，则插入客户表数据。
5、每月导入一次，每次导入数据量很多，我们需要提高导入的效率。
二、使用技术
前端Jeecgboot vue3，后端框架使用jeecgboot
导入记录表：Trade
导入数据记录表：TradeRecord
三、前端代码
导入的Modal页面：
<template>
<BasicModal v-bind="$attrs" @register="registerModal" destroyOnClose :title="title" :width="800" @ok="handleSubmit">
<div style="margin: 0 35px 15px" >
<span style="display: inline-block; height: 32px; line-height: 32px; vertical-align: middle">选择月份:</span>
<span style="margin-left: 6px">
<a-month-picker :format="monthFormat" v-model="defalutMonth" @panelChange="handleChangeMonth"></a-month-picker>
</span>
</div>
<!--上传-->
<div style="margin: 0 35px 15px" >
<span style="display: inline-block; height: 32px; line-height: 32px; vertical-align: middle">导入文件:</span>
<span style="margin-left: 6px">
<a-upload name="file" accept=".xls,.xlsx" :multiple="false" :fileList="fileList" @remove="handleRemove" :beforeUpload="beforeUpload">
<a-button preIcon="ant-design:upload-outlined">选择导入文件</a-button>
</a-upload>
</span>
</div>
<!--页脚-->
<template #footer>
<a-button @click="handleClose">关闭</a-button>
<a-button type="primary" @click="handleImport" :disabled="uploadDisabled" :loading="uploading">{{
uploading ? '上传中...' : '开始上传'
}}
</a-button>
</template>
</BasicModal>
</template>
这里提交的内容有：月份、excel文件，点击“开始上传”按钮后执行handleImport方法，方法内容如下：
//表单提交事件
//文件上传
function handleImport() {
let {biz, online} = props;
const formData = new FormData();
if (biz) {
formData.append('isSingleTableImport', biz);
}
if (unref(monthValue)) {
formData.append("month", unref(monthValue));
}else{
createMessage.error("请选择月份！")
return;
}
unref(fileList).forEach((file) => {
formData.append('files', file);
});
uploading.value = true;
// 请求怎样处理的问题
let headers = {
'Content-Type': 'multipart/form-data;boundary = ' + new Date().getTime(),
};
defHttp.post({url: "/business/trade/importExcelData", timeout: 60 * 60 * 1000, params: formData, headers}, {isTransformResponse: false}).then((res) => {
uploading.value = false;
if (res.success) {
if (res.code == 201) {
errorTip(res.message, res.result);
} else {
createMessage.success(res.message);
}
handleClose();
reset();
emit('success');
} else {
createMessage.warning(res.message);
}
}).catch(() => {
uploading.value = false;
});
}
这里就已经提交到了后台了
四、后端代码
首先引入依赖文件
<!--Excel 操作-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.0-beta4</version>
</dependency>
后台执行的Controller方法：
1、controller方法：获取到MultipartFile文件
/**
* 通过excel导入数据
* @return
*/
@RequestMapping(value = "/importExcelData", method = RequestMethod.POST)
public Result<?> importExcelData(MultipartFile[] files, String month) {
System.out.println(month);
// 获取上传文件对象
try {
MultipartFile multipartFile = files[0];
//查询选择是否有导入过数据
Trade dup = tradeService.getOne(new LambdaQueryWrapper<Trade>().eq(
Trade::getMonth, month).eq(Trade::getIzValid,PointStoreConst.NUM_1).last("limit 1"));
if(Optional.ofNullable(dup).isPresent()){
return Result.error("本月已导入过数据！");
}else{
//MultipartFile file = multipartRequest.getFile("file");
String id = tradeService.doFileImport(month,multipartFile);
return Result.OK("文件导入成功！");
}
} catch (Exception e) {
e.printStackTrace();
return Result.error("文件导入失败:" + e.getMessage());
}
}
2、service类：调用EasyExcel.read方法
String doFileImport( String month,MultipartFile file) throws Exception;
@Override
@Transactional(rollbackFor = Exception.class)
public String doFileImport(String month,MultipartFile file) throws Exception {
log.info(" --- 执行开始，时间："+ DateUtils.now()+"---");
String code = PointStoreConst.SH + TimeUtil.getTimestampStr(TimeUtil.STR_yyyyMMddHHmmss);
Trade trade = new Trade();
trade.setCode(code);
trade.setIzValid(PointStoreConst.NUM_0.toString());
trade.setMonth(month);
tradeMapper.insert(trade);
StringBuilder errMsg = new StringBuilder();
//获取计算积分的规则
BusiRules rules = busiRulesService.getOne(new LambdaQueryWrapper<BusiRules>().isNotNull(BusiRules::getId).last("limit 1"));
//部门信息
List<SysDepart> departs = departService.list();
EasyExcel.read(file.getInputStream(), TradeRecord.class, new TradeExcelListener(recordService, customerService,errMsg, rules, trade.getId(),trade.getMonth(), departs)).sheet().doRead();
if (errMsg.length() > 0) {
throw new Exception(errMsg.toString());
}
log.info(" --- 执行完毕，时间："+ DateUtils.now()+"---");
return trade.getId();
}
3、在TradeExcelListener中进行解析以及处理
@Slf4j
public class TradeExcelListener extends AnalysisEventListener<TradeRecord> {
/**
* 行计数器
*/
private AtomicInteger lineCounter = new AtomicInteger(1);
/**
* 错误信息
*/
private StringBuilder errMsg;
/**
* 主表id
*/
private String tradeId;
/**
* 主表月份
*/
private String month;
@Resource
private ITradeRecordService recordServiceService;
@Resource
private ICusCustomerService customerService;
/**
* 导入的数据列表
*/
private List<TradeRecord> recordList = new ArrayList<>();
/**
* 导入的客户列表
*/
private List<BusMerchant> merchantList = new ArrayList<>();
/**
* 换算业务规则
*/
private BusiRules rule;
/**
* 部门列表
*/
private List<SysDepart> departs;
/**
* 构造器
*/
public TradeExcelListener(ITradeRecordService tradeService, ICusCustomerService customerService, StringBuilder errMsg, BusiRules rule, String tradeId,String month, List<SysDepart> departs) {
this.recordServiceService = tradeService;
this.customerService = customerService;
this.errMsg = errMsg;
this.tradeId = tradeId;
this.month = month;
this.rule = rule;
this.departs = departs;
}
/**
* 获取表头
*
* @param dataMap
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> dataMap, AnalysisContext context) {
if (dataMap.size() != 11) {
errMsg.append("格式错误！请下载模板再重试！");
return;
}
if(!"网点".equals(dataMap.get(0))){
errMsg.append("格式错误！请下载模板再重试！");
return;
}
}
@Override
public void invoke(TradeRecord record, AnalysisContext context) {
//log.info("解析到一条数据:{}", recordImpVo.toString());
// 不为空说明有错误 不继续执行
if (errMsg.length() > 0) {
return;
}
try {
record.setTradeId(tradeId);
record.setMonth(month);
record.setMoneyNum(ObjUtil.isEmpty(record.getMoneyNum()) ? new Double(0) : record.getMoneyNum());
record.setTradeNum(ObjUtil.isEmpty(record.getTradeNum()) ? new Integer(0) : record.getTradeNum());
record.setTradeDays(ObjUtil.isEmpty(record.getTradeDays()) ? new Integer(0) : record.getTradeDays());
record.setMonthAvg(ObjUtil.isEmpty(record.getMonthAvg()) ? new Double(0) : record.getMonthAvg());
//设置积分
switch (rule.getBusiRules()) {
//积分的各种处理方法
}
//总和
record.setIntegralSum(record.getMoneyNumInte() + record.getTradeNumInte() + record.getTradeDaysInte() + record.getMonthAvgInte());
//数据加密
record.setMerchCode(AESUtil.encrypt(record.getMerchCode(), AESUtil.KEY.getBytes()));
//商户列表
BusMerchant merchant= new BusMerchant();
merchant.setMerchCode(record.getMerchCode()).setMerchAbbr(record.getMerchAbbr()).setConncatPhone(record.getConncatPhone());
merchantList.add(merchant);
} catch (Exception e) {
log.error("积分转换失败：" + e.getMessage());
errMsg.append("积分转换失败：" + e.getMessage());
e.printStackTrace();
return;
}
//设置部门id
SysDepart dep = departs.stream().filter(d -> d.getOrgCode().equals(record.getOrgCode())).findFirst().orElse(null);
if (Optional.ofNullable(dep).isPresent()) {
record.setDepartId(dep.getId());
} else {
errMsg.append(lineCounter.get()+"行数据：部门编号不存在：" + record.getOrgCode() + "\n");
}
recordList.add(record);
lineCounter.set(lineCounter.get() + 1);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (errMsg.length() > 0) {
return;
}
//执行存入数据方法
try {
recordServiceService.saveRecords(recordList);
} catch (InterruptedException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
4、CountDownLatch处理列表数据后使用@Async注解方式保存
/**
* 处理列表数据，并保存
* @param recordList
*/
void saveRecords(List<TradeRecord> recordList) throws InterruptedException, SQLException;
/**
* 导入时，只是增加商户数据记录,以及客户表信息、商户表信息
*
* @param recordList
* @throws InterruptedException
* @throws SQLException
*/
@Override
@Transactional(rollbackFor = Exception.class)
public void saveRecords(List<TradeRecord> recordList) throws InterruptedException, SQLException {
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
List<TradeRecord> recordsAdd = new ArrayList<>();
//客户
List<CusCustomer> addCustomers = new ArrayList<>();
//商户
List<BusMerchant> addMerchantss = new ArrayList<>();
//查询导入数据在客户表中的数据
List<String> recordPhones = recordList.stream().distinct().map(TradeRecord::getConncatPhone).collect(Collectors.toList());
recordPhones = recordPhones.stream().map(phone -> {
try {
return AESUtil.encrypt(phone, AESUtil.KEY.getBytes());
} catch (Exception e) {
throw new RuntimeException(e);
}
}).collect(Collectors.toList());
List<CusCustomer> customerList = customerService.list(new LambdaQueryWrapper<CusCustomer>().in(CusCustomer::getPhone, recordPhones));
/**获取需要插入的商户编号集合*/
List<String> addMerCodes = recordList.stream().distinct().map(TradeRecord::getMerchCode).collect(Collectors.toList());
List<String> existCodeList = busMerchantService.list().stream().distinct().map(BusMerchant::getMerchCode).collect(Collectors.toList());
//从导入List中移除已存在的商户List中包含的元素
if(CollUtil.isNotEmpty(existCodeList)){
addMerCodes.removeAll(existCodeList);
}
/**循环获取需要添加的客户信息、记录内容、商户信息*/
CountDownLatch downLatch = new CountDownLatch(recordList.size());
ExecutorService executorService = Executors.newFixedThreadPool(10);
recordList.stream().forEach(record -> {
try {
executorService.submit(() -> {
try {
//处理内容
} catch (Exception e) {
throw new RuntimeException(e);
}
downLatch.countDown();
});
} catch (Exception e) {
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
throw new RuntimeException(e);
}
});
executorService.shutdown();
downLatch.await();
//批量存入新客户
if (CollUtil.isNotEmpty(addCustomers)) {
customerService.saveCustomers(addCustomers);
customerList.addAll(addCustomers);
}
//导入数据列表中客户id设置
//保存记录
if (CollUtil.isNotEmpty(recordsAdd)) {
Date start = DateUtils.getDate();
//每1000条数据插入开一个线程
List<List<TradeRecord>> lists = groupList(recordsAdd, 1000);
CountDownLatch countDownLatch = new CountDownLatch(lists.size());
for (List<TradeRecord> listSub : lists) {
executeAsync(listSub, countDownLatch); //分批次执行数据
}
try {
countDownLatch.await(); //保证之前的所有的线程都执行完成，才会走下面的； 这样就可以在下面拿到所有线程执行完的集合结果
} catch (Exception e) {
e.printStackTrace();
log.error("阻塞异常:" + e.getMessage());
}
Date end = DateUtils.getDate();
System.out.println("插入记录消耗时间" + DateUtils.getDiffSeconds(end, start));
}
/**商户数据:存入不存在的商户数据*/
if (CollUtil.isNotEmpty(addMerchantss)) {
try {
addMerchantss.stream().distinct().collect(Collectors.toList());//去重
busMerchantService.saveMerchants(addMerchantss);//这里的插入要在mybatis.xml文件中使用foreach一次性插入，不要调用saveBatch()方法
} catch (Exception e) {
e.printStackTrace();
log.error("存入商户数据失败：" + e.getMessage());
}
}
}
数据分组：
/**
* 根据每个线程执行的数量拆分成多个数组
*
* @param list 所有数据
* @param size 拆分每个批次数量
*/
public List<List<TradeRecord>> groupList(List<TradeRecord> list, int size) {
List<List<TradeRecord>> resList = new ArrayList<>();
int totalBatches = (int) Math.ceil((double) list.size() / size);
for (int i = 0; i < totalBatches; i++) {
int startIndex = i * size;
int endIndex = Math.min((i + 1) * size, list.size());
List<TradeRecord> batch = list.subList(startIndex, endIndex);
resList.add(batch);
}
return resList;
}
executeAsync方法：
/**
* 开启多线程
*
* @param list 每个批次的数据
* @param countDownLatch Java 中的一个并发工具类，用于协调多个线程之间的同步
*/
@Async("asyncServiceExecutor")
public void executeAsync(List<TradeRecord> list, CountDownLatch countDownLatch) {
try {
log.warn("start executeAsync");
//异步线程要做的事情 批量新增插入数据库
tradeRecordMapper.saveRecords(list);
log.warn("end executeAsync");
} finally {
countDownLatch.countDown();// 很关键, 无论上面程序是否异常必须执行countDown,否则await无法释放
}
}
其中的asyncServiceExecutor注解为自定义一个异步线程池，使用@EnableAsync注解
5、自定义异步线程池@EnableAsync
@Configuration
@EnableAsync
@Slf4j
public class ExecutorConfig {
@Value("${async.executor.thread.core_pool_size}")
private int corePoolSize;
@Value("${async.executor.thread.max_pool_size}")
private int maxPoolSize;
@Value("${async.executor.thread.queue_capacity}")
private int queueCapacity;
@Value("${async.executor.thread.name.prefix}")
private String namePrefix;
@Bean(name = "asyncServiceExecutor")
public Executor asyncServiceExecutor() {
log.warn("start asyncServiceExecutor");
//在这里修改
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
//配置核心线程数
executor.setCorePoolSize(corePoolSize);
//配置最大线程数
executor.setMaxPoolSize(maxPoolSize);
//配置队列大小
executor.setQueueCapacity(queueCapacity);
//配置线程池中的线程的名称前缀
executor.setThreadNamePrefix(namePrefix);
// rejection-policy：当pool已经达到max size的时候，如何处理新任务
// CALLER_RUNS：不在新线程中执行任务，而是有调用者所在的线程来执行
executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
//执行初始化
executor.initialize();
return executor;
}
}
总结
1、不要使用Jeecgboot自动生成的poi.excel的ExcelImportUtil.importExcel()方法，可使用EasyExcel.read()，自定义继承AnalysisEventListener的监听方法来解析数据，方便数据预处理；
2、获取到导入数据列表后，进行再次数据处理，与其他模块功能联动时，使用线程，以及自定义异步线程池的方式提高效率；
3、插入到数据库的时候，不要直接调用mybatis plus的多次单条插入或者修改方法，使用mybatis中的foreach循环数据来插入，减少数据库连接压力；
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
