MySQL调优--11--分批插入/更新数据 ---案例
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
案例1–Lists.partition
Lists.partition（）
private void updateStatus(List<WsapModel> wsapModelList) {
if (CollectionUtils.isEmpty(wsapModelList)) {
return;
}
try {
List<List<WsapModel>> partList = Lists.partition(wsapModelList, 1000);
for (int i = 0; i < partList.size(); i++) {
List<WsapModel> list = partList.get(i);
List<Long> idlist = list.stream().map(WsapModel::getId).collect(Collectors.toList());
personalSubscribeWsapMapper.updateStatus(idlist, LocalDateTime.now());
}
} catch (Exception e) {
logger.error("更新t_personal_subscribe_wsap status为1 异常：{} >>{}", DateUtils.getNow(),
e.getMessage());
}
}
案例2 --while 循环
while (true){
}
while (true) {
try {
goodsMediaDetailExportVOList = getGoodsMediaDetailExportVOList(goodsMediaDetailRepDTO, goodsMediaVO, goodsMediaDetailListApiRes);
log.info("查询物媒视图参数：{}，条数：{}", goodsMediaDetailRepDTO, goodsMediaDetailExportVOList.size());
if (CollUtil.isNotEmpty(goodsMediaDetailExportVOList)) {
totalRow = totalRow + goodsMediaDetailExportVOList.size();
listLists = getExportData(goodsMediaDetailExportVOList);
if (totalRow % fileRowSize == 0) {
fileIndex++;
fileName = "物媒明细数据报表" + fileIndex + ".csv";
path = syncExportDTO.getExportFilePath(downLoadFolder) + fileName;
fileNames.add(path);
ExportUtil.writeCsvFile(fileName, headerList, path, listLists, false);
} else {
ExportUtil.writeCsvFile(fileName, new ArrayList<>(), path, listLists, true);
}
} else {
break;
}
} catch (Exception e) {
log.error("物媒明细数据报表异常", e);
}
}
while (!CollectionUtils.isEmpty(detailList)) {
}
private final Integer BATCH_SIZE = 1000;
queryWrapper.last("limit " + BATCH_SIZE);
List<ActivityListApplyDetailModel> detailList = activityListApplyDetailMapper.selectList(queryWrapper);
int finallyTransferCount = 0;
while (!CollectionUtils.isEmpty(detailList)) {
// 将数据插入到迁移表
activityListApplyDetailMapper.newInsertBatchTransfer(tableName, detailList);
// 删除已迁移的数据
List<Long> detailIdList = detailList.stream().map(ActivityListApplyDetailModel::getId).collect(Collectors.toList());
activityListApplyDetailMapper.deleteBatchIds(detailIdList);
finallyTransferCount += detailList.size();
log.info("报名明细迁移已完成迁移量：{}", finallyTransferCount);
detailList = activityListApplyDetailMapper.selectList(queryWrapper);
}
while (true) 配合分页插件
int pageNum = 1, pageSize = 1000;
long count = 0;
while (true) {
pageQuery.setPageNum(pageNum);
pageQuery.setPageSize(pageSize);
List<GoodsMediaModel> goodsMediaModelList = PageHelperUtil.listByPage(() -> goodsMediaMapper.selectList(queryWrapper), pageQuery).getLists();
// 统计总数
count += goodsMediaModelList.size();
// 写入文件
FileLineHelper.appendWrite(file, parseToString(goodsMediaModelList, specifyDate));
if (goodsMediaModelList.size() < pageSize)
break;
else
pageNum++;
}
案例3-- do while
do{ } while (!CollectionUtils.isEmpty());
int pageNo = 1;
int pageSize = 1000;
PageBean<PersonalMediaDetailRespDTO> pageBean = null;
do {
// ck查询客户明细
try {
ApiResponse<PageBean<PersonalMediaDetailRespDTO>> pageBeanApiResponse = viewFeign.selectPersonalMediaViewDetailList(viewDetailRequestDTO);
pageBean = pageBeanApiResponse.getData();
if (pageBean == null || CollectionUtils.isEmpty(pageBean.getLists())) {
if (pageNo == 1) {
log.warn("【人媒明细报表导出】查询浏览事件为空");
List<List<Object>> dataList = Lists.newArrayList();
List<Object> data = Lists.newArrayList("未查找到符合条件的数据，请修改查询条件");
dataList.add(data);
ExportUtil.writeCsvFile(fileName, null, path, dataList, true);
}
break;
}
} catch (Exception e) {
log.error("【人媒明细报表导出】，统计view汇总，请求参数：{}, 错误信息：", JSONObject.toJSONString(viewDetailRequestDTO), e);
break;
}
List<PersonalMediaDetailRespDTO> viewDetailRespDTOList = pageBean.getLists();
pageNo++;
viewDetailRequestDTO.setPageNum(pageNo);
} while (!CollectionUtils.isEmpty(pageBean.getLists()));
do{
if (CollectionUtils.isEmpty())) {
break;
}
} while ()
@Override
public List<ChannelOrgDTO> selectActiveChannelList(String date) {
List<ChannelOrgDTO> result = Lists.newArrayList();
long pageCount;
int pageNum = 0;
PageQuery pageQuery = new PageQuery();
pageQuery.setPageSize(5000);
do {
pageNum++;
pageQuery.setPageNum(pageNum);
PageBean<ChannelOrgDTO> page =
PageHelperUtil.listByPage(() -> channelMapper.selectActiveChannelList(date), pageQuery);
if (CollectionUtils.isEmpty(page.getLists())) {
break;
}
result.addAll(page.getLists());
pageCount = page.getPageCount();
} while (pageCount > pageNum);
return result;
}
原文地址：https://blog.csdn.net/weixin_48052161/article/details/140696652
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！