jpa 如果使用原生sql如何分页查询呢?
@GetMapping("/getInfo")
public Result getOpenId() throws Exception{
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date start = simpleDateFormat.parse("2022-03-26 00:00:00");
Date end = simpleDateFormat.parse("2022-03-26 24:59:59");
String[] shopIds = {"LF5P","LFLI"};
int pageSize = 100;
Pageable pageable = new PageRequest(0, pageSize);
Page<UniOrderDescEntity> pageList = uniOrderDescDao.findInfoByTime(start,end,shopIds,pageable);
log.info("查询到的数据:{}",JSONObject.toJSONString(pageList));
return Result.success(true);
}
/***
* 根据时间分页查询数据
* @param startTime
* @param endTime
* @return
*/
// group by u.memberId
@Query( nativeQuery =true ,value = "select d.*,o.shop_id from uni_order_desc as d,uni_order as o" +
" where o.uni_ord_id = d.uni_order_id and o.shop_id not in (:shopIds) " +
" and d.last_sync_time between :startTime and :endTime group by d.member_id ",
countQuery = "select count(1) from uni_order_desc as d,uni_order as o" +
" where o.uni_ord_id = d.uni_order_id and o.shop_id not in (:shopIds) " +
" and d.last_sync_time between :startTime and :endTime group by d.member_id "
)
Page<UniOrderDescEntity> findInfoByTime(@Param("startTime") Date startTime, @Param("endTime") Date endTime,@Param("shopIds") String[] shopIds, Pageable pageable);
使用原生sql分页查询的时候需要添加countQuery 查询数量,nativeQuery =true 使用原生sql ,快去试一试吧!