JPA 多表嵌套+nativeQuery + Page分页

先看需求

 

一共5个区域，分别对应5张表，作者表、文章列表、点赞记录表、评论表、阅读记录表

sql

写的有点水，有点渣。这里查询两次点赞记录表，要查看当前登录人是否点过赞

 SELECT  ar.id, ar.author, su.avatar,ar.user_id as userId, ar.title, ar.image_url as imageUrl, ar.create_date as createDate, ar.lastmodified_time as lastmodifiedTime,
		 ar.tag, ar.tag_id as tagId,ar.des,ar.status, arr.readTimes,alr.likeRecordTimes,alra.checklike,aco.commentTimes FROM article ar
	 LEFT JOIN (SELECT a.id,COUNT(rr.article) as readTimes FROM article a LEFT JOIN read_record rr ON rr.article = a.id GROUP BY a.id)  arr ON arr.id = ar.id
	LEFT JOIN (SELECT a.id,COUNT(lr.article) as likeRecordTimes FROM article a LEFT JOIN like_record lr ON lr.article = a.id GROUP BY a.id  ) alr ON alr.id = ar.id
	LEFT JOIN (SELECT a.id,lr.username as checklike FROM article a LEFT JOIN like_record lr ON lr.article = a.id and lr.username ='admin' ) alra ON alra.id = ar.id
	LEFT JOIN ( SELECT a.id,COUNT(co.article) as commentTimes FROM article a  LEFT JOIN comment co ON co.article = a.id GROUP BY a.id ) aco ON aco.id = ar.id 
	LEFT JOIN  sys_user su ON su.id = ar.user_id 
复制代码

jpa 实现

    @Query(value = " SELECT  ar.id, ar.author, su.avatar,ar.user_id as userId, ar.title, ar.image_url as imageUrl, ar.create_date as createDate, ar.lastmodified_time as lastmodifiedTime, " +
            "  ar.tag, ar.tag_id as tagId,ar.des,ar.status, arr.readTimes,alr.likeRecordTimes,alra.checklike,aco.commentTimes FROM article ar " +
            " LEFT JOIN (SELECT a.id,COUNT(rr.article) as readTimes FROM article a LEFT JOIN read_record rr ON rr.article = a.id GROUP BY a.id)  arr ON arr.id = ar.id " +
            " LEFT JOIN (SELECT a.id,COUNT(lr.article) as likeRecordTimes FROM article a LEFT JOIN like_record lr ON lr.article = a.id GROUP BY a.id  ) alr ON alr.id = ar.id  " +
            " LEFT JOIN (SELECT a.id,lr.username as checklike FROM article a LEFT JOIN like_record lr ON lr.article = a.id and lr.username =:username ) alra ON alra.id = ar.id " +
            " LEFT JOIN ( SELECT a.id,COUNT(co.article) as commentTimes FROM article a  LEFT JOIN comment co ON co.article = a.id GROUP BY a.id ) aco ON aco.id = ar.id " +
            " LEFT JOIN  sys_user su ON su.id = ar.user_id WHERE ar.status =:status and ar.tag_id =:tagId ",
            countQuery = "SELECT count(*) FROM article",
            nativeQuery = true)
    Page<Map<String, Object>> findByStatusAllAndTagId(@Param("username") String username, @Param("status") int status, @Param("tagId") int tagId, Pageable pageable);
复制代码

调用, 这里使用多字段排序

Sort sort = new Sort(Sort.Direction.DESC, "readTimes").and(new Sort(Sort.Direction.DESC, "create_date"));
PageRequest pageable = PageRequest.of(pageNo, pageSize, sort);
articleDao.findByStatusAllAndTagId(username, status, tagId, pageable);
复制代码

• 这里的关键点

1、Page<> 里面要使用Map<String, Object> 否者查询到的是结果集

2、如果不需要分页的话或者不使page最为分页的集合（不需要统计总数），可以不需要加countQuery，如果需要使用Page，这个必须带上，里面的sql结果是你的数据数量，我这里的value 结果 数量与article 是一样的，所以直接查询的这个表，如果和我需求不一样，请直接使用value的count；

3、最后就是 参数的顺序最好跟sql里面保持一致 在jpa官方文章用的是数字 1、2、3来代替

在弄这个的时候一度想要放弃jpa改用mybatis:sweat_smile:,最后在Stack Overflow找到了问题所在

1、Spring Data and Native Query with pagination

2、jpa 官网文档，坑太多

个人站点