Spring Data JPA : Sort 시 내부함수 호출

Sort 방법에 제약사항이 있다.

• Sort 객체의 매개 값으로 오는 것은 Property 명, 또는 Alias 여야 할 것.

public interface CommentRepository extends JpaRepository<Comment,Long> {

    List<Comment> findByTitle(String titile, Sort sort);
}

@Test
    public void testSort(){
        Comment comment = new Comment();
        comment.setTitle("Spring?");
        comment.setComment("Data JPA");

        Comment savedComment = commentRepository.save(comment);

        List<Comment> sortedComment = commentRepository.findByTitle("Spring?", Sort.by(Sort.Direction.DESC, "title"));

        assertThat(sortedComment.size()).isEqualTo(1);
        sortedComment.forEach(System.out::println);
    }

Alias 일 경우

public interface CommentRepository extends JpaRepository<Comment,Long> {

    @Query("SELECT c, c.title as cTitle FROM Comment as c WHERE c.title = ?1")
    List<Comment> findByTitle(String titile, Sort sort);
}

@Test
    public void testSort1(){
        Comment comment = new Comment();
        comment.setTitle("Spring?");
        comment.setComment("Data JPA");

        Comment savedComment = commentRepository.save(comment);

        List<Comment> sortedComment = commentRepository.findByTitle("Spring?", Sort.by(Sort.Direction.DESC, "cTitle"));

        assertThat(sortedComment.size()).isEqualTo(1);
        sortedComment.forEach(System.out::println);
    }
    
    Hibernate: 
    select
        comment0_.id as col_0_0_,
        comment0_.title as col_1_0_,
        comment0_.id as id1_0_,
        comment0_.comment as comment2_0_,
        comment0_.title as title3_0_ 
    from
        comment comment0_ 
    where
        comment0_.title=? 
    order by
        col_1_0_ desc

---

내부 함수를 쓸 수는 없을까

• Sort 대신 JpaSort.unsafe() 를 호출하면 가능.

메서드 명 그대로 safe 하지가 않다. 매개 값인 String 을 Order by 절에 추가하는 걸로 보여 잘 적어줘야할 듯 하다.

@Test
    public void testSort2(){
        Comment comment = new Comment();
        comment.setTitle("Spring?");
        comment.setComment("Data JPA");

        Comment savedComment = commentRepository.save(comment);

        List<Comment> sortedComment = commentRepository.findByTitle("Spring?", JpaSort.unsafe(Sort.Direction.DESC, "UPPER(title)"));

        assertThat(sortedComment.size()).isEqualTo(1);
        sortedComment.forEach(System.out::println);
    }
    
    
    Hibernate: 
    select
        comment0_.id as col_0_0_,
        comment0_.title as col_1_0_,
        comment0_.id as id1_0_,
        comment0_.comment as comment2_0_,
        comment0_.title as title3_0_ 
    from
        comment comment0_ 
    where
        comment0_.title=? 
    order by
        upper(comment0_.title) desc
2020-11-18 20:03:08.572 TRACE 3748 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Spring?]
2020-11-18 20:03:08.577 TRACE 3748 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_0_] : [BIGINT]) - [1]
2020-11-18 20:03:08.577 TRACE 3748 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [BIGINT]) - [1]
2020-11-18 20:03:08.592 TRACE 3748 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_1_0_] : [VARCHAR]) - [Spring?]
Comment{id=1, title='Spring?', comment='Data JPA'}