QueryDsl - Fetch join / 서브 쿼리 / concat

지금부터 아래의 글에는 모두 @BeaforEach 를 사용하여 다음의 값을 넣어 줄 것입니다.

member : member1 / member2 / member3 / member4
team : teamA / teamB가 생성될 것입니다.

• Member와 Team은 N:1 연관관계

  @BeforeEach
    public void before() {
        jpaQueryFactory = new JPAQueryFactory(em);
        Team teamA = new Team("teamA");
        Team teamB = new Team("teamB");
        em.persist(teamA);
        em.persist(teamB);
        Member member1 = new Member("member1", 10, teamA);
        Member member2 = new Member("member2", 20, teamA);
        Member member3 = new Member("member3", 30, teamB);
        Member member4 = new Member("member4", 40, teamB);
        em.persist(member1);
        em.persist(member2);
        em.persist(member3);
        em.persist(member4);
    }

Member엔티티와 Team엔티티는 N : 1 연관관계이다. 그리고 LAZY지연로딩이 설정되어있다

일반조인

• 다음

    @Test
    @DisplayName("일반 조인 not fetchjoin")
    void join() throws Exception {
        em.flush();
        em.clear();

        Member findMember = jpaQueryFactory
                .selectFrom(member)
                .join(member.team,team)
                .where(member.username.eq("member1"))
                .fetchOne();

    }

• Team엔티티는 member와 지연로딩으로 설정이 되어있기 때문에 team을 조회하지 않는다면 처음의 쿼리는 다음과 같이 member 엔티티만 조회하는 쿼리만 먼저 동작한다.

  select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.team_id as team_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    inner join
        team team1_ 
            on member0_.team_id=team1_.team_id 
    where
        member0_.username=?

QueryDsl의 Fetch Join

• join() 뒤에 .fetchJoin()의 메소드를 적어주면 fetch Join이 적용된다.

    @Test
    @DisplayName("fetch join 적용시")
    void fetch() throws Exception {

        Member findMember = jpaQueryFactory
                .selectFrom(member)
                .join(member.team,team).fetchJoin()
                .where(member.username.eq("member1"))
                .fetchOne();

    }

• 실행되는 sql은 다음 아래와 같이 member와 team을 한번에 조회한다.
• 즉시로딩으로 Member, Team SQL 쿼리 조인으로 한번에 조회

  select
        member0_.member_id as member_i1_0_0_,
        team1_.team_id as team_id1_1_1_,
        member0_.age as age2_0_0_,
        member0_.team_id as team_id4_0_0_,
        member0_.username as username3_0_0_,
        team1_.name as name2_1_1_ 
    from
        member member0_ 
    inner join
        team team1_ 
            on member0_.team_id=team1_.team_id 
    where
        member0_.username=?

QueryDsl 서브 쿼리

🎯 서브쿼리안를 사용할 때는 반드시 새로운 QMember를 생성해주자!

QueryDsl에서 서브쿼리를 이용하기 위해서는 com.querydsl.jpa.JPAExpressions 사용해야 한다.

서브쿼리 예제 🅰

• 나이가 가장 많은 회원 조회를 서브쿼리를 이용한 코드

/**나이가 가장 많은 회원 조회*/
    @Test
    @DisplayName("서브 쿼리")
    void subQuery() throws Exception {
        QMember memberSub = new QMember("memberSub");

        List<Member> result = jpaQueryFactory.selectFrom(member)
                .where(member.age.eq(
                        JPAExpressions
                                .select(memberSub.age.max())
                                .from(memberSub)
                )).fetch();

        assertThat(result).extracting("age").containsExactly(40);
    }

keypoint

where() 절 안에서 서브쿼리를 만들어 줄 때 바깥에 jpaQueryFactory.selectFrom(member) 에 있는 QMember.member와 겹치면 안되기 때문에 (알리아스가 겹치면 안되기 때문에) Qmember 객체를 새로 만들어 주었다.

• 실행되는 SQL 구문 🔽

 select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.team_id as team_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    where
        member0_.age=(
            select
                max(member1_.age) 
            from
                member member1_
        )

서브쿼리 예제 🅱

• member의 나이가 평균 이상인 회원만 조회

    @Test
    @DisplayName("나이가 평균 이상인 회원 조회")
    void subQuery2() throws Exception {
        QMember memberSub = new QMember("memberSub");
        // 주의 !!!!!!!!!!!! 서브쿼리안에 기존의 member사용하지 않기
        List<Member> result = jpaQueryFactory
                .selectFrom(member)
                .where(member.age.goe(
                        JPAExpressions
                                .select(memberSub.age.avg())
                                .from(memberSub)
                ))
                .fetch();
        // 10살 20살 30살 40살 -> 평균나이는 25살이다.
        assertThat(result).extracting("age")
                .containsExactly(30, 40);
    }

• 실행되는 SQL 구문 🔽

  select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.team_id as team_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    where
        member0_.age>=(
            select
                avg(cast(member1_.age as double)) 
            from
                member member1_
        )

서브쿼리 in절 사용

• 다음의 예제는 10살을 제외하고 조회할 것인데 in절을 사용.

    /***10살 제외 in절 사용*/
    @Test
    @DisplayName("서브쿼리 in절 사용 ")
    void inSub() throws Exception {

        QMember memberSub = new QMember("memberSub");

        List<Member> result = jpaQueryFactory
                .selectFrom(member)
                .where(member.age.in(
                        JPAExpressions
                                .select(memberSub.age)
                                .from(memberSub)
                                .where(memberSub.age.gt(10))
                ))
                .fetch();

        assertThat(result).extracting("age")
                .containsExactly(20, 30, 40);

    }

QueryDsl 문자더하기 concat()

keypoint

member.age.stringValue() 부분이 중요한데, 문자가 아닌 다른 타입들은 stringValue()로 문자로 변환할 수 있다.

 @Test
    @DisplayName("문자 더하기 concat")
    void concat() throws Exception {
        List<String> list = jpaQueryFactory
                .select(member.username.concat("_").concat(member.age.stringValue()).concat("살"))
                .from(member)
                .fetch();

        for (String s : list) {
            System.out.println("member = " + s);
        }

• 해당 QueryDsl의 출력🔽

member = member1_10살
member = member2_20살
member = member3_30살
member = member4_40살