[Spring] Spring Data JPA PageRquest 관련 에러

Page<PostInterface> findAllPostByCategory(PageRequest pageRequest);

Page<PostInterface> 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

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<PostInterface> 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<PostInterface> findAllPostByCategory(Long categoryId, PageRequest pageRequest);

 

변경 후 코드

 

@Transactional(readOnly = true)
public PostsDto readAllPost(Long categoryId, int page, int size) {
    Pageable pageable = PageRequest.of(page, size);
    Page<PostInterface> 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<PostInterface> findAllPostByCategory(Long categoryId, Pageable pageable);

 

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

 

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

 

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

 

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