业务:
实现数据导入和查询功能.
问题:
数据库中数据为季度数据,一个项目会有0-4条数据,一年不定数量的季度满意度数据如何导入?如何按年查询?
导入:
导入的模板数据包含项目信息,年份,不定数量的季度数据,我需要将该年份的数据导入进数据库,所以这里foreach遍历easyExcel读取到的数据,每个对象先把四个季度的满意度信息提取出来,以<季度,满意度>格式放进map中.然后遍历这个map,根据项目信息和当前的key(季度)查询数据库判断该数据是否存在,存在就更新,不存在就新增.
至此导入功能完成,代码实现如下:
String fileName = file.getOriginalFilename();
Date date = new Date();
try {
//easyExcel导入表格数据
List<SatisfactionInfoOfYearRes> satisfactionOfYearList = EasyExcel.read(new BufferedInputStream(file.getInputStream()))
.head(SatisfactionInfoOfYearRes.class).sheet().doReadSync();
//遍历,按季度拆分数据,分别操作
satisfactionOfYearList.forEach(satisfactionInfoOfYearRes -> {
if (satisfactionInfoOfYearRes.getProjectName() == null || satisfactionInfoOfYearRes.getProjectId() == null || satisfactionInfoOfYearRes.getYear() == null) {
return;
}
MpSatisfactionInfoDO satisfactionInfoDO = new MpSatisfactionInfoDO();
BeanUtils.copyProperties(satisfactionInfoOfYearRes, satisfactionInfoDO);
Map<String,BigDecimal> quarter = new HashMap<>();
BigDecimal firstQuarter = satisfactionInfoOfYearRes.getFirstQuarter();
BigDecimal secondQuarter = satisfactionInfoOfYearRes.getSecondQuarter();
BigDecimal thirdQuarter = satisfactionInfoOfYearRes.getThirdQuarter();
BigDecimal fourthQuarter = satisfactionInfoOfYearRes.getFourthQuarter();
quarter.put("1",firstQuarter);
quarter.put("2",secondQuarter);
quarter.put("3",thirdQuarter);
quarter.put("4",fourthQuarter);
quarter.forEach((k,v)->{
if (v == null){
return;
}
satisfactionInfoDO.setQuarter(k);
satisfactionInfoDO.setSatisfaction(v);
//每次新增前判断是否存在该数据
String recordId = this.satisfactionInfoDao.selectRecordId(satisfactionInfoDO);
if (StringUtils.isBlank(recordId)){
//insert
satisfactionInfoDO.setRecordId(TinyUUIDGenerator.generate());
satisfactionInfoDao.insert(satisfactionInfoDO);
}else {
//update
satisfactionInfoDO.setRecordId(recordId);
this.satisfactionInfoDao.update(satisfactionInfoDO);
}
});
} catch (IOException e) {
throw new RuntimeException(e);
}
return BaseResponse.success("导入成功");
查询:
这里需要把数据库查到的季度数据合并为年度数据,难点在于如何分页,如何在不需要知道该年有几条数据的前提下把查到的数据合并.我的思路是根据项目和年份分组,再通过mysql的GROUP_CONCAT()函数合并数据
SELECT a.project\_id,a.project\_name,a.`quarter`,a.YEAR,a.satisfaction,a.create\_time,a.create\_by ,
\-- 合并季度数据
GROUP\_CONCAT(a.`quarter`) AS quarters,
\-- 合并满意度数据,如果为空则赋值0
GROUP\_CONCAT(IFNULL(a.satisfaction,0)) AS satisfactions
FROM
mp\_satisfaction\_info a
WHERE a.is\_deleted = "00"
GROUP BY
a.YEAR,
a.project\_id
order by a.`year`, a.`quarter`
合并前的数据长这个样子:
合并后的数据长这个样子:
GROUP_CONCAT()函数返回的字段类型是String,这里拿到数据后,需要用到String类的方法split(","),该方法会以形参中指定的字符分割字符串,并返回一个String[],拿到数据后遍历数据,填充数据,即可实现数据合并.
Page<Object> page = PageHelper.startPage(pageNum, pageSize);
List<MpSatisfactionInfoDO> list = satisfactionInfoDao.list(pageReq.getYear(),pageReq.getProjectName());
//mergeList()方法的代码在下面
List<SatisfactionInfoOfYearRes> resList = this.mergeList(list);
PageResponse pageResponse = new PageResponse();
pageResponse.setData(resList);
pageResponse.setTotalNum(page.getTotal());
pageResponse.setTotalPage(page.getPages());
return pageResponse;
这里用switch目的是方便前端拿参数,如果前端技术好可以用map<>存储遍历得到的数据.
private List<SatisfactionInfoOfYearRes> mergeList(List<MpSatisfactionInfoDO> list) {
ArrayList<SatisfactionInfoOfYearRes> resList = new ArrayList<>();
list.forEach(p ->{
SatisfactionInfoOfYearRes satisfactionInfoOfYearRes = new SatisfactionInfoOfYearRes();
BeanUtils.copyProperties(p, satisfactionInfoOfYearRes);
String\[] quarters = p.getQuarters().split(",");
String\[] satisfactions = p.getSatisfactions().split(",");
for (int i = 0; i < quarters.length; i++){
String quarter = quarters\[i];
BigDecimal satisfaction = new BigDecimal(satisfactions\[i]);
switch (quarter){
case "1":
satisfactionInfoOfYearRes.setFirstQuarter(satisfaction);
break;
case "2":
satisfactionInfoOfYearRes.setSecondQuarter(satisfaction);
break;
case "3":
satisfactionInfoOfYearRes.setThirdQuarter(satisfaction);
break;
case "4":
satisfactionInfoOfYearRes.setFourthQuarter(satisfaction);
}
}
resList.add(satisfactionInfoOfYearRes);
});
return resList;
}
