기본 문법 - Part2

in Spring on Querydsl


조인

기본 조인

조인의 기본 문법은 첫 번째 파라미터에 조인 대상을 지정하고,

두 번째 파라미터에 별칭으로 사용할 Q타입을 지정하면 된다.

join(조인 대상, 별칭으로 사용할 Q타입)

• join(), innerJoin() : 내부 조인

• leftJoin() : left 외부 조인

• rightJoin() : right 외부 조인


▷ Querydsl

@Test
public void join() throws Exception {
    List<Member> result = queryFactory
        .selectFrom(member)
        .join(member.team, team)           // leftJoin, rightJoin
        .where(team.name.eq("teamA"))
        .fetch();

    assertThat(result)
        .extracting("username")
        .containsExactly("member1", "member2");
}


▷ JPQL

select member1
from Member member1
inner join member1.team as team
where team.name = 'teamA'


▷ SQL

select
    --...
from member member0_ 
inner join team team1_ 
    on member0_.team_id=team1_.team_id 
where team1_.name=NULL;


참고로 inner join을 사용하면, where 절에서 필터링 하는 것과 기능이 동일하다.

위의 쿼리를 보면 JPQL은 where절을, SQL은 on절을 사용한 걸 볼 수 있다.


세타 조인

연관관계가 없는 필드로 조인하는 방식이다.

@Test
public void theta_join() throws Exception {
    
    em.persist(Member.of("teamA"));
    em.persist(Member.of("teamB"));

    // cross - join나간다.
    List<Member> fetch = queryFactory
            .select(member)
            .from(member, team)
            .where(member.username.eq(team.name))
            .fetch();

    assertThat(fetch)
            .extracting("username")
            .containsExactly("teamA", "teamB");
}

• from절에 여러 엔티티를 선택해서 세타조인을 할 수 있다.

• Outer Join은 불가능하다. → On절을 사용하면 가능해진다.


▷ JPQL

select 
    member1
from 
    Member member1, Team team
where 
    member1.username = team.name


▷ SQL

내부적으로 cross join을 사용한다.

select
    --...
from 
    member member0_ 
cross join team team1_ 
where member0_.username=team1_.name;


조인 - on절

ON절을 활용한 조인은 JPA 2.1부터 지원하며 2가지 기능을 제공한다.

1. 조인 대상 필터링
2. 연관관계 없는 엔티티 외부 조인


조인 대상 필터링

▷ Querydsl

@Test
@DisplayName("조인 대상 필터링")
public void simpleJoinTest1() {

    List<Tuple> result1 = queryFactory
                    .select(member,team)
                    .from(member)
                    .leftJoin(member.team, team).on(team.name.eq("teamA"))
                    .fetch();

    List<Tuple> result2 = queryFactory
                    .select(member,team)
                    .from(member)
                    .join(member.team, team)
                    .where(team.name.eq("teamA"))
                    .fetch();

}


▷ JPQL

--result1 Query
select
    member1, team 
from
    Member member1   
left join
    member1.team as team 
        with team.name = 'teamA'

--result2 Query
select 
    member1, team
from 
    Member member1
inner join member1.team as team
where team.name = 'teamA'


▷ SQL

result2 Query의 가독성이 훨씬 좋다.

--result1 Query
select
    --...
from
    member member0_ 
left outer join
    team team1_ 
        on member0_.team_id=team1_.team_id 
            and (
                team1_.name='teamA'
            )


--result2 Query
select 
    --..
from 
    member member0_ 
inner join team team1_ 
    on member0_.team_id=team1_.team_id
where team1_.name='teamA';


위에서도 잠깐 언급했지만 on절을 활용해 조인 대상을 필터링 할 때,

외부조인이 아니라 내부조인(inner join)을 사용하면, where절에서 필터링하는

것과 기능이 동일하다. 따라서 on절을 활용한 조인 대상 필터링을 사용할 때,

내부조인 이면 익숙한 where 절로 해결하고, 정말 외부조인이 필요한 경우에만

이 기능을 사용하자.


참고

아래 두 블로그에 Join시 ON절과 WHERE절에서 필터링의 차이에 대해서 확인할 수 있다.

▷ Join시 ON절과 WHERE절에서 필터링의 차이1

▷ Join시 ON절과 WHERE절에서 필터링의 차이2


연관관계 없는 엔티티 외부 조인

▷ Querydsl

@Test
public void joinOnNoRelation() {

    List<Tuple> result = queryFactory
                .select(member, team)
                .from(member)
                .leftJoin(team).on(member.username.eq(team.name))
                .fetch();

}

여기서 주의할 점은 leftJoin()의 문법을 잘 확인해야 한다.

leftJoin() 부분에 일반 조인과 다르게 엔티티 하나만 들어간다.

일반 Join : leftJoin(member.team, team).on(~~)

On Join  : leftJoin(team).on(~~)


▷ JPQL

select 
    member1, team
from 
    Member member1
left join Team team 
    with member1.username = team.name


▷ SQL

select 
    --..
from 
    member member0_ 
left outer join team team1_ 
    on member0_.username=team1_.name;


조인 - fetch join

fetch join 적용하기

join(),leftJoin() 뒤에 fetchJoin()이라고 추가하면 된다.

@Test
public void fetchJoinUse() throws Exception {
    em.flush();
    em.clear();

    Member findMember = queryFactory
            .selectFrom(member)
            .join(member.team, team).fetchJoin()        //← 여기가 핵심
            .where(member.username.eq("member1"))
            .fetchOne();

    assert findMember != null;
    boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam());
    assertThat(loaded).isTrue();

}


▷ JPQL

select
    member1 
from
    Member member1   
inner join fetch 
    member1.team as team 
where
    member1.username = 'member1'


▷ SQL

select 
    --..
from 
    member member0_
inner join team team1_ 
    on member0_.team_id=team1_.team_id 
where member0_.username='member1';


서브쿼리

서브 쿼리 eq 사용


QMember memberSub = new QMember("memberSub");

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


▷ JPQL

select 
    m 
from 
    Member m 
where m.age = (
    select max(memberSub.age)
    from Member memberSub
    )


▷ SQL

select
    --..
from
    member member0_ 
where
    member0_.age=(
        select
            max(member1_.age) 
        from
            member member1_
        )


서브 쿼리 goe 사용

▷ Querydsl

import static com.querydsl.jpa.JPAExpressions.*;

QMember memberSub = new QMember("memberSub");

List<Member> result = queryFactory
        .selectFrom(member)
        .where(member.age.goe(
                select(memberSub.age.avg())
                        .from(memberSub)
        ))
        .fetch();


▷ JPQL

select 
    m 
from Member m 
where m.age >= (
    select avg(memberSub.age)
    from Member memberSub
)


▷ SQL

select
    --..
from
    member member0_ 
where
    member0_.age >= (
        select
            avg(cast(member1_.age as double))
        from
            member member1_
        )


서브쿼리 여러 건 처리 in 사용

▷ Querydsl

List<Member> result = queryFactory
                .selectFrom(member)
                .where(member.in(
                    selectFrom(memberSub)
                    .where(memberSub.age.gt(10))
                ))
                .fetch();


▷ JPQL

select 
    m 
from Member m 
where m.age in (
    select memberSub 
    from Member memberSub 
    where memberSub.age > 10
    )


▷ SQL

select 
    --..
from member member0_ 
where member0_.member_id in (
    select member1_.member_id 
    from member member1_ 
    where member1_.age>10
    );


select 절에 subquery

▷ Querydsl

List<Tuple> result = queryFactory
            .select(member.username,
                    select(memberSub.age.avg()).from(memberSub))
            .from(member)
            .fetch();


▷ JPQL

select 
    m
    ,(select avg(memberSub.age) from Member memberSub)
from Member m


▷ SQL

select
    member0_.username as col_0_0_
    ,(select avg(cast(member1_.age as double)) 
      from member member1_) as col_1_0_ 
from
    member member0_


from 절의 서브쿼리

▷ 한계

JPA JPQL 서브쿼리의 한계점으로 from 절의 서브쿼리(인라인 뷰)는 지원하지 않는다.

당연히 Querydsl도 지원하지 않는다.

하이버네이트 구현체를 사용하면 select절의 서브쿼리는 지원한다.

Querydsl도 하이버네이트 구현체를 사용하면 select절의 서브쿼리를 지원한다.


▷ 해결방안

⒈ 서브쿼리를 join으로 변경한다.(가능한 상황도 있고, 아닐수도 있다.)

⒉ 애플리케이션에서 쿼리를 2번 분리해서 실행한다.

⒊ nativeSQL을 사용한다.


CASE문

단순한 조건

▷ Querydsl

List<String> result = queryFactory
        .select(member.age
                .when(10).then("열살")
                .when(20).then("스무살")
                .otherwise("기타"))
        .from(member)
        .fetch();


▷ JPQL

select
    case
        when m.age = 10 then '열살'
        when m.age = 20 then '스무살'
        else '기타'
    end
from Member m;


▷ SQL

select 
    case 
        when member0_.age=10 then '열살'
        when member0_.age=20 then '스무살' 
        else '기타' 
    end as col_0_0_ 
from member member0_;


복잡한 조건

▷ Querydsl

List<String> result = queryFactory
        .select(new CaseBuilder()
                .when(member.age.between(0,20)).then("열살")
                .when(member.age.between(21,30)).then("스무살")
                .otherwise("기타"))
        .from(member)
        .fetch();


▷ JPQL

select 
    case 
        when (member1.age between 0 and 20) then '열살' 
        when (member1.age between 21 and 30) then '스무살' 
        else '기타' 
    end
from Member member1


▷ SQL

select 
    case 
        when member0_.age between 0 and 20 then '열살' 
        when member0_.age between 21 and 30 then '스무살' 
        else '기타' 
    end as col_0_0_ 
from member member0_;


orderBy에서 Case 문 함께 사용하기

▷ Querydsl

NumberExpression<Integer> rankPath = new CaseBuilder()
                .when(member.age.between(0, 20)).then(2)
                .when(member.age.between(21, 30)).then(1)
                .otherwise(3);

        List<Tuple> result = queryFactory
                .select(member.username, member.age, rankPath)
                .from(member)
                .orderBy(rankPath.desc())
                .fetch();


▷ JPQL

select
    member1.username,
    member1.age,
    case 
        when (member1.age between 0 and 20) then 2 
        when (member1.age between 21 and 30) then 1 
        else 3 
    end 
from
    Member member1 
order by
    case 
        when (member1.age between 0 and 20) then 2 
        when (member1.age between 21 and 30) then 1 
        else 3 
    end desc


▷ SQL

select
    member0_.username as col_0_0_,
    member0_.age as col_1_0_,
    case 
        when member0_.age between 0 and 20 then 2 
        when member0_.age between 21 and 30 then 1 
        else 3 
    end as col_2_0_ 
from
    member member0_ 
order by
    case 
        when member0_.age between 0 and 20 then 2 
        when member0_.age between 21 and 30 then 1 
        else 3 
    end desc


상수, 문자 더하기

상수 더하기

▷ Querydsl

List<Tuple> result = queryFactory
                .select(member.username, Expressions.constant("A"))
                .from(member)
                .fetch();


▷ JPQL

최적화가 가능한 경우에는 SQL에 constant값을 넘기지 않는다.

select
    member1.username
    -- constannt 값 없음.
from
    Member member1


▷ SQL

select
    member0_.username as col_0_0_
    -- constannt 값 없음.
from
    member member0_


문자 더하기

▷ Querydsl

문자가 아닌 다른 타입들은 stringValue() 메소드를 사용하여 문자로

변환할 수 있다. 이 방법은 ENUM을 처리할 때도 자주 사용된다.

List<String> result = queryFactory
                .select(member.username.concat("_").concat(member.age.stringValue()))
                .from(member)
                .fetch();


▷ JPQL

select
    concat(concat(member1.username,'_'), str(member1.age)) 
from
    Member member1


▷ SQL

select
    ((member0_.username||'-')||cast(member0_.age as character varying)) as col_0_0_ 
from
    member member0_