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 |