开发过程必不可少的业务功能就有导入导出、下载上传,本菜菜鸟在此过程中遇到了一些问题,当然对于别人来说可能不算什么,菜菜鸟在此记录一下,以便自己今后查看,也希望可以帮助到别人。
废话不说,直接上代码
首先需要工具类、配置啥的,在这我就不搞出来了,下面涉及的一些数据等等可以酌情处理,以映射为主
菜菜鸟导入主要分成四部分
Controller
因为业务需要,客户数据和系统数据存在差异,因此实现新功能模块,关系数据
@RestController
@Slf4j
@RequiredArgsConstructor
@RequestMapping("/baseTownMapping")
@Api(value = "baseTownMapping", tags = "关系映射")
public class BaseTownMappingController {
//导入
@Autowired
private BaseTownMappingService baseTownMappingService;
@ApiOperation(value = "乡镇映射信息导入", notes = "乡镇映射信息导入")
@PostMapping("/importExcelData") //接口设置
@PreAuthorize("@pms.hasPermission('baseTownMapping_import')") //权限设置
public R importExcelData(FileParams file) { //FileParams 工具类 ; R 共用类
try {
//判断文件是否为空
BaseFileMsgVO baseFileMsgVO = baseBusinessInfoService.importExcelData(file);
if (baseFileMsgVO.getFileEmpty()) {
return R.failed("文件为空!");
}else {
return R.ok(baseFileMsgVO);
}
} catch (ExcelAnalysisException e) {
log.error("导入数据错误", e);
return R.failed(e.getMessage());
} catch (Exception e) {
log.error("导入数据错误", e);
return R.failed(e.getMessage());
}
}
}
Service
其中包括Service和ServiceImpl,以及文件Excellistener
Service层
映射Service
public interface BaseTownMappingService extends IService<BaseTownMapping> {
//导入
BaseFileMsgVO importExcelData(FileParams fileParams);
//删除已有数据
void deleteBatchByTownCode(List<String> townCodeList);
//新增数据
void insertBatch(List<BaseTownMapping> baseTownMappingList);
//更新数据
//void updateTownByTownCode(@Param("param")BaseTownMappingExcelReadDTO baseTownMappingExcelReadDTO);
}
系统Service
public interface BaseTownService extends IService<BaseTown> {
List<BaseTownDTO> getTownNameByTownCode();
}
ServiceImpl层
映射ServiceImpl
public class BaseTownMappingServiceImpl extends ServiceImpl<BaseTownMappingMapper, BaseTownMapping> implements BaseTownMappingService {
@Autowired
private BaseTownService baseTownService;
@Autowired(required = false)
private BaseTownMappingMapper baseTownMappingMapper;
@Override
public BaseFileMsgVO importExcelData(FileParams fileParams) {
BaseFileMsgVO baseFileMsgVO = new BaseFileMsgVO();
if (fileParams != null) {
try {
List<BaseFileErrorVO> errorVOList = new ArrayList<>();
/*
* 系统数据查询,映射字段属性,存放map集合中
*/
List<BaseTownDTO> list = baseTownService.getTownNameByTownCode();
Map<String, String> townMap = new HashMap<>();
if (!CollectionUtils.isEmpty(list)) {
list.forEach(dto -> {
townMap.put(dto.getTownCode(), dto.getTownName());
});
}
/*
* 客户数据查询,映射字段属性,利用key:value存放于map集合中,key唯一,value不唯一
*/
List<BaseTownMappingDTO> mappingList = baseTownMappingMapper.getTownNameByTownCode();
Map<String, String> mappingMap = new HashMap<>();
if (!CollectionUtils.isEmpty(mappingList)) {
mappingList.forEach(dto -> {
mappingMap.put(dto.getTownCode(), dto.getSwireCode());
});
}
List<Integer> nums = new ArrayList<>();
EasyExcel.read(fileParams.getFile().getInputStream(),
BaseTownMappingExcelReadDTO.class,
new BaseTownMappingListener(redisUtil, fileParams.getGuid(), this, baseTownService, townMap, mappingMap, 1, errorVOList,nums)).sheet().doRead();
baseFileMsgVO.setErrors(errorVOList);
Integer total = (Integer) redisUtil.get(CacheConstants.PROJECT_BUSINESS_IMPORT + fileParams.getGuid());
baseFileMsgVO.setTotal(total);
if (!CollectionUtil.isEmpty(errorVOList)) {
baseFileMsgVO.setCheckListFlag(true);
} else {
baseFileMsgVO.setCheckListFlag(false);
}
} catch (IOException e) {
log.error("导入数据错误", e);
throw new RuntimeException("导入数据错误", e);
}
}
return baseFileMsgVO;
}
@Override
public void deleteBatchByTownCode(List<String> townCodeList) {
baseTownMappingMapper.deleteBatchByTownCode(townCodeList);
}
@Override
public void insertBatch(List<BaseTownMapping> baseTownMappingList) {
baseTownMappingMapper.insertBatch(baseTownMappingList);
}
}
系统ServiceImpl
@Service
@Transactional(rollbackFor = Exception.class)
public class BaseTownServiceImpl extends ServiceImpl<BaseTownMapper, BaseTown> implements BaseTownService {
@Override
public List<BaseTownDTO> getTownNameByTownCode() {
return this.baseTownMapper.getTownNameByTownCode();
}
}
Excellistener
导入功能,实现数据库有数据直接修改,没有数据进行新增;后期保存的时候被公司大哥修改了一丢丢
@Slf4j
public class BaseTownMappingListener extends AnalysisEventListener<BaseTownMappingExcelReadDTO> {
/**
* 每隔3000条存储数据库,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 2000;
//文件上传完毕所占百分比(根据导入数据量设定)
private static final Double IMPORT_SCHEDULE = 15D;
private final Map<String, String> mappingMap;
private List<Integer> nums;
private RedisUtil redisUtil;
private String guid; //导入文件redis key用于记录数据
private BaseTownMappingService baseTownMappingService;
private Integer index;
private List<BaseFileErrorVO> errorVOList;
// 必填写字段
private String townCode;//乡镇编码
// 导入数据的数量
private Integer dataIndex = 0;
private BaseTownService baseTownService;
public BaseTownMappingListener(RedisUtil redisUtil, String guid,
BaseTownMappingService baseTownMappingService,
BaseTownService baseTownService,
Map<String,String> townMap,
Map<String,String> mappingMap,
Integer index,
List<BaseFileErrorVO> errorVOList,
List<Integer> nums
) {
this.redisUtil = redisUtil;
this.guid = guid;
this.baseTownMappingService = baseTownMappingService;
this.baseTownService = baseTownService;
this.townMap = townMap;
this.mappingMap = mappingMap;
this.index = index;
this.errorVOList = errorVOList;
this.nums = nums;
//设置导入进度条值(根据业务数据量设置)key存在获取缓存进度值并更新,不存在设置进度值并存储
ImplrtScheduleRedisUtil.putImportScheduleValue(redisUtil, guid, IMPORT_SCHEDULE);
}
/**
* 乡镇附加信息列表
*/
List<BaseTownMapping> baseTownMappingList = new ArrayList<>();
List<Long> townIdList = new ArrayList<>();
Map<String,String> map = new HashMap<>();
Map<String,String> townMap = new HashMap<>();
List<String> delTownCodes = new ArrayList<>();
/**
* uniqueKey
*/
Set<String> uniqueKey = new HashSet<>();
@Override
public void invoke(BaseTownMappingExcelReadDTO data, AnalysisContext context) {
nums.add(index);
index++;
StringBuilder stringBuilder = getCheckInfo(data);
Boolean flag = true;
if (stringBuilder.length() > 0 ) {
BaseFileErrorVO baseFileErrorVO = new BaseFileErrorVO();
baseFileErrorVO.setError(stringBuilder.toString());
baseFileErrorVO.setIndex(index);
errorVOList.add(baseFileErrorVO);
flag = false;
}
//log.info("解析到一条数据:{}", JSON.toJSONString(data));
if(flag){
if (mappingMap.containsKey(data.getTownCode())){
delTownCodes.add(data.getTownCode());
}
BaseTownMapping baseTownDetail = new BaseTownMapping();
baseTownDetail.setTownCode(data.getTownCode());
baseTownDetail.setTownName(townMap.get(data.getTownCode()));
baseTownDetail.setSwireCode(data.getSwireCode());
baseTownDetail.setSwireName(data.getSwireName());
PigUser user = SecurityUtils.getUser();
//设置修改人和创建人(当前修改人就是创建人)
baseTownDetail.setCreateBy(user.getUsername());
baseTownDetail.setUpdateBy(user.getUsername());
baseTownMappingList.add(baseTownDetail);
}
/*
* 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM(改的时候发现用不到删了部分的实现)
*/
if (baseTownMappingList.size() > BATCH_COUNT) {
saveData(context);
// 存储完成清理 list
baseTownMappingList.clear();
}
}
private void saveLbsData() {
BaseTownPullLbsRequest request = new BaseTownPullLbsRequest();
request.setTownIds(townIdList);
}
/**
* 乡镇附件数据合法性校验(目前检验乡镇编码)
* @param data
* @return
*/
private StringBuilder getCheckInfo(BaseTownMappingExcelReadDTO data) {
StringBuilder stringBuilder = new StringBuilder();
// 系统乡镇编码
String townCode = data.getTownCode();//系统乡镇编码
if (StringUtils.isEmpty(townCode)) {
stringBuilder.append("系统乡镇编码不能为空;");
}else {
if(!townMap.containsKey(data.getTownCode())){
stringBuilder.append("系统乡镇编码为'" + data.getTownCode()+ "'的乡镇不存在;");
}else {
if (uniqueKey.contains(data.getTownCode())){
stringBuilder.append("系统乡镇编码为'" + data.getTownCode()+ "'重复;");
}else{
uniqueKey.add(data.getTownCode());
}
}
}
String swireCode = data.getSwireCode();
if (StringUtils.isEmpty(swireCode)) {
stringBuilder.append("太古乡镇编码不能为空;");
}
return stringBuilder;
}
/**
* 所有数据解析完成了 都会来调用
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData(context);
//saveLbsData();
log.info("所有数据解析完成!");
//设置导入进度条(根据业务数据条数设置)
ImplrtScheduleRedisUtil.putImportScheduleValue(redisUtil, this.guid, 100D);
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
* @param exception
* @param context
* @throws Exception
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
log.error("解析失败,不继续解析下一行:{}", exception.getMessage());
// 如果是某一个单元格的转换异常 能获取到具体行号
BaseFileErrorVO baseFileErrorVO = new BaseFileErrorVO();
// 如果要获取头的信息 配合invokeHeadMap使用
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
log.error("第{}行第{}列解析异常:{}", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), exception.getMessage());
baseFileErrorVO.setIndex(excelDataConvertException.getRowIndex() + 1);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("第").append(excelDataConvertException.getColumnIndex() + 1).append("列 数据格式错误");
baseFileErrorVO.setError(stringBuilder.toString());
} else {
baseFileErrorVO.setError(exception.getMessage());
}
if(null != baseFileErrorVO.getIndex()){
errorVOList.add(baseFileErrorVO);
}
}
/**
* 储数乡镇据库
*/
private void saveData(AnalysisContext context) {
log.info("开始存储数据库,共{}条数据!", baseTownMappingList.size());
dataIndex += baseTownMappingList.size();
/*if(updateBaseTownMappingList != null && updateBaseTownMappingList.size()>0){
baseTownMappingService.updateBatch(updateBaseTownMappingList);
}*/
if (baseTownMappingList.size() > 0) {
//先删除所有乡镇数据,后保存
if (CollectionUtil.isNotEmpty(delTownCodes) && delTownCodes.size() > 0){
//删除
baseTownMappingService.deleteBatchByTownCode(delTownCodes);
delTownCodes.clear();
}
baseTownMappingService.insertBatch(baseTownMappingList);
//saveLbsData();//保存乡镇附加信息lbs
//------------------进度条设置----------------------------
//进度=100 * (dataList.size()/totalCount)
Integer totalCount = context.readSheetHolder().getApproximateTotalRowNumber();
Double stepValue = new BigDecimal(100 - IMPORT_SCHEDULE).multiply(new BigDecimal(baseTownMappingList.size()).divide(new BigDecimal(totalCount.toString()), 6, BigDecimal.ROUND_HALF_UP)).doubleValue();
//设置导入进度条(根据业务数据条数设置)
ImplrtScheduleRedisUtil.putImportScheduleValue(redisUtil, this.guid, stepValue);
redisUtil.set(CacheConstants.PROJECT_BUSINESS_IMPORT + this.guid, dataIndex, 2 * 60);
//------------------进度条设置----------------------------
}
log.info("存储数据库成功!");
}
}
Mapper
Mapper层
映射Mapper
@Mapper
public interface BaseTownMappingMapper extends BaseMapper<BaseTownMapping>{
List<BaseTownMappingDTO> getTownNameByTownCode();
void deleteBatchByTownCode(List<String> townCodeList);
void insertBatch(@Param("list") List<BaseTownMapping> baseTownMappingList);
}
系统Mapper
@Mapper
public interface BaseTownMapper extends BaseMapper<BaseTown> {
List<BaseTownDTO> getTownNameByTownCode();
}
MapperXML层
映射MapperXML
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.topprismcloud.rtm.base.mapper.BaseTownMappingMapper">
<resultMap id="BaseTownMappingMap" type="com.topprismcloud.rtm.base.api.entity.BaseTownMapping">
<id property="id" column="id"/>
<result property="townCode" column="town_code"/>
<result property="townName" column="town_name"/>
<result property="swireCode" column="swire_code"/>
<result property="swireName" column="swire_name"/>
<result property="createTime" column="create_time"/>
<result property="createBy" column="create_by"/>
<result property="type" column="type"/>
</resultMap>
<select id="getTownNameByTownCode" resultType="com.topprismcloud.rtm.base.api.dto.BaseTownMappingDTO" >
SELECT
m.town_code as townCode,
m.swire_code as swireCode
FROM
base_town_mapping m
</select>
<delete id="deleteBatchByTownCode" parameterType="java.util.List">
delete FROM base_town_mapping
<where>
town_code in
<foreach collection="list" item="item" separator="," close=")" open="(" index="index">
#{item}
</foreach>
</where>
</delete>
<insert id="insertBatch" parameterType="java.util.List">
insert into base_town_mapping (
town_code,
town_name,
swire_code,
swire_name,
create_by,
update_by,
create_time,
update_time
)
VALUES
<foreach collection ="list" item="entity" index= "index" separator =",">
(
#{entity.townCode},
#{entity.townName},
#{entity.swireCode},
#{entity.swireName},
#{entity.createBy},
#{entity.createBy},
now(),
now()
)
</foreach>
</insert>
<!-- <select id="getTownByTownCodeId" resultMap="BaseTownMappingMap" >
select * from base_town_mapping where town_code = #{townCode}
</select>
<update id="updateTownByTownCode" >
UPDATE
base_town_mapping AS b
SET
b.town_name = #{param.townName},
b.swire_code = #{param.swireCode},
b.swire_name = #{param.swireName},
b.create_by = #{param.createBy}
WHERE
b.town_code = #{param.townCode}
</update> -->
</mapper>
系统MapperXML
<mapper namespace="com.topprismcloud.rtm.base.mapper.BaseTownMapper">
<select id="getTownNameByTownCode" resultMap="baseTownMap" >
select t.town_name,t.town_code from base_town t
</select>
</mapper>
实体
DTO
@Data
public class BaseTownMappingExcelReadDTO {
@ApiModelProperty(value = "id")
// @ExcelProperty(index = 0,value = "id")
private String id;
/**
* 乡镇编码
*/
@ApiModelProperty(value = "系统乡镇编码")
@ExcelProperty(index = 0,value = "系统乡镇编码(必填)")
private String townCode;
/**
* 乡镇名称
*/
@ApiModelProperty(value = "系统乡镇名称")
@ExcelProperty(index = 1,value = "系统乡镇名称")
private String townName;
/**
* 乡镇编码
*/
@ApiModelProperty(value = "太古乡镇编码")
@ExcelProperty(index = 2,value = "太古乡镇编码")
private String swireCode;
/**
* 乡镇名称
*/
@ApiModelProperty(value = "太古乡镇名称")
@ExcelProperty(index = 3,value = "太古乡镇名称")
private String swireName;
@ApiModelProperty(value = "创建人")
@ExcelProperty(index = 4,value = "创建人")
private String createBy;
}
实体
@Data
@TableName("base_town_mapping")
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "")
public class BaseTownMapping extends Model<BaseTownMapping> {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@TableId
@ApiModelProperty(value = "主键")
private Long id;
/**
* 系统乡镇编码
*/
@ApiModelProperty(value = "系统乡镇编码")
@ExcelProperty(index = 0,value = "系统乡镇编码")
private String townCode;
/**
* 系统乡镇名称
*/
@ApiModelProperty(value = "系统乡镇名称")
@ExcelProperty(index = 1,value = "系统乡镇名称")
private String townName;
/**
* 太古乡镇编码
*/
@ApiModelProperty(value = "太古乡镇编码")
@ExcelProperty(index = 2,value = "太古乡镇编码")
private String swireCode;
/**
* 太古乡镇名称
*/
@ApiModelProperty(value = "太古乡镇名称")
@ExcelProperty(index = 3,value = "太古乡镇名称")
private String swireName;
@ApiModelProperty(value = "创建时间")
@ExcelProperty(index = 4,value = "创建时间")
private Date createTime;
@ApiModelProperty(value = "创建人")
@ExcelProperty(index = 5,value = "创建人")
private String createBy;
@ApiModelProperty(value = "修改时间")
@ExcelProperty(index = 6,value = "修改时间")
private Date updateTime;
@ApiModelProperty(value = "修改人")
@ExcelProperty(index = 7,value = "修改人")
private String updateBy;
}
以上就是我个人导入查写,其中有些数据的校验,警戒自己。
导入数据不能一条一条校验,一条一条校验影响运行加载,数据量大可能导致崩溃,所以需要合理利用集合一些特点。