前言
- 最近遇到一个场景问题,就是基于SQL server数据库的规范,查询条件in如果个数超过2100个就会报错。由于是ORM映射框架采用的MybatisPlus 起初我想到的是基于 MybatisPlus 的 参数分割,测试还是不行,于是就直接基于 mybatis xml 去做这个事情了。
- 思路:1-分批查询再汇总,2-再手动算法分页
伪代码举例
public static final int MATERIAL_LINE_EXCEL_MAX_NUM = 1500;
public static final int BATCH_SIZE = 500;
private List<OrderInfo> getOrderInfoList(reqParam param, List<Long> idList) {
List<OrderInfo> sumList = new ArrayList<>();
if (idList.size() <= MATERIAL_LINE_EXCEL_MAX_NUM) {
sumList = orderInfoMapper.selectOrderInfoList(param, idList);
} else {
int i = idList.size() / IMPORT_LINE_EXCEL_MAX_NUM;
for (int i1 = 0; i1 <= i; i1++) {
if (i1 == i) {
List<Long> subList = idList.subList(i * IMPORT_LINE_EXCEL_MAX_NUM, idList.size());
if (CollectionUtil.isNotEmpty(subList)) {
List<OrderInfo> orderInfoList= materialRequisitionMapper.selectOrderInfoList(param, subList);
sumList.addAll(orderInfoList);
}
} else {
List<Long> subList = idList.subList(i1 * IMPORT_LINE_EXCEL_MAX_NUM, (i1 + 1) * IMPORT_LINE_EXCEL_MAX_NUM);
List<OrderInfo> orderInfoList= orderInfoMapper.selectOrderInfoList(param, subList);
sumList.addAll(materialRequisitionList);
}
}
}
return sumList;
}
Page<OrderInfo> page = new Page<>(param.getCurrent(), param.getSize());
long startIndex = (param.getCurrent() - 1) * param.getSize();
long endIndex = Math.min(startIndex + param.getSize(), orderInfoList.size());
List<OrderInfo> paginatedList = orderInfoList.subList((int) startIndex, (int) endIndex);
paginatedList.sort(Comparator.comparing(OrderInfo::getOrderId).reversed());
page.setRecords(paginatedList);
page.setTotal(materialRequisitionList.size());
return paginatedList;