记一次SpringDateJpa 使用原生sql 分页查询

最新推荐文章于 2024-08-21 10:26:47 发布

苏察哈尔菜

最新推荐文章于 2024-08-21 10:26:47 发布

阅读量747

点赞数 1

文章标签： java spring

本文链接：https://blog.csdn.net/qq_31486829/article/details/123972752

版权

本文展示了如何在Java中利用JPA的@Query注解执行原生SQL查询并实现分页。通过提供一个具体的查询示例，开发者可以了解如何结合Pageable接口来获取指定时间段内的数据，并按成员ID分组，同时排除特定商店ID。注意，为了进行分页，还需要提供一个对应的countQuery来计算总记录数。

摘要由CSDN通过智能技术生成

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 ,快去试一试吧！