先看需求
一共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](https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination
)