完美的 jpa 多表 原生sql 分页查询

多表分页查询,强烈推荐原生sql

一般需要两个方法，一个查条数，一个查当前页数据

    @Query(value = "" +
            "" +
            " select " +
            "a.id," +
            "a.title," +
            "a.article_abstract," +
            "a.author_id," +
            "a.like_count ," +
            "a.read_count," +
            "a.tags," +
            "a.create_time," +
            "a.update_time," +
            "a.type," +
            "a.`status` " +
            " from re_article_category rac " +
            "" +
            " left join  " +
            " article a " +
            "" +
            " on a.id=rac.article_id " +
            "" +
            " where rac.category_id= :catId" +
            " and a.status = :status limit :start,:size",nativeQuery = true)
    List<Object[]> pageByCatId(@Param("catId") Integer catId,@Param("status")String status,@Param("start")long start, @Param("size")int size);

   @Query(value = "select" +
           " count(a.id)" +
           " from re_article_category rac" +
           "" +
           " left join " +
           " article a" +
           "" +
           " on a.id=rac.article_id" +
           "" +
           " where rac.category_id= :catId" +
           " and a.status= :status",nativeQuery = true)
    Integer countByCat(@Param("catId") Integer catId,@Param("status") String status);

service 层转换：

    //根据分类获取所有文章
    @Override
    public Result getPageByCatId(Integer catId, Integer limit, Integer pageNo) {
   

  PageRequest pageRequest = PageRequest.of(pageNo, limit);//借助计算起始位置
        int total=blogDao.countByCat(catId,BLOG_STATUS_NORMAL);// 计算数据总条数
        List<Object[]> records=blogDao.pageByCatId(catId,BLOG_STATUS_NORMAL,pageRequest.getOffset(),pageRequest.</