数据量分页太大分段处理
前言
Github：https://github.com/HealerJean
1、Java 分段
@Slf4j
public class BigDataSubUtils {
// /**
// * 1、获取分段结果
// */
// public static void main(String[] args) {
// int totalCount = 0;
// int intervalLength = 5;
// List<ImmutablePair<Integer, Integer>> result = intervalPairOfTotalCount(totalCount, intervalLength);
// // result.forEach(item -> System.out.println(item.getLeft() + "-" + item.getRight()));
//
// List<String> list = Lists.newArrayList("1","2","3","4","5");
// List<List<String>> lists = intervalPairOfTotalCount(list, 6);
// lists.forEach(item -> {
// item.forEach(item2->{
// System.out.printf(item2);
// });
// System.out.println();
// });
// }
/**
* 获取分段区间，索引从0开始，注意下游
*
* @param totalCount 总数量
* @param pageSize 区间长度
*/
public static List<ImmutablePair<Integer, Integer>> sizeSub(Integer totalCount, Integer pageSize) {
List<ImmutablePair<Integer, Integer>> result = new ArrayList<>();
Integer pageCount;
if (totalCount % pageSize == 0) {
pageCount = totalCount / pageSize;
} else {
pageCount = totalCount / pageSize + 1;
}
Integer pageNow = 1;
while (pageNow <= pageCount) {
int startIndex = (pageNow - 1) * pageSize;
int endIndex = pageNow.compareTo(pageCount) != 0 ? startIndex + pageSize : totalCount;
ImmutablePair<Integer, Integer> pair = ImmutablePair.of(startIndex, endIndex);
pageNow++;
result.add(pair);
}
return result;
}
/**
* 获取分段区间
*
* @param list 数据
* @param pageSize 区间长度
*/
public static <T> List<List<T>> listSub(List<T> list, Integer pageSize) {
Integer totalCount = list.size();
List<List<T>> result = new ArrayList<>();
Integer pageCount;
if (totalCount % pageSize == 0) {
pageCount = totalCount / pageSize;
} else {
pageCount = totalCount / pageSize + 1;
}
Integer pageNow = 1;
while (pageNow <= pageCount) {
int startIndex = (pageNow - 1) * pageSize;
int endIndex = pageNow.compareTo(pageCount) != 0 ? startIndex + pageSize : totalCount;
List<T> sonList = list.subList(startIndex, endIndex);
pageNow++;
result.add(sonList);
}
return result;
}
}
2、RPC 多次查(分页)
注意下游bug导致无线死循环，防止方法，设置方法请求次数或者设置超时时间
2.1、普通调用
public Set<String> queryAllSigningVendorIds(SignUpStatusQueryReq signUpStatusQueryReq) {
Set<String> result = new HashSet<>();
PageQueryRequest<SignUpStatusQueryReq> pageQueryRequest = new PageQueryRequest<>();
pageQueryRequest.setQuery(signUpStatusQueryReq);
pageQueryRequest.setPageIndex(NumberConstant.ONE);
pageQueryRequest.setPageSize(NumberConstant.TEN_THOUSAND);
try {
while (true) {
PageQueryResponse<String> response = signResource.querySigningVendorIdsPage(pageQueryRequest);
if (response != null && Boolean.TRUE.equals(response.getSuccess())) {
if (CollectionUtils.isEmpty(response.getValues())) {
return result;
}
result.addAll(response.getValues());
if (response.getValues().size() < pageQueryRequest.getPageSize()){
return result;
}
pageQueryRequest.setPageIndex(pageQueryRequest.getPageIndex() + NumberConstant.ONE);
continue;
}
log.error("SignResourceRpc#queryAllSigningVendorIds] FAIL, request:{}, response:{}",
JSON.toJSONString(signUpStatusQueryReq), JSON.toJSONString(response));
throw new RuntimeException("查询所有在约的商家Id集合--接口数据异常");
}
} catch (Exception e) {
log.error("SignResourceRpc#queryAllSigningVendorIds] ERROR, request:{}， e:{}",
JSON.toJSONString(signUpStatusQueryReq), ExceptionUtils.getStackTrace(e));
throw new RuntimeException("查询在约的商家Id集合-接口调用异常");
}
}
3、数据库多次查询(分页)
3.1、偏移量查询
public List<Route> selectByExample(RouteQuery query) {
List<Route> result = new ArrayList<>();
int pageNo = NumberConstant.ONE;
int pageSize = NumberConstant.TEN_THOUSAND;
while (true) {
long start = System.currentTimeMillis();
int offset = (pageNo - NumberConstant.ONE) * pageSize;
query.setOffset(offset);
query.setPageSize(pageSize);
List<Route> list = routeMapper.selectByExample(query);
if (CollectionUtils.isEmpty(list)) {
return result;
}
pageNo = pageNo + 1;
result.addAll(list);
if (list.size() < pageSize){
return result;
}
}
}
3.3、PageHelper
/**
* queryAll
*
* @param function function
* @param q q
* @param pageSize pageSize
* @return {@link List<R>}
*/
public static <Q, R> List<R> queryAll(Function<Q, List<R>> function, Q q, int pageSize) {
List<R> dbList = Lists.newArrayList();
PageInfo<R> pageInfo;
int pageNow = 1;
while (true) {
PageHelper.startPage(pageNow, pageSize);
pageInfo = new PageInfo<>(function.apply(q));
if (CollectionUtils.isEmpty(pageInfo.getList())) {
break;
}
dbList.addAll(pageInfo.getList());
pageNow = pageNow + 1;
}
return dbList;
}
4、Java 分页
4.1、简单分页
package com.hlj.util.z035_分页;
import com.google.common.collect.Lists;
import lombok.Data;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import java.util.List;
import java.util.stream.Collectors;
/**
* java分页工具
* @author zhangyujin
* @date 2021/11/5 9:10 上午.
*/
@Slf4j
public class JavaPageUtils {
public static void main(String[] args) {
int pageNow = 3;
int pageSize = 2;
PageDto<String> page = toPageDto(Lists.newArrayList("1", "2", "3", "4", "5"), pageNow, pageSize);
log.info("toPageDto:{}", page);
}
public static <T> PageDto<T> toPageDto(List<T> list, Integer pageNow, Integer pageSize) {
if (list == null || list.isEmpty()){
return new PageDto<>(null, pageNow, pageSize, 0, 0);
}
Integer totalCount = list.size();
Integer pageCount;
if (totalCount % pageSize == 0) {
pageCount = totalCount / pageSize;
} else {
pageCount = totalCount / pageSize + 1;
}
List<T> pageList = list.stream().skip((long) (pageNow - 1) * pageSize).limit(pageSize).collect(Collectors.toList());
return new PageDto<>(pageList, pageNow, pageSize, totalCount, pageCount);
}
@Data
@Accessors(chain = true)
public static class PageDto<T> {
private List<T> datas;
/**
* 当前页码数
*/
private Integer pageNow;
/**
* 每页显示的记录数
*/
private Integer pageSize;
/**
* 总记录数
*/
private Integer totalCount;
/**
* 一共多少页
*/
private Integer pageCount;
public PageDto(List<T> datas, Integer pageNow, Integer pageSize, Integer totalCount, Integer pageCount) {
this.datas = datas;
this.pageNow = pageNow;
this.pageSize = pageSize;
this.totalCount = totalCount;
this.pageCount = pageCount;
}
private PageDto(List<T> datas) {
this.datas = datas;
}
private PageDto() {
}
}
}
4.1、匹配分页
package com.hlj.util.z035_分页;
import com.google.common.collect.Lists;
import lombok.Data;
import lombok.ToString;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Comparator;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* Stream 流分页
* @author zhangyujin
* @date 2023/5/30 11:51.
*/
@Slf4j
public class StreamPageUtils {
public static void main(String[] args) {
DemoDTO demo1 = new DemoDTO()
.setPrice(new BigDecimal("1"))
.setSkuName("大疆飞机V1")
.setOrderId("111")
.setCreateTime(LocalDateTime.now().minusDays(1));
DemoDTO demo2 = new DemoDTO()
.setPrice(new BigDecimal("1"))
.setSkuName("大疆飞机V2")
.setOrderId("222")
.setCreateTime(LocalDateTime.now().minusDays(1));
DemoDTO demo3 = new DemoDTO()
.setPrice(new BigDecimal("1"))
.setSkuName("大疆飞机V3")
.setOrderId("333")
.setCreateTime(LocalDateTime.now().minusDays(1));
DemoDTO demo4 = new DemoDTO()
.setPrice(new BigDecimal("1"))
.setSkuName("大疆飞机V4")
.setOrderId("444")
.setCreateTime(LocalDateTime.now().minusDays(1));
DemoDTO demo5 = new DemoDTO()
.setPrice(new BigDecimal("1"))
.setSkuName("大疆飞机V5")
.setOrderId("555")
.setCreateTime(LocalDateTime.now().minusDays(1));
List<DemoDTO> list = Lists.newArrayList(demo1,demo2,demo3,demo4,demo5);
DemoQuery query = new DemoQuery().setOrderId("111");
PageDto<DemoDTO> page = toPageDto(query, list, 1, 1);
log.info("page:{}", page);
query = new DemoQuery().setSkuName("大疆飞机");
page = toPageDto(query, list, 2, 3);
log.info("page:{}", page);
}
public static PageDto<DemoDTO> toPageDto(DemoQuery query,
List<DemoDTO> list,
Integer pageNow,
Integer pageSize) {
if (list == null || list.isEmpty()){
return new PageDto<>(null, pageNow, pageSize, 0, 0);
}
Stream<DemoDTO> stream = list.stream();
if (Objects.nonNull(query.getStartTime()) ) {
stream = stream.filter(item->query.getStartTime().compareTo(item.getCreateTime()) <= 0);
}
if (Objects.nonNull(query.getEndTime()) ) {
stream = stream.filter(item->query.getEndTime().compareTo(item.getCreateTime()) >= 0);
}
if (StringUtils.isNotBlank(query.getOrderId()) ) {
stream = stream.filter(item->StringUtils.equals(query.getOrderId(), item.getOrderId()));
}
if (StringUtils.isNotBlank(query.getSkuName()) ) {
stream = stream.filter(item-> item.getSkuName().contains(query.getSkuName()));
}
List<DemoDTO> matchList = stream
.sorted(Comparator.comparing(DemoDTO::getCreateTime).reversed())
.collect(Collectors.toList());
Integer totalCount = matchList.size();
Integer pageCount;
if (totalCount % pageSize == 0) {
pageCount = totalCount / pageSize;
} else {
pageCount = totalCount / pageSize + 1;
}
List<DemoDTO> pageList = matchList.stream()
.skip((long) (pageNow - 1) * pageSize)
.limit(pageSize)
.collect(Collectors.toList());
return new PageDto<>(pageList, pageNow, pageSize, totalCount, pageCount);
}
/**
* @author zhangyujin
* @date 2023/5/30 13:06.
*/
@ToString
@Accessors(chain = true)
@Data
public static class DemoDTO {
private String orderId;
private String skuName;
private BigDecimal price;
private LocalDateTime createTime;
}
/**
* @author zhangyujin
* @date 2023/5/30 13:06.
*/
@ToString
@Accessors(chain = true)
@Data
public static class DemoQuery {
private String orderId;
private String skuName;
private BigDecimal price;
private LocalDateTime startTime;
private LocalDateTime endTime;
}
@ToString
@Accessors(chain = true)
public static class PageDto<T> {
private List<T> datas;
/**
* 当前页码数
*/
private Integer pageNow;
/**
* 每页显示的记录数
*/
private Integer pageSize;
/**
* 总记录数
*/
private Integer totalCount;
/**
* 一共多少页
*/
private Integer pageCount;
public PageDto(List<T> datas, Integer pageNow, Integer pageSize, Integer totalCount, Integer pageCount) {
this.datas = datas;
this.pageNow = pageNow;
this.pageSize = pageSize;
this.totalCount = totalCount;
this.pageCount = pageCount;
}
private PageDto(List<T> datas) {
this.datas = datas;
}
private PageDto() {
}
}
}
5、工具
/**
* userDemoManager
*/
@Resource
private UserDemoManager userDemoManager;
/**
* 大数据量-分页查询全部
*
* @param queryBo queryBo
* @return List<UserDemoBO>
*/
@Override
public List<UserDemoBO> queryAllUserDemoByLimit(UserDemoQueryBO queryBo) {
return BatchQueryUtils.queryAllByLimit(p -> userDemoManager.queryUserDemoPage(p), queryBo, 1000L);
}
/**
* 大数据量-IdSize查询全部
*
* @param queryBo queryBo
* @return List<UserDemoBO>
*/
@Override
public List<UserDemoBO> queryAllUserDemoByIdSize(UserDemoQueryBO queryBo) {
IdQueryBO idQueryBO = new IdQueryBO(0L, 2L);
List<UserDemo> list = BatchQueryUtils.queryAllByIdSize(
(p, q) -> userDemoManager.queryUserDemoByIdSize(p, q),
queryBo,
idQueryBO);
return UserDemoConverter.INSTANCE.covertUserDemoPoToBoList(list);
}
/**
* 大数据量-Id区间查询全部
*
* @param queryBo queryBo
* @return List<UserDemoBO>
*/
@Override
public List<UserDemoBO> queryAllUserDemoByIdSub(UserDemoQueryBO queryBo) {
ImmutablePair<Long, Long> minAndMaxId = userDemoManager.queryMinAndMaxId(queryBo);
Long minId = minAndMaxId.getLeft();
Long maxId = minAndMaxId.getRight();
IdQueryBO idQueryBO = new IdQueryBO(minId, maxId, 2L);
List<UserDemo> list = BatchQueryUtils.queryAllByIdSub(
(p, q) -> userDemoManager.queryUserDemoByIdSub(p, q),
queryBo,
idQueryBO);
return UserDemoConverter.INSTANCE.covertUserDemoPoToBoList(list);
}
/**
* 大数据量-线程池limit查询
*
* @param completionService completionService
* @param query query
* @return List<Future < List < UserDemoExcel>>>
*/
@Override
public List<Future<List<UserDemoBO>>> queryAllUserDemoByPoolLimit(CompletionService<List<UserDemoBO>> completionService, UserDemoQueryBO query) {
return BatchQueryUtils.queryAllByPoolLimit(completionService, p -> userDemoManager.queryUserDemoPage(p), query, 1);
}
/**
* 大数据量-线程池根据Id区间查询
*
* @param completionService completionService
* @param query queryBO
* @return List<Future < List < UserDemoExcel>>>
*/
@Override
public List<Future<List<UserDemoBO>>> queryAllUserDemoByPoolIdSub(CompletionService<List<UserDemoBO>> completionService, UserDemoQueryBO query) {
ImmutablePair<Long, Long> minAndMaxId = userDemoManager.queryMinAndMaxId(query);
Long minId = minAndMaxId.getLeft();
Long maxId = minAndMaxId.getRight();
IdQueryBO idQueryBO = new IdQueryBO(minId, maxId, 2L);
return BatchQueryUtils.queryAllByPoolIdSub(completionService,
(p, q) -> userDemoManager.queryUserDemoByIdSub(p, q),
query,
idQueryBO,
UserDemoConverter.INSTANCE::covertUserDemoPoToBoList);
}
1、limit 分页查询
/**
* 大数据量-分页查询全部
*
* @return {@link List<R>}
*/
public static <R> List<R> queryAllByLimit(LongFunction<PageBO<R>> function) {
List<R> result = new ArrayList<>();
long currentPageNo = 1;
while (true) {
PageBO<R> pageBo = function.apply(currentPageNo++);
if (CollectionUtils.isEmpty(pageBo.getList())) {
break;
}
result.addAll(pageBo.getList());
}
return result;
}
2、IdSize 分页查询
/**
* 大数据量-IdSize查询全部
*
* @return {@link List<R>}
*/
public static <R> List<R> queryAllByIdSize(Function<IdQueryBO, List<R>> function, long pageSize) {
IdQueryBO idQuery = new IdQueryBO(0L, pageSize);
List<R> result = Lists.newArrayList();
while (true) {
List<R> dbList = function.apply(idQuery);
if (CollectionUtils.isEmpty(dbList)) {
break;
}
idQuery.setMinId(idQuery.getMaxId());
result.addAll(dbList);
}
return result;
}
3、Id 区间查询全部
/**
* 大数据量-Id区间查询全部
*
* @return {@link List<R>}
*/
public static <Q, R> List<R> queryAllByIdSub(Function<IdQueryBO, List<R>> function,
IdQueryBO minMax) {
List<R> result = Lists.newArrayList();
Long minId = minMax.getMinId();
Long maxId = minMax.getMaxId();
Long size = minMax.getSize();
for (long i = minId; i <= maxId; i = i + size) {
long endId = Math.min(i + size, maxId);
boolean maxEqualFlag = endId == maxId;
IdQueryBO idQueryBO = new IdQueryBO(true, i, maxEqualFlag, endId, size);
List<R> dbList = function.apply(idQueryBO);
if (CollectionUtils.isEmpty(dbList)) {
break;
}
result.addAll(dbList);
if (maxEqualFlag) {
break;
}
}
return result;
}
4、线程池 limit 分页查询
/**
* 大数据量-线程池limit查询
*
* @param executorService executorService
* @param function function
* @param q q
* @param pageSize pageSize
*/
public static <Q, T> List<Future<List<T>>> queryAllByPoolLimit(CompletionService<List<T>> executorService,
Function<PageQueryBO<Q>, PageBO<T>> function,
Q q,
long pageSize) {
PageQueryBO<Q> pageQuery = new PageQueryBO<>(1L, 1L);
pageQuery.setData(q);
Long totalPage = function.apply(pageQuery).getTotalPage();
List<Future<List<T>>> result = new ArrayList<>();
for (long i = 1; i <= totalPage; i++) {
PageQueryBO<Q> poolPageQuery = new PageQueryBO<>(i, pageSize);
poolPageQuery.setData(q);
Future<List<T>> future = executorService.submit(() -> {
PageBO<T> pageBo = function.apply(poolPageQuery);
return pageBo.getList();
});
result.add(future);
}
return result;
}
5、线程池 Id 区间查询
/**
* 大数据量-线程池Id区间查询
*
* @param executorService 线程池
* @param function 分页函数
* @param query 查询条件
* @param minMax minMax 最小Id和最大Id
* @param coverFunction coverFunction 对象转化
*/
public static <Q, R, T> List<Future<List<T>>> queryAllByPoolIdSub(CompletionService<List<T>> executorService,
BiFunction<IdQueryBO, Q, List<R>> function,
Q query,
IdQueryBO minMax,
Function<List<R>, List<T>> coverFunction) {
Long minId = minMax.getMinId();
Long maxId = minMax.getMaxId();
Long size = minMax.getSize();
List<Future<List<T>>> result = new ArrayList<>();
for (long i = minId; i <= maxId; i = i + size) {
long endId = Math.min(i + size, maxId);
boolean maxEqualFlag = endId == maxId;
IdQueryBO idQueryBO = new IdQueryBO(true, i, maxEqualFlag, endId, size);
Future<List<T>> future = executorService.submit(() -> {
List<R> list = function.apply(idQueryBO, query);
return coverFunction.apply(list);
});
result.add(future);
if (maxEqualFlag) {
break;
}
}
return result;
}
