Querydsl 기본(2)

 

우선 기본적인 정렬이다.

 

List<Member> result = queryFactory
        .selectFrom(member)
        .where(member.age.eq(100))
        .orderBy(member.age.desc(), member.username.asc().nullsLast())
        .fetch();

orderBy 인자는 OrderSpecifier 타입을 받는다. 

 

이 중에 nullsLast()와 nullsFirst()를 사용했을 때 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"=? 
    order by
        member0_."age" desc,
        member0_."username" asc nulls last

 

nulls 값일 때 결과의 뒤에 배치되도록 하는 SQL문이다. 저런 문법은 처음 본다.

 

통계 옵션을 켜고 jpql을 확인했을 때도 다음과 같다.

select member1
from Member member1
where member1.age = ?1
order by member1.age desc, member1.username asc nulls last, time: 4ms, rows: 3

jpql도 지원하는 문법인가 보다. 

 

 

페이징을 해보자.

 

List<Member> result = queryFactory
        .selectFrom(member)
        .orderBy(member.username.desc())
        .offset(1)
        .limit(2)
        .fetch();

assertThat(result.size()).isEqualTo(2);

   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_ 
    order by
        member0_."username" desc limit ? offset ?

 

offset은 0부터 시작이다. 

 

 

 

그룹에 대해 진행해 보자.

 

List<Tuple> result = queryFactory
        .select(
                member.count(),
                member.age.sum(),
                member.age.max(),
                member.age.min()
        )
        .from(member)
        .fetch();

 

Tuple타입의로 반환된다. Tuple은 여러 개의 반환 타입이 있을 때 반환되는 타입이다. 

    select
        count(member0_."member_id") as col_0_0_,
        sum(member0_."age") as col_1_0_,
        max(member0_."age") as col_2_0_,
        avg(cast(member0_."age" as double)) as col_3_0_,
        min(member0_."age") as col_4_0_ 
    from
        "member" member0_

 

Tuple로 데이터를 조회할 때는 다음과 같이 사용한다.

Tuple tuple = result.get(0);
assertThat(tuple.get(member.count())).isEqualTo(4);
assertThat(tuple.get(member.age.sum())).isEqualTo(100);
assertThat(tuple.get(member.age.max())).isEqualTo(40);
assertThat(tuple.get(member.age.avg())).isEqualTo(25);
assertThat(tuple.get(member.age.min())).isEqualTo(10);

 

group by

 

List<Tuple> result = queryFactory
        .select(team.name, member.age.avg())
        .from(member)
        .join(member.team, team) //member의 team과 team을 조인해준다.
        .groupBy(team.name)
        .fetch();

 

join의 의미가 member의 team과 team을 조인하라고 저렇게 두 개를 사용하나 보다. 

    select
        team1_."name" as col_0_0_,
        avg(cast(member0_."age" as double)) as col_1_0_ 
    from
        "member" member0_ 
    inner join
        "team" team1_ 
            on member0_."team_id"=team1_."id" 
    group by
        team1_."name"
        
 HQL: select team.name, avg(member1.age)
		from Member member1
 		inner join member1.team as team
		group by team.name, time: 4ms, rows: 2
        
        
[[teamA, 15.0], [teamB, 35.0]]

 

 

Join

 

List<Member> teamA = queryFactory
        .selectFrom(member)
        .join(member.team, team)
        .where(team.name.eq("teamA"))
        .fetch();

 

join(team)와 같이는 안되고 뭔가 힌트를 줘야 하나?

 

join(team)만 사용하니 다음과 같이 에러가 발생한다.,

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:

Syntax error in SQL statement 
"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 [*]
  where team1_.""name""=?"; 
  
  expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
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 
  where team1_."name"=? [42001-214]

 

join이 on절에 해당되는 값이 들어가는구나. 그래서 둘 다 명시해줘야 한다.

 

    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_."id" 
    where
        team1_."name"=?

 

 

on 절

 

나는 조인을 할 때 가장 적은 테이블을 선호한다. 연속적으로 조인을 하게 되면 생각보다 선행테이블의 로우 수가 성능에 영향을 미쳤던 것 같다. 그래서 on 절에서 조건을 많이 주는 편이다. 

 

이번에는 Querydsl을 사용한 on 절에 대해 알아본다.

 

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

 

필터링하는 과정인데, 왜 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_ 
    inner join
        "team" team1_ 
            on member0_."team_id"=team1_."id" 
    where
        team1_."name"=?

 

select절에 member와 team을 동시에 조회하면 정상적인 쿼리문이 발생한다.

    select
        member0_."member_id" as member_i1_0_0_,
        team1_."id" as 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_ 
    left outer join
        "team" team1_ 
            on member0_."team_id"=team1_."id" 
            and (
                team1_."name"=?
            )

[jpql]
select member1, member1.team
from Member member1
left join member1.team as team with team.name = ?1, time: 3ms, rows: 4

 

 

조인 대상을 조회하지 않기 때문에 Querydsl의 자체적인 쿼리를 수정해서 나간 것으로 보인다. 

 

당황스럽다. 개발자가 실수를 하던 작성된 querydsl으로 나간 것이 아닌 자체적으로 수정하여 나간다는 것에 약간 querydsl의 불신이 생기기 시작한다. 

 

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

 

다음과 같이 외래키 대신 on 절의 조건을 통해 조인을 할 수 있다. 이 경우 leftJoin에 아이디를 매칭하지 않고 team만 넣어서 처리했다. 

 

문법적인 차이이다. 

 

일반 조인은 leftJoin(member.team, team)

on 조인은 leftJoin(team). on(condition..)

 

실제 발생되는 쿼리는 다음과 같다.

    select
        member0_."member_id" as member_i1_0_0_,
        team1_."id" as 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_ 
    left outer join
        "team" team1_ 
            on (
                member0_."username"=team1_."name"
            )
2023-02-06 22:45:01.571 DEBUG 28432 --- [           main] o.h.stat.internal.StatisticsImpl         : HHH000117: HQL:

select member1, team
from Member member1
left join Team team with member1.username = team.name, time: 3ms, rows: 7

 

이러한 조인은 아마 엔티티를 조회하는 것보다 DTO Projections으로 조회할 때 많이 사용될 것 같다.