JPQL → NativeQuery 속도 개선

필요한 컬럼만 가져오기 (생각)

리팩토링 전 코드

베스트셀러 구하는 쿼리

@Query("SELECT b,a FROM BookHistory bh JOIN bh.book b join bh.book.author a GROUP BY b.id ORDER BY COUNT(bh.id) DESC")
     List<Book> findBooksByHistory();

주간 베스트 셀러 구하는 쿼리

 @Query("SELECT b,a FROM BookHistory bh JOIN bh.book b join bh.book.author a WHERE bh.createdAt >= :startOfWeek AND bh.createdAt <= :endOfWeek GROUP BY b.id ORDER BY COUNT(bh.id) DESC")
     List<Book> findWeekBestSellers(@Param("startOfWeek") LocalDateTime startOfWeek, @Param("endOfWeek") LocalDateTime endOfWeek);

일별 베스트 셀러 구하는 쿼리

@Query("SELECT b FROM BookHistory bh JOIN bh.book b JOIN fetch bh.book.author a WHERE bh.createdAt >= :startOfDay AND bh.createdAt <= :endOfDay GROUP BY b.id ORDER BY COUNT(bh.id) DESC")
     Page<Book> findTopDayBestSeller(@Param("startOfDay") LocalDateTime startOfDay,
                                     @Param("endOfDay") LocalDateTime endOfDay,
                                     Pageable pageable);

이어보기 구하는 쿼리

@Query("SELECT bh FROM BookHistory bh JOIN FETCH bh.book b JOIN FETCH bh.user u WHERE u.id = :userId")
     List<BookHistory> findBookHistoryByUserId(@Param("userId") Integer userId);

필요한 컬럼만 가져오기 (적용)

1.해결방법 (JPA 문법 오류)

2.해결방법(DTO 프로젝션 오류)

3.해결방법(인터페이스 프로젝션 완료)

리팩토링 후 코드

베스트셀러 구하는 쿼리

@Query(value = """
         SELECT 
             b.id AS id,
             b.path AS bookImagePath,
             b.title AS bookTitle,
             a.name AS author,
             COUNT(bh.id) AS readCount
         FROM book_history_tb bh
         JOIN book_tb b ON bh.book_id = b.id
         JOIN author_tb a ON b.author_id = a.id
         GROUP BY b.id, b.path, b.title, a.name
         ORDER BY readCount DESC
     """, nativeQuery = true)
     List<BestSellerProjection> findBestSellers();

주간 베스트 셀러 구하는 쿼리

@Query(value = """
         SELECT 
             b.id AS id,
             b.path AS bookImagePath,
             b.title AS bookTitle,
             a.name AS author,
             COUNT(bh.id) AS readCount
         FROM book_history_tb bh
         JOIN book_tb b ON bh.book_id = b.id
         JOIN author_tb a ON b.author_id = a.id
         WHERE bh.created_at BETWEEN :startOfWeek AND :endOfWeek
         GROUP BY b.id, b.path, b.title, a.name
         ORDER BY readCount DESC
         """, nativeQuery = true)
     List<WeekBestSellerProjection> findWeekBestSellersNative(@Param("startOfWeek") LocalDateTime startOfWeek, @Param("endOfWeek") LocalDateTime endOfWeek);

일별 베스트 셀러 구하는 쿼리

@Query(value = """
         SELECT 
             b.id AS id,
             b.title AS bookTitle,
             a.name AS author,
             b.book_intro AS bookIntro,
             b.path AS bookImagePath
         FROM book_history_tb bh
         JOIN book_tb b ON bh.book_id = b.id
         JOIN author_tb a ON b.author_id = a.id
         WHERE bh.created_at BETWEEN :startOfDay AND :endOfDay
         GROUP BY b.id, b.title, a.name, b.book_intro, b.path
         ORDER BY COUNT(bh.id) DESC
         LIMIT 1
     """, nativeQuery = true)
     DayBestSellerProjection findDayBestSellerNative(@Param("startOfDay") LocalDateTime startOfDay,
                                                     @Param("endOfDay") LocalDateTime endOfDay);

이어보기 구하는 쿼리

@Query(value = """
     SELECT 
         bh.user_id AS userId,
         b.id AS bookId,
         b.title AS bookTitle,
         b.page_count AS pageCount,
         bh.last_read_page AS lastReadPage,
         b.path AS bookImagePath
     FROM book_history_tb bh
     JOIN book_tb b ON bh.book_id = b.id
     WHERE bh.user_id = :userId
     """, nativeQuery = true)
     List<BookHistoryProjection> findContinueReadingBooks(@Param("userId") Integer userId);

Interface

베스트셀러

 public interface BestSellerProjection { //"인터페이스 기반 Projection은 Native Query나 JPQL을 통해 조회 시,쿼리 결과가 getter 메서드 명과 자동 매핑된다."
     Integer getId();
     String getBookImagePath();
     String getBookTitle();
     String getAuthor();
     Long getReadCount();
 }

주간 베스트셀러

 public interface WeekBestSellerProjection {
     Integer getId();
     String getBookImagePath();
     String getBookTitle();
     String getAuthor();
     Long getReadCount();
 }

일간 베스트셀러

 public interface DayBestSellerProjection {
     Integer getId();
     String getBookTitle();
     String getAuthor();
     String getBookIntro();
     String getBookImagePath();
 }

이어보기

 public interface BookHistoryProjection {
     Integer getUserId();
     Integer getBookId();
     String getBookTitle();
     Integer getPageCount();
     Integer getLastReadPage();
     String getBookImagePath();
 }