springframework/Spring Data JPA

Spring Data Common : 쿼리 작성

Jungsoomin :) 2020. 11. 15. 22:53 
public interface CommentRepository  extends  MyRepository<Comment, Long>{

    List<Comment> findByCommentContains(String keyword); // comment 프로퍼티 기준으로 keyword 를 가졌는지 찾아보게 됨
}
@Test
    public void testQuery(){
        Comment comment = new Comment();
        comment.setComment("Spring Data JPA");
        commentRepository.save(comment);

        List<Comment> comments = commentRepository.findByCommentContains("spring");

        assertThat(comments.size()).isEqualTo(1);
    }

문제는 대소문자를 비교하고 있다는 것이다.

Hibernate: 
    select
        comment0_.id as id1_1_,
        comment0_.comment as comment2_1_,
        comment0_.like_count as like_cou3_1_,
        comment0_.post_id as post_id4_1_ 
    from
        comment comment0_ 
    where
        comment0_.comment like ? escape ?
2020-11-15 22:02:05.501 TRACE 2436 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%spring%]
2020-11-15 22:02:05.503 TRACE 2436 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [CHAR] - [\]


org.opentest4j.AssertionFailedError: 
Expecting:
 <0>
to be equal to:
 <1>
but was not.
Expected :1
Actual   :0

JPA 가 해석하도록 IgnoreCase 를 달아주면 UPPER 함수를 사용하여 찾게된다.

public interface CommentRepository  extends  MyRepository<Comment, Long>{

    List<Comment> findByCommentContainsIgnoreCase(String keyword);
}

@Test
    public void testQuery(){
        Comment comment = new Comment();
        comment.setComment("Spring Data JPA");
        commentRepository.save(comment);

        List<Comment> comments = commentRepository.findByCommentContainsIgnoreCase("spring");

        assertThat(comments.size()).isEqualTo(1);
    }
Hibernate: 
    select
        comment0_.id as id1_1_,
        comment0_.comment as comment2_1_,
        comment0_.like_count as like_cou3_1_,
        comment0_.post_id as post_id4_1_ 
    from
        comment comment0_ 
    where
        upper(comment0_.comment) like upper(?) escape ?
2020-11-15 22:05:55.417 TRACE 16204 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%spring%]
2020-11-15 22:05:55.417 TRACE 16204 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [CHAR] - [\]
2020-11-15 22:05:55.421 TRACE 16204 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_1_] : [BIGINT]) - [1]

And 나 Or 를 제시하여 WHERE 절에 추가조건을 기술할 수 있다.

public interface CommentRepository  extends  MyRepository<Comment, Long>{

    List<Comment> findByCommentContainsIgnoreCaseAndLikeCountGreaterThan(String keyword,int likeCount);
}

@Test
    public void testQuery(){
        Comment comment = new Comment();
        comment.setComment("Spring Data JPA");
        comment.setLikeCount(100);
        commentRepository.save(comment);

        List<Comment> comments = commentRepository.findByCommentContainsIgnoreCaseAndLikeCountGreaterThan("spring",10);

        assertThat(comments.size()).isEqualTo(1);
    }
Hibernate: 
    select
        comment0_.id as id1_1_,
        comment0_.comment as comment2_1_,
        comment0_.like_count as like_cou3_1_,
        comment0_.post_id as post_id4_1_ 
    from
        comment comment0_ 
    where
        (
            upper(comment0_.comment) like upper(?) escape ?
        ) 
        and comment0_.like_count>?
2020-11-15 22:11:06.478 TRACE 1476 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%spring%]
2020-11-15 22:11:06.484 TRACE 1476 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [CHAR] - [\]
2020-11-15 22:11:06.485 TRACE 1476 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [INTEGER] - [10]
2020-11-15 22:11:06.494 TRACE 1476 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_1_] : [BIGINT]) - [1]

OrderBy프로퍼티 로 Order By 절을 기술할 수 있다.

public interface CommentRepository  extends  MyRepository<Comment, Long>{

    List<Comment> findByCommentContainsIgnoreCaseOrderByLikeCountDesc(String keyword);
}

@Test
    @Rollback(false)
    public void testQuery(){
        this.createComment(100,"Spring Data JPA");
        this.createComment(55,"Hibernate Spring");

        List<Comment> comments = commentRepository.findByCommentContainsIgnoreCaseOrderByLikeCountDesc("spring");

        assertThat(comments).first().hasFieldOrPropertyWithValue("likeCount", 100);
        assertThat(comments).first().hasFieldOrPropertyWithValue("comment", "Spring Data JPA");
    }

    private void createComment(int likeCount, String comment){
        Comment newComment = new Comment();
        newComment.setLikeCount(likeCount);
        newComment.setComment(comment);
        commentRepository.save(newComment);
    }

Order By 절로 정렬을 하고 있음

Hibernate: 
    select
        comment0_.id as id1_1_,
        comment0_.comment as comment2_1_,
        comment0_.like_count as like_cou3_1_,
        comment0_.post_id as post_id4_1_ 
    from
        comment comment0_ 
    where
        upper(comment0_.comment) like upper(?) escape ? 
    order by
        comment0_.like_count desc
2020-11-15 22:20:00.708 TRACE 11452 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%spring%]
2020-11-15 22:20:00.709 TRACE 11452 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [CHAR] - [\]
2020-11-15 22:20:00.722 TRACE 11452 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_1_] : [BIGINT]) - [1]
2020-11-15 22:20:00.723 TRACE 11452 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_1_] : [BIGINT]) - [2]

동적인 페이징은 Pageable 을 사용하는 것이 좋다.

  • Page 는 PageRequest 의 팩토리 메서드 of 를 사용
  • Sort 는 by 를 사용하며 Direction의 상수 값으로 정렬 방식을 정하고, 정렬할 프로퍼티를 정한다.
public interface CommentRepository  extends  MyRepository<Comment, Long>{

    Page<Comment> findByCommentContainsIgnoreCase(String keyword, Pageable pageable);
}

@Test
    @Rollback(false)
    public void testQuery(){
        this.createComment(100,"Spring Data JPA");
        this.createComment(55,"Hibernate Spring");

        PageRequest pageRequest = PageRequest.of(0, 10, Sort.by(Sort.Direction.DESC, "likeCount"));

        Page<Comment> comments = commentRepository.findByCommentContainsIgnoreCase("spring",pageRequest);

        assertThat(comments.getNumberOfElements()).isEqualTo(2);
        assertThat(comments).first().hasFieldOrPropertyWithValue("likeCount", 100);
    }

    private void createComment(int likeCount, String comment){
        Comment newComment = new Comment();
        newComment.setLikeCount(likeCount);
        newComment.setComment(comment);
        commentRepository.save(newComment);
    }

동적으로 limit 함수 및 order by 연산으로 정렬을 하고 있다.

Hibernate: 
    select
        comment0_.id as id1_1_,
        comment0_.comment as comment2_1_,
        comment0_.like_count as like_cou3_1_,
        comment0_.post_id as post_id4_1_ 
    from
        comment comment0_ 
    where
        upper(comment0_.comment) like upper(?) escape ? 
    order by
        comment0_.like_count desc limit ?
2020-11-15 22:29:28.700 TRACE 1420 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%spring%]
2020-11-15 22:29:28.700 TRACE 1420 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [CHAR] - [\]
2020-11-15 22:29:28.705 TRACE 1420 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_1_] : [BIGINT]) - [1]
2020-11-15 22:29:28.706 TRACE 1420 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_1_] : [BIGINT]) - [2]

java.util.stream.Stream 으로 받을 수도 있음

try-with-resource 문으로 자원반납이 필요함

public interface CommentRepository  extends  MyRepository<Comment, Long>{

    Stream<Comment> findByCommentContainsIgnoreCase(String keyword, Pageable pageable);
}


@Test
    @Rollback(false)
    public void testQuery(){
        this.createComment(100,"Spring Data JPA");
        this.createComment(55,"Hibernate Spring");

        PageRequest pageRequest = PageRequest.of(0, 10, Sort.by(Sort.Direction.DESC, "likeCount"));

        try(Stream<Comment> comments = commentRepository.findByCommentContainsIgnoreCase("spring",pageRequest)){
            Comment firstComment = comments.findFirst().get();
            assertThat(firstComment).hasFieldOrPropertyWithValue("likeCount", 100);
        }catch (Exception e){
            e.printStackTrace();
        };

    }

    private void createComment(int likeCount, String comment){
        Comment newComment = new Comment();
        newComment.setLikeCount(likeCount);
        newComment.setComment(comment);
        commentRepository.save(newComment);
    }
Hibernate: 
    select
        comment0_.id as id1_1_,
        comment0_.comment as comment2_1_,
        comment0_.like_count as like_cou3_1_,
        comment0_.post_id as post_id4_1_ 
    from
        comment comment0_ 
    where
        upper(comment0_.comment) like upper(?) escape ? 
    order by
        comment0_.like_count desc limit ?
2020-11-15 22:34:10.884 TRACE 8056 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%spring%]
2020-11-15 22:34:10.885 TRACE 8056 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [CHAR] - [\]
2020-11-15 22:34:10.898 TRACE 8056 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_1_] : [BIGINT]) - [1]

'springframework > Spring Data JPA' 카테고리의 다른 글

Spring Data Common : 커스텀 리포지토리 생성  (0) 2020.11.16
Spring Data Common : Async Query Method  (0) 2020.11.16
Spring Data Common : 쿼리 작성 키워드  (0) 2020.11.15
Spring Data Common : Null 처리  (0) 2020.11.15
Spring Data Common : Repository 인터페이스 정의  (0) 2020.11.15

'springframework/Spring Data JPA'의 다른글

  • 현재글Spring Data Common : 쿼리 작성

관련글

티스토리툴바