jpa 多表嵌套+nativeQuery + Page分页

最新推荐文章于 2023-03-31 22:27:51 发布

weixin_33895657

最新推荐文章于 2023-03-31 22:27:51 发布

阅读量723

点赞数

文章标签： java 数据库

原文链接：https://juejin.im/post/5d02778e6fb9a07ed740794d

版权

先看需求

一共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；