Look at the requirements

A total of 5 areas, respectively corresponding to 5 tables, author table, article list, like record table, comment table, reading record table

sql

A little bit of water, a little bit of slag. Here query the likes record table twice, to check whether the current login person has clicked the likes

 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 
Copy the code

The jpa implementation

    @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);
Copy the code

Call, where multi-field sorting is used

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);
Copy the code
  • The key point here

1, Page<> <String, Object> if not, query result set

CountQuery is not required if you do not need pagination or if you do not need to count the total number of pages. If you need to use page, you must add countQuery. Therefore, if the table directly queried is different from my requirements, please directly use the count of value;

3. Finally, the order of parameters had better be consistent with SQL. Jpa official article uses numbers 1, 2, 3 instead

I was thinking of abandoning JPA and switching to Mybatis: Sweat_smile: when I was working on this, I found the problem on Stack Overflow

1. Spring Data and Native Query with pagination

2. Jpa official website documents have too many pits

Personal site