[Spring] Spring Data JPA PageRquest 관련 에러

[Spring] Spring Data JPA PageRquest 관련 에러

Page findAllPostByCategory(PageRequest pageRequest); Page findAllPostByCategory(Long categoryId, PageRequest pageRequest);

위처럼 파라미터가 PageRquest 하나였을 땐, 잘 실행되다가

Long categoryId가 파라미터로 추가되면서,

but parameter 'Optional[pageRequest]' not found in annotated query ~

java.lang.IllegalStateException: Using named parameters for method public abstract ~

같은 에러들이 발생했습니다..

아래쪽 Illegal 에러로 검색해본 결과, @Param 어노테이션과 관련된 이슈들이 많이 보여서

위쪽 에러로 검색을 해봤습니다.

https://stackoverflow.com/questions/54620809/how-to-get-pagerequest-to-work-in-spring-boot-with-annotated-query

stackoverflow 에서 비슷한 질문을 찾게 됐고, PageRequest 대신 Pageable 객체를 쓰라는 거였는데,

결과적으로 잘 해결이 됐습니다.

변경 전 코드

@Transactional(readOnly = true) public PostsDto readAllPost(Long categoryId, int page, int size) { PageRequest pageRequest = PageRequest.of(page, size); Page result = postRepository.findAllPostByCategory(categoryId, pageRequest); return PostsDto.builder() .pages(result.getTotalPages()) .count(result.getTotalElements()) .posts(result.stream().map(PostDto::new).collect(Collectors.toList())) .build(); } @Query(value = "SELECT c.name as category, p.id, p.title, p.views, p.create_date as createDate, u.nickname as writer, count(r.post_id) as likes " + "FROM post as p " + "LEFT JOIN category as c " + "ON p.category_id = c.id " + "LEFT JOIN user as u " + "ON p.user_id = u.id " + "LEFT JOIN recommendation as r " + "ON p.id = r.post_id " + "WHERE (:categoryId % 10 != 0 AND p.category_id = :categoryId) " + "OR (:categoryId % 10 = 0 AND p.category_id < :categoryId + 10 AND p.category_id > :categoryId) " + "GROUP BY p.id, p.title " + "ORDER BY p.id DESC", countQuery = "SELECT * FROM post as p " + "WHERE (:categoryId % 10 != 0 AND p.category_id = :categoryId) " + "OR (:categoryId % 10 = 0 AND :categoryId + 10 > p.category_id AND p.category_id > :categoryId)", nativeQuery = true) Page findAllPostByCategory(Long categoryId, PageRequest pageRequest);

변경 후 코드

@Transactional(readOnly = true) public PostsDto readAllPost(Long categoryId, int page, int size) { Pageable pageable = PageRequest.of(page, size); Page result = postRepository.findAllPostByCategory(categoryId, pageable); return PostsDto.builder() .pages(result.getTotalPages()) .count(result.getTotalElements()) .posts(result.stream().map(PostDto::new).collect(Collectors.toList())) .build(); } @Query(value = "SELECT c.name as category, p.id, p.title, p.views, p.create_date as createDate, u.nickname as writer, count(r.post_id) as likes " + "FROM post as p " + "LEFT JOIN category as c " + "ON p.category_id = c.id " + "LEFT JOIN user as u " + "ON p.user_id = u.id " + "LEFT JOIN recommendation as r " + "ON p.id = r.post_id " + "WHERE (:categoryId % 10 != 0 AND p.category_id = :categoryId) " + "OR (:categoryId % 10 = 0 AND p.category_id < :categoryId + 10 AND p.category_id > :categoryId) " + "GROUP BY p.id, p.title " + "ORDER BY p.id DESC", countQuery = "SELECT * FROM post as p " + "WHERE (:categoryId % 10 != 0 AND p.category_id = :categoryId) " + "OR (:categoryId % 10 = 0 AND :categoryId + 10 > p.category_id AND p.category_id > :categoryId)", nativeQuery = true) Page findAllPostByCategory(Long categoryId, Pageable pageable);

저의 경우 Long categoryId에 @Param 어노테이션을 붙여도 해결되지 않았는데,

제 생각에는 Native Query를 쓰면서 categoryId는 SQL문안의 파라미터로 사용됐는데,

PageRequest는 SQL문안에 쓰이는 파라미터가 아니고 Pagination을 위한 객체다 보니,

모종의(?) 충돌이 생기지 않았나 생각이 듭니다.

from http://hackids.tistory.com/130 by ccl(A) rewrite - 2021-12-30 18:01:11