个人博客 个人博客

2020 记录精彩的程序人生

目录
springboot + springdata jpa 分页其他方法实现分页
/  

springboot + springdata jpa 分页其他方法实现分页

1.简单分页(jpa 默认Pageable分页)

Repository:

Page<DpOrder> findByOrderNoContainingAndCustomerNameContainingAllIgnoreCase(String orderNo, String customerName, Pageable pageable);  

Service:

Page<DpOrder> getDpOrders(String orderNo, String customerName, Integer pageIndex, Integer pageSize);

ServiceImpl:

Pageable pageable = new PageRequest(pageIndex, pageSize, new Sort(Direction.DESC, "orderNo"));
  return dpOrderRepo.findByOrderNoContainingAndCustomerNameContainingAllIgnoreCase(orderNo, customerName, pageable);

Controller:

@RequestMapping(value = "/dporders", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
public Page<DpOrder> getDpOrders(
  @RequestParam(value = "orderNo") String orderNo,
  @RequestParam(value = "customerName") String customerName,
  @RequestParam Integer pageIndex,
  @RequestParam Integer pageSize) {
return dpOrderService.getDpOrders(orderNo.trim(), customerName.trim(), pageIndex, pageSize);

2.较复杂的情况(多个表关联/或该对象是值对象)

Repository:

@Query(value = "xxxx limit :pageSize offset :pageOffset", nativeQuery = true)
List<Object[]> getGoodsReceiptRecordSumsWithPage(
	@Param(value = "customerName") String customerName,
	@Param(value = "pageSize") Integer pageSize,
	@Param(value = "pageOffset") Integer pageOffset);

@Query(value = "select count(*) from(xxxx) pageCount", nativeQuery = true)
public long getGoodsReceiptRecordSumCount(@Param(value = "customerName") String customerName);

Service:

Page<GoodsReceiptRecordSum> getAllGoodsReceiptRecordSumsWithPage(String customerName,Integer pageIndex,Integer pageSize);

ServiceImpl:

@Override
public Page<GoodsReceiptRecordSum> getAllGoodsReceiptRecordSumsWithPage(String customerName,Integer pageIndex,Integer pageSize) {
   Pageable pageable = new PageRequest(pageIndex, pageSize);
   List<GoodsReceiptRecordSum> goodsReceiptRecordSums = receiptRecordRepo.getAllGoodsReceiptRecordSumsWithPage(customerName,pageSize,pageIndex * pageSize)
	.stream().map(recordsum -> genGoodsReceiptRecordSum(recordsum)).collect(Collectors.toList());
   Long totalCount = receiptRecordRepo.getGoodsReceiptRecordSumCount(customerName);
  return new PageImpl<GoodsReceiptRecordSum>(goodsReceiptRecordSums, pageable, totalCount);
}

private GoodsReceiptRecordSum genGoodsReceiptRecordSum(Object[] obj) {
   GoodsReceiptRecordSum grr = new GoodsReceiptRecordSum();
   if (grr != null) {
	grr.setCustomerNo(obj[0].toString());
	grr.setCustomerName(obj[1].toString());
	grr.setActualAmountSum(Float.parseFloat(obj[2].toString()));
	grr.setPaidAmountSum(Float.parseFloat(obj[3].toString()));
	grr.setUnpaidAmountSum(Float.parseFloat(obj[4].toString()));
	grr.setUntransProdQty(Integer.parseInt(obj[5].toString()));
   }
   return grr;
}

3.更复杂的情况(根据订单号查询分页)

Repository:

@Query(value = "xxx limit :pageSize offset :pageOffset", nativeQuery = true)
List<String> findGoodsReceiptRecordNosByPage(@Param(value = "customerName") String customerName,
  @Param(value = "orderNo") String orderNo,@Param(value = "beginDate") Date beginDate,
  @Param(value = "endDate") Date endDate,Param(value = "pageSize") Integer pageSize,@Param(value = "pageOffset") Integer pageOffset);

List<GoodsReceiptRecord> findByOrderNoIn(Set<String> orderNos);

@Query(value = "select count(*) from ( xxx ) pageCount", nativeQuery = true)
long findGoodsReceiptRecordDetailsCount(@Param(value = "customerName") String customerName,
 @Param(value = "orderNo") String orderNo,@Param(value = "beginDate") Date beginDate,
 @Param(value = "endDate") Date endDate);

Service:

Page<GoodsReceiptRecord> getGoodsReceiptRecordDetailsByPage(String customerName,String orderNo,
  String beginDate, String endDate, Integer pageIndex, Integer pageSize);

ServiceImpl:

@Override
public Page<GoodsReceiptRecord> getGoodsReceiptRecordDetailsByPage(String customerName,String orderNo,
  String beginDate, String endDate, Integer pageIndex, Integer pageSize) {
    if (beginDate == null || beginDate.equals("")) {
	beginDate = "1900-1-1";
    }
    if (endDate == null || endDate.equals("")) {
	endDate = "9999-12-31";
    }
    Pageable pageable = new PageRequest(pageIndex, pageSize);
    List<String> orderNos = receiptRecordRepo.findGoodsReceiptRecordNosByPage(customerName, orderNo, 
      DateFormat.getFormatDate("yyyy-MM-dd HH:mm:ss", beginDate + " 00:00:00"),
      DateFormat.getFormatDate("yyyy-MM-dd HH:mm:ss", endDate + " 23:59:59"),pageSize, pageSize * pageIndex);
    List<GoodsReceiptRecord> receiptRecordWithDetailsByPage = receiptRecordRepo.findByOrderNoIn(orderNos);
    long totalCount = receiptRecordRepo.findGoodsReceiptRecordDetailsCount(customerName, orderNo,
      DateFormat.getFormatDate("yyyy-MM-dd HH:mm:ss", beginDate + " 00:00:00"),
      DateFormat.getFormatDate("yyyy-MM-dd HH:mm:ss", endDate + " 23:59:59"));
    return new PageImpl<GoodsReceiptRecord>(receiptRecordWithDetailsByPage, pageable, totalCount);
}