Query By Example

코드로 살펴보자.

 

// MemberRepositoryTest

@Test
public void queryByExample() {
    // given
    Team teamA = new Team("teamA");
    em.persist(teamA);

    Member m1 = new Member("m1", 0, teamA);
    Member m2 = new Member("m2", 0, teamA);
    em.persist(m1);
    em.persist(m2);

    em.flush();
    em.clear();

    // when
    // Probe
    Member member = new Member("m1");
    Example<Member> example = Example.of(member);

    List<Member> result = memberRepository.findAll(example);

    assertThat(result.get(0).getUsername()).isEqualTo("m1");
}

 

 

실행결과는 아래와 같다.

select
        member0_.member_id as member_i1_1_,
        member0_.age as age2_1_,
        member0_.created_date as created_3_1_,
        member0_.tema_id as tema_id5_1_,
        member0_.username as username4_1_ 
    from
        member member0_ 
    inner join
        team team1_ 
            on member0_.tema_id=team1_.team_id 
    where
        member0_.username=? 
        and team1_.name=?
2022-04-19 15:50:34.661  INFO 71773 --- [           main] p6spy                                    : #1650351034661 | took 0ms | statement | connection 3| url jdbc:h2:tcp://localhost/~/datajpa
select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.created_date as created_3_1_, member0_.tema_id as tema_id5_1_, member0_.username as username4_1_ from member member0_ inner join team team1_ on member0_.tema_id=team1_.team_id where member0_.username=? and team1_.name=?
select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.created_date as created_3_1_, member0_.tema_id as tema_id5_1_, member0_.username as username4_1_ from member member0_ inner join team team1_ on member0_.tema_id=team1_.team_id where member0_.username='m1' and team1_.name='teamA';

ExampleMatcher을 넣어서 돌려보면

// MemberRepositoryTest

// ExampleMatcher을 넣어서 돌려보면

@Test
    public void queryByExample() {
        // given
        Team teamA = new Team("teamA");
        em.persist(teamA);

        Member m1 = new Member("m1", 0, teamA);
        Member m2 = new Member("m2", 0, teamA);
        em.persist(m1);
        em.persist(m2);

        em.flush();
        em.clear();

        // when
        // Probe
        Member member = new Member("m1");


		// age는 다 무시할꺼다.
        ExampleMatcher matcher = ExampleMatcher.matching()
                .withIgnorePaths("age");


        Example<Member> example = Example.of(member, matcher);

        List<Member> result = memberRepository.findAll(example);

        assertThat(result.get(0).getUsername()).isEqualTo("m1");
    }

실행결과에서

where
        member0_.username=?만 가져오는 것을 확인할 수 있다.

select
        member0_.member_id as member_i1_1_,
        member0_.age as age2_1_,
        member0_.created_date as created_3_1_,
        member0_.tema_id as tema_id5_1_,
        member0_.username as username4_1_ 
    from
        member member0_ 
    where
        member0_.username=?
2022-04-19 16:11:00.875  INFO 72105 --- [           main] p6spy                                    : #1650352260875 | took 0ms | statement | connection 3| url jdbc:h2:tcp://localhost/~/datajpa
select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.created_date as created_3_1_, member0_.tema_id as tema_id5_1_, member0_.username as username4_1_ from member member0_ where member0_.username=?
select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.created_date as created_3_1_, member0_.tema_id as tema_id5_1_, member0_.username as username4_1_ from member member0_ where member0_.username='m1';

 

 

member와 team을 연관관계 매핑을 시키고 테스트를 돌려보면 

// when
    // Probe
    Member member = new Member("m1");
    Team team = new Team("teamA");
    // 객체 그래프를 연관관계 지어서 검색을 던질수가 있다.
    member.setTeam(team);

@Test
public void queryByExample() {
    // given
    Team teamA = new Team("teamA");
    em.persist(teamA);

    Member m1 = new Member("m1", 0, teamA);
    Member m2 = new Member("m2", 0, teamA);
    em.persist(m1);
    em.persist(m2);

    em.flush();
    em.clear();

    // when
    // Probe
    Member member = new Member("m1");
    Team team = new Team("teamA");
    // 객체 그래프를 연관관계 지어서 검색을 던질수가 있다.
    member.setTeam(team);

    // age는 다 무시한다.
    ExampleMatcher matcher = ExampleMatcher.matching()
            .withIgnorePaths("age");


    Example<Member> example = Example.of(member, matcher);

    List<Member> result = memberRepository.findAll(example);

    assertThat(result.get(0).getUsername()).isEqualTo("m1");
}

아래와 같이 나오게 된다.

select
        member0_.member_id as member_i1_1_,
        member0_.age as age2_1_,
        member0_.created_date as created_3_1_,
        member0_.tema_id as tema_id5_1_,
        member0_.username as username4_1_ 
    from
        member member0_ 
	// 팀과 멤버를 조인을 하고
    inner join
        team team1_ 
            on member0_.tema_id=team1_.team_id 
            
	// 검색 조건이 제대로 들어간다.
    where
        team1_.name=? 
        and member0_.username=?
2022-04-19 16:13:47.493  INFO 72142 --- [           main] p6spy                                    : #1650352427493 | took 0ms | statement | connection 3| url jdbc:h2:tcp://localhost/~/datajpa
select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.created_date as created_3_1_, member0_.tema_id as tema_id5_1_, member0_.username as username4_1_ from member member0_ inner join team team1_ on member0_.tema_id=team1_.team_id where team1_.name=? and member0_.username=?
select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.created_date as created_3_1_, member0_.tema_id as tema_id5_1_, member0_.username as username4_1_ from member member0_ inner join team team1_ on member0_.tema_id=team1_.team_id where team1_.name='teamA' and member0_.username='m1';

 

장점

• 동적 쿼리를 편리하게 처리
• 도메인 객체를 그대로 사용
• 데이터 저장소를 RDB에서 NOSQL로 변경해도 코드 변경없이 추상화가 되어있고 스프링 데이터 JPA JpaRepository 인터페이스에 이미 포함이 되어있다.

단점

• 조인은 가능하지만 내부 조인(INNER JOIN)만 가능하고 외부조인(LEFT JOIN)이 안된다.
• 다음과 같은 중첩 제약조건 안되는데
• firstname = ?0 or (firstname = ?1 and lastname = ?2)

 

• 매칭 조건이 매우 단순하다.
• 문자는 starts/contains/ends/regex
• 다른 속성은 정확한 매칭(=)만 지원
• 실무에서 사용하기 매칭 조건이 매우 단순하고, LEFT 조인이 안됨

실무에서는 QueryDSL을 사용하자!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<출처 김영한: 실전! 스프링 데이터 JPA >

https://www.inflearn.com/course/%EC%8A%A4%ED%94%84%EB%A7%81-%EB%8D%B0%EC%9D%B4%ED%84%B0-JPA-%EC%8B%A4%EC%A0%84/dashboard

실전! 스프링 데이터 JPA - 인프런 | 강의