记录文件入库效率优化,之前的代码大约每秒1000条左右,这样优化完后大约每秒1200,具体效率有点记不清了,效率肯定是有提升的。
需求:给一个txt压缩后的zip文件,进行入库操作。
入库时先修改该入库任务的状态。
再验证文件的MD5是否正确。
再读取文件每1000条数据入库一次,分批入库。
完成后修改入库任务为完成,并返回本次入库时的版本号。
/**
* 文件解压入库
*/
@Override
public String stockpileFile(MultipartFile multipartFile, HttpServletRequest request, Long resourceFileId, String md5) {
if (multipartFile.isEmpty() || multipartFile.getSize() == 0) {
throw new BusinessException("文件为空");
}
LocalDateTime startTime = LocalDateTime.now(); // 记录开始时间
/**修改任务状态*/
updateFileStatus(resourceFileId, MedicalInsuranceFileStatus.Downloading, MedicalInsuranceFileStatus.Be_Put_In_Storage);
FileResourceList fileResource = fileResourceListRepository.findFileResourceListById(resourceFileId);
if (fileResource == null) {
throw new BusinessException("文件id:" + resourceFileId + "不存在");
}
try (InputStream inputStream = multipartFile.getInputStream()) {
String fileMD5 = DigestUtils.md5Hex(inputStream);
if (!fileMD5.equalsIgnoreCase(md5)) {
throw new BusinessException("文件错误,MD5验证失败");
}
try (ZipInputStream zipInputStream = new ZipInputStream(new ByteArrayInputStream(multipartFile.getBytes()), Charset.forName("GBK"))) { //new ByteArrayInputStream(multipartFile.getBytes())
ZipEntry entry;
List<String> lines = new ArrayList<>(1000);
List<CompletableFuture> futures = new ArrayList<>();
try (BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(zipInputStream, "UTF-8"), 65536)) {
while ((entry = zipInputStream.getNextEntry()) != null) {
int count = 0;
String recordLine;
while ((recordLine = bufferedReader.readLine()) != null) {
lines.add(recordLine);
if (++count % 1000 == 0) {
batchSaveRecordService.batchSaveRecords(lines, fileResource);
lines.clear();
}
}
if (lines.size() > 0) {
batchSaveRecordService.batchSaveRecords(lines, fileResource);
}
}
}
}
} catch (IOException e) {
updateFileStatus(resourceFileId, MedicalInsuranceFileStatus.Exception, MedicalInsuranceFileStatus.Be_Put_In_Storage);
e.printStackTrace();
throw new BusinessException("文件IO异常!");
} catch (Exception e) {
e.printStackTrace();
throw e;
}
/**修改任务状态为:完成*/
updateFileStatus(resourceFileId, MedicalInsuranceFileStatus.Finish, MedicalInsuranceFileStatus.Finish);
LocalDateTime endTime = LocalDateTime.now(); //记录结束时间
long durationInSeconds = java.time.Duration.between(startTime, endTime).getSeconds();
System.out.println("持续时间(秒):" + durationInSeconds);
FileResourceList fileResourceList = fileResourceListRepository.findFileResourceListById(resourceFileId);
return fileResourceList.getLocalVersion();
}
入库时其他处理
入库时选择其中部分字段进行入库。
对已有的数据进行更新,没有的则进行新增。
对需要保留的数据进行不更新处理。
每入库1000条数据时,更新一次当前任务的入库条数的数量进度。
更新文件中最大的版本号。
package com.medier.insur.service.impl;
import com.medier.common.exception.BusinessException;
import com.medier.common.utils.HanyupinyinUtils;
import com.medier.insur.model.FileResourceList;
import com.medier.insur.model.MedicalInsuranceDirectory;
import com.medier.insur.model.MedicalInsuranceFile;
import com.medier.insur.repo.FileResourceListRepository;
import com.medier.insur.repo.MedicalInsuranceDirectoryRepository;
import com.medier.insur.repo.MedicalInsureFileRepository;
import com.medier.insur.utils.MedicalInsuranceDirectoryConstant;
import com.medier.insur.utils.MedicalInsuranceFileConstant;
import com.medier.insur.utils.MedicalInsuranceFileStatus;
import lombok.RequiredArgsConstructor;
import org.junit.Test;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
/**
* @author: Yz
* @Description:
* @create: 2023-09-03
**/
@Service
@Transactional
@RequiredArgsConstructor
public class BatchSaveRecordService {
private final MedicalInsureFileRepository medicalInsureFileRepository;
private final FileResourceListRepository fileResourceListRepository;
private final MedicalInsuranceDirectoryRepository medicalInsuranceDirectoryRepository;
@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = {Exception.class})
public void batchSaveRecords(List<String> lines, FileResourceList fileResource) {
List<MedicalInsuranceDirectory> medicalInsuranceDirectoryList = new ArrayList<>();
Set<String> verSet = new HashSet<>();
if (fileResource.getResourceFileCode().equals(MedicalInsuranceFileConstant.Chinese_Medicine_Pieces)) {
/**中药饮片*/
for (String line : lines) {
String[] parts = line.split("\t");
MedicalInsuranceDirectory medicalInsuranceDirectory = new MedicalInsuranceDirectory();
medicalInsuranceDirectory.setMedInsCode(parts[0].toUpperCase()); //编码
medicalInsuranceDirectory.setMedInsName(parts[1].equals("null") ? null : parts[1]); //名称
// medicalInsuranceDirectory.setStandardCode(parts[8]); //药监本位码
// medicalInsuranceDirectory.setSpecification(parts[16]); //规格
// medicalInsuranceDirectory.setPacMatl(parts[29]); //包装材质
// medicalInsuranceDirectory.setMinUnt(parts[40]); //最小计量单位
// medicalInsuranceDirectory.setMinPacCnt((parts[41].equals("null")) ? null : Integer.parseInt(parts[41])); //最小包装数量
// medicalInsuranceDirectory.setMinPacUnt(parts[42]); //最小包装单位
// medicalInsuranceDirectory.setManufacturers(parts[23]); //生产企业名称-药材种来源
// medicalInsuranceDirectory.setAprvNo(parts[61].equals("null") ? null : parts[61]); //批准文号
// medicalInsuranceDirectory.setDosageForm(parts[10]); //剂型-品种
// medicalInsuranceDirectory.setClassABMark(parts[91]); //甲乙类标识
medicalInsuranceDirectory.setResourceFileCode(fileResource.getResourceFileCode()); //资源文件编号
//medicalInsuranceDirectory.setManMnemonicCode(HanyupinyinUtils.getFirstLettersUp(parts[23]));
medicalInsuranceDirectory.setMedMnemonicCode((HanyupinyinUtils.getFirstLettersUp(parts[1])).equals("null") ? null : (HanyupinyinUtils.getFirstLettersUp(parts[1])));
medicalInsuranceDirectory.setParams(medicalInsuranceDirectory.assignment()); //模糊查询参数
verSet.add(parts[18]);
medicalInsuranceDirectoryList.add(medicalInsuranceDirectory);
}
} else if (fileResource.getResourceFileCode().equals(MedicalInsuranceFileConstant.medical_Consumables)) {
/**医疗耗材*/
for (String line : lines) {
String[] parts = line.split("\t");
MedicalInsuranceDirectory medicalInsuranceDirectory = new MedicalInsuranceDirectory();
medicalInsuranceDirectory.setMedInsCode(parts[0].toUpperCase()); //编码
medicalInsuranceDirectory.setMedInsName(parts[1].equals("null") ? null : parts[1]); //名称
// medicalInsuranceDirectory.setStandardCode(parts[8]); //药监本位码
medicalInsuranceDirectory.setSpecification(parts[7].equals("null") ? null : parts[7]); //规格
medicalInsuranceDirectory.setPacMatl(parts[14].equals("null") ? null : parts[14]); //包装材质
// medicalInsuranceDirectory.setMinUnt(parts[40]); //最小计量单位
// medicalInsuranceDirectory.setMinPacCnt((parts[41].equals("null")) ? null : Integer.parseInt(parts[41])); //最小包装数量
// medicalInsuranceDirectory.setMinPacUnt(parts[42]); //最小包装单位
medicalInsuranceDirectory.setManufacturers(parts[59].equals("null") ? null : parts[59]); //生产企业名称
// medicalInsuranceDirectory.setAprvNo(parts[61]); //批准文号
// medicalInsuranceDirectory.setDosageForm(parts[10]); //剂型
// medicalInsuranceDirectory.setClassABMark(parts[91]); //甲乙类标识
medicalInsuranceDirectory.setResourceFileCode(fileResource.getResourceFileCode()); //资源文件编号
medicalInsuranceDirectory.setParams(medicalInsuranceDirectory.assignment()); //模糊查询参数
medicalInsuranceDirectory.setManMnemonicCode((HanyupinyinUtils.getFirstLettersUp(parts[59])).equals("null") ? null : (HanyupinyinUtils.getFirstLettersUp(parts[59])));
medicalInsuranceDirectory.setMedMnemonicCode((HanyupinyinUtils.getFirstLettersUp(parts[4])).equals("null") ? null : (HanyupinyinUtils.getFirstLettersUp(parts[4])));
verSet.add(parts[70]);
medicalInsuranceDirectoryList.add(medicalInsuranceDirectory);
}
} else if (fileResource.getResourceFileCode().equals(MedicalInsuranceFileConstant.Western_Chinese_Patent)) {
/**西药中成药*/
for (String line : lines) {
String[] parts = line.split("\t");
MedicalInsuranceDirectory medicalInsuranceDirectory = new MedicalInsuranceDirectory();
medicalInsuranceDirectory.setMedInsCode(parts[0].toUpperCase());
medicalInsuranceDirectory.setMedInsName(parts[3].equals("null") ? null : parts[3]);
medicalInsuranceDirectory.setStandardCode(parts[8].equals("null") ? null : parts[8]);
medicalInsuranceDirectory.setSpecification(parts[16].equals("null") ? null : parts[16]);//规格
medicalInsuranceDirectory.setPacMatl(parts[29].equals("null") ? null : parts[29]);
medicalInsuranceDirectory.setMinUnt(parts[40].equals("null") ? null : parts[91]);
medicalInsuranceDirectory.setMinPacCnt((parts[41].equals("null")) ? null : Integer.parseInt(parts[41]));
medicalInsuranceDirectory.setMinPacUnt(parts[42].equals("null") ? null : parts[42]);
medicalInsuranceDirectory.setManufacturers(parts[53].equals("null") ? null : parts[53]);
medicalInsuranceDirectory.setAprvNo(parts[61].equals("null") ? null : parts[61]);
medicalInsuranceDirectory.setDosageForm(parts[90].equals("null") ? null : parts[90]);
medicalInsuranceDirectory.setClassABMark(parts[91].equals("null") ? null : parts[91]);
medicalInsuranceDirectory.setResourceFileCode(fileResource.getResourceFileCode());
medicalInsuranceDirectory.setManMnemonicCode((HanyupinyinUtils.getFirstLettersUp(parts[53])).equals("null") ? null : (HanyupinyinUtils.getFirstLettersUp(parts[53])));
medicalInsuranceDirectory.setMedMnemonicCode((HanyupinyinUtils.getFirstLettersUp(parts[3])).equals("null") ? null : (HanyupinyinUtils.getFirstLettersUp(parts[3])));
medicalInsuranceDirectory.setParams(medicalInsuranceDirectory.assignment());
verSet.add(parts[83]);
medicalInsuranceDirectoryList.add(medicalInsuranceDirectory);
}
}
/**保存数据-更新或新增*/
saveDataInBatches(medicalInsuranceDirectoryList, fileResource.getId());
/**修改最大版本号*/
updateMaxVer(fileResource.getId(), verSet);
}
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void updateRecordCount(Long resourceFileId, int count) {
/**记录数据总条数*/
MedicalInsuranceFile MIF = medicalInsureFileRepository.findMedicalInsuranceFileByResourceFileIdAndFileStatus(resourceFileId, MedicalInsuranceFileStatus.Downloading);
if (MIF == null) {
throw new BusinessException(resourceFileId + "无正在下载的任务");
}
MIF.setRows(count);
medicalInsureFileRepository.save(MIF);
}
/**
* 入库时有则更新,没有则新增
*/
private void saveDataInBatches(List<MedicalInsuranceDirectory> westernChinesePatentMedicineCatalogs, Long resourceFileId) {
/**更新数据集合*/
List<MedicalInsuranceDirectory> existingEntities = new ArrayList<>();
/**将数据库中重复的对象添加至 更新数据集合*/
List<String> codeList = westernChinesePatentMedicineCatalogs.stream()
.map(MedicalInsuranceDirectory::getMedInsCode)
.collect(Collectors.toList());
Map<String, MedicalInsuranceDirectory> medicalInsuranceDirectoryMap = westernChinesePatentMedicineCatalogs.stream().collect(Collectors.toMap(MedicalInsuranceDirectory::getMedInsCode, Function.identity()));
List<MedicalInsuranceDirectory> result = medicalInsuranceDirectoryRepository.findMedicalInsuranceDirectoriesByMedInsCodeIn(medicalInsuranceDirectoryMap.keySet());//获取数据库中重复的对象。
existingEntities.addAll(result);
/**根据查询结果进行更新或新增操作*/
for (MedicalInsuranceDirectory MID : existingEntities) {
MedicalInsuranceDirectory entity = medicalInsuranceDirectoryMap.get(MID.getMedInsCode().toUpperCase());
MID.setIsArtificialMark(MedicalInsuranceDirectoryConstant.IS_NOT);
MID.setResourceFileCode(entity.getResourceFileCode());
MID.setStandardCode(entity.getStandardCode());
MID.setSpecification(entity.getSpecification());
MID.setManufacturers(entity.getManufacturers());
MID.setPacMatl(entity.getPacMatl());
MID.setMinUnt(entity.getMinUnt());
MID.setMinPacUnt(entity.getMinPacUnt());
MID.setMinPacCnt(entity.getMinPacCnt());
MID.setMedInsName(entity.getMedInsName());
MID.setMedInsCode(entity.getMedInsCode());
MID.setDosageForm(entity.getDosageForm());
MID.setClassABMark(entity.getClassABMark());
MID.setAprvNo(entity.getAprvNo());
/**对退出商品进行保留*/
if (MID.getValidMark().equals(MedicalInsuranceDirectoryConstant.EXITED)) {
MID.setValidMark(MedicalInsuranceDirectoryConstant.EXITED);
} else {
MID.setValidMark(MedicalInsuranceDirectoryConstant.NOT_QUIT);
}
if (entity.getManufacturers() == null) {
MID.setManMnemonicCode(null);
} else {
MID.setManMnemonicCode(HanyupinyinUtils.getFirstLettersUp(entity.getManufacturers()));
}
if (entity.getMedInsName() == null) {
MID.setMedMnemonicCode(null);
} else {
MID.setMedMnemonicCode(HanyupinyinUtils.getFirstLettersUp(entity.getMedInsName()));
}
MID.setParams(MID.assignment());
medicalInsuranceDirectoryMap.remove(MID.getMedInsCode());
}
/**新增数据集合*/
List<MedicalInsuranceDirectory> newEntities = new ArrayList<>(medicalInsuranceDirectoryMap.values());
/**批量更新*/
if (!existingEntities.isEmpty()) {
saveData(existingEntities, resourceFileId);
}
/**批量新增*/
if (!newEntities.isEmpty()) {
saveData(newEntities, resourceFileId);
}
}
/**
* 更新或新增
*/
private void saveData(List<MedicalInsuranceDirectory> westernChinesePatentMedicineCatalogs, Long resourceFileId) {
int totalCount = westernChinesePatentMedicineCatalogs.size();
medicalInsuranceDirectoryRepository.saveAll(westernChinesePatentMedicineCatalogs);
updateProgress(resourceFileId, westernChinesePatentMedicineCatalogs.size());
medicalInsuranceDirectoryRepository.flush();
}
/**
* 修改当前入库进度
*/
private void updateProgress(Long resourceFileId, int savedCount) {
MedicalInsuranceFile MIF = medicalInsureFileRepository.findMedicalInsuranceFileByResourceFileIdAndFileStatus(resourceFileId, MedicalInsuranceFileStatus.Downloading);
if (MIF == null) {
throw new BusinessException(resourceFileId + "无正在下载的任务");
}
MIF.setCurrentProgress(savedCount + MIF.getCurrentProgress());
medicalInsureFileRepository.save(MIF);
medicalInsureFileRepository.flush();
}
/**
* 修改最大版本号
*/
private void updateMaxVer(Long resourceFileId, Set<String> set) {
MedicalInsuranceFile MIF = medicalInsureFileRepository.findMedicalInsuranceFileByResourceFileIdAndFileStatus(resourceFileId, MedicalInsuranceFileStatus.Downloading);
if (MIF == null) {
throw new BusinessException(resourceFileId + "无正在下载的任务");
}
List<String> list = new ArrayList<>(set);
list.add(MIF.getLocalVersion());
list.removeAll(Collections.singleton(null));
Collections.sort(list);
String ver ="";
if (set.size() == 1) {
MIF.setLocalVersion(set.iterator().next());
ver=set.iterator().next();
} else {
if (list.get(list.size() - 1).equals("V1.0")) {
ver = list.get(list.size() - 2);
} else {
ver = list.get(list.size() - 1);
}
MIF.setLocalVersion(ver);
}
medicalInsureFileRepository.save(MIF);
FileResourceList fileResourceList = fileResourceListRepository.findFileResourceListById(resourceFileId);
fileResourceList.setLocalVersion(ver);
fileResourceListRepository.save(fileResourceList);
}
}