[Querydsl 3편] Querydsl 메서드 정리

실전! Querydsl 강의 정리 3편

들어가기전

Spribgboot + Querydsl 설정

https://devfunny.tistory.com/844

[Querydsl 1편] SpringBoot2.7 + Querydsl5.0 설정하기 (Entity, Dto class to QClass)

 

예제 Entity 설정하기

https://devfunny.tistory.com/845

[Querydsl 2편] DTO, Entity, Test 클래스 생성 및 ERD (JPAQueryFactory.java)

 

 

ERD

 

 

SELECT

■ username이 "member1"의 데이터를 조회하라

@Test
public void startQuerydsl() {
    QMember m = QMember.member; //기본 인스턴스 사용
    // static import도 가능

    Member findMember = queryFactory
            .select(m)
            .from(m)
            .where(m.username.eq("member1"))// 파라미터 바인딩 처리
            .fetchOne();

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

■ 수행 Querydsl

select
    member1 
from
    Member member1 
where
    member1.username = ?1

■ 수행 쿼리

select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_ 
from
    member member0_ 
where
    member0_.username=?

 

QMember을 import static하면 코드가 좀더 간단해진다.

import static study.querydsl.entity.QMember.*;
...

@Test
public void startQuerydsl3() {
    // member1을 찾아라.
    Member findMember = queryFactory
            .select(member)
            .from(member)
            .where(member.username.eq("member1"))
            .fetchOne();

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

 

(참고) JPQL vs Querydsl

@Test
public void startJPQL() {
    // member1을 찾아라.
    String qlString =
            "select m from Member m " +
                    "where m.username = :username";

    Member findMember = em.createQuery(qlString, Member.class)
            .setParameter("username", "member1")
            .getSingleResult();

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

JPQL은 런타임 시점에 오류를 발견할 수 있고, Querydsl은 컴파일 시점에 오류를 발견할 수 있다.

 

 

SimpleExpression.java 메서드 정리

SimpleExpression.java 의 패키지는 아래와 같다.

package com.querydsl.core.types.dsl;

 

제공 메서드 목록

member.username.eq("member1") // username = 'member1'
member.username.ne("member1") //username != 'member1'
member.username.eq("member1").not() // username != 'member1'
member.username.isNotNull() //이름이 is not null
member.age.in(10, 20) // age in (10,20)
member.age.notIn(10, 20) // age not in (10, 20)
member.age.between(10,30) //between 10, 30
member.age.goe(30) // age >= 30
member.age.gt(30) // age > 30
member.age.loe(30) // age <= 30
member.age.lt(30) // age < 30
member.username.like("member%") //like 검색
member.username.contains("member") // like ‘%member%’ 검색
member.username.startsWith("member") //like ‘member%’ 검색

 

 

Where의 And

■ username이 "member1" AND age가 10인 데이터를 조회하라

@Test
public void search() {
    Member findMember = queryFactory
            .selectFrom(member)
            .where(member.username.eq("member1")
                    .and(member.age.eq(10)))
            .fetchOne();
    // 검색 조건은 .and() , . or() 를 메서드 체인으로 연결할 수 있다.

    assertThat(findMember.getUsername()).isEqualTo("member1");
}

/* where() 에 파라미터로 검색조건을 추가하면 AND 조건이 추가됨 */
@Test
public void searchAndParam() {
    List<Member> result1 = queryFactory
            .selectFrom(member)
            .where(member.username.eq("member1"),
                    member.age.eq(10))
            .fetch();

    assertThat(result1.size()).isEqualTo(1);
}

■ 수행 Querydsl

select
    member1 
from
    Member member1 
where
    member1.username = ?1 
    and member1.age = ?2

■ 수행 쿼리

select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_ 
from
    member member0_ 
where
    member0_.username=? 
    and member0_.age=?

 

 

AbstractJPAQuery 메서드 정리

SimpleExpression.java 의 패키지는 아래와 같다.

com.querydsl.jpa.impl

메서드	설명
fetch()	리스트 조회, 데이터 없으면 빈 리스트 반환
fetchOne()	단 건 조회 - 결과가 없으면 : null - 결과가 둘 이상이면 : com.querydsl.core.NonUniqueResultException
fetchFirst()	limit(1).fetchOne()
fetchResults()	페이징 정보 포함, total count 쿼리 추가 실행 - 페이징 쿼리가 복잡해지면 데이터,totalCount 조회 쿼리가 다를 수도 있다.   성능 때문에 totalCount 쿼리를 좀더 간단하게 조회할 수 있으므로, 성능이 중요할경우 둘을 따로 조회하자.
fetchCount()	count 쿼리로 변경해서 count 수 조회

 

 

Sort()

■ 아래의 조건으로 정렬을 수행하라.

• 1) 회원 나이 내림차순(desc)
• 2) 회원 이름 올림차순(asc)
• 3) 단 2)에서 회원 이름이 없으면 마지막에 출력(nulls last)

@Test
public void sort() {
    em.persist(new Member(null, 100));
    em.persist(new Member("member5", 100));
    em.persist(new Member("member6", 100));

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

    Member member5 = result.get(0);
    Member member6 = result.get(1);
    Member memberNull = result.get(2);

    assertThat(member5.getUsername()).isEqualTo("member5");
    assertThat(member6.getUsername()).isEqualTo("member6");
    assertThat(memberNull.getUsername()).isNull();
}

■ 수행 Querydsl

select
    member1 
from
    Member member1 
where
    member1.age = ?1 
order by
    member1.age desc,
    member1.username asc nulls last

■ 수행 쿼리

select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_ 
from
    member member0_ 
where
    member0_.age=? 
order by
    member0_.age desc,
    member0_.username asc nulls last

 

 

offset(), limit()

■ 정렬된 데이터에서 0, 1, 2, 3,,, 중에 1~2 총 2개의 데이터를 조회하라.

@Test
public void paging1() {
    List<Member> result = queryFactory
            .selectFrom(member)
            .orderBy(member.username.desc())
            .offset(1) // 0부터 시작(zero index): 1개를 skip한다.
            .limit(2) // 최대 2건 조회
            .fetch();

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

■ 수행 Querydsl

select
    member1 
from
    Member member1 
order by
    member1.username desc

 

■ 수행 쿼리

select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_ 
from
    member member0_ 
order by
    member0_.username desc limit ? offset ?

 

 

 

fetchResults() - getTotal(), getLimit(), getOffset(), getResults().size()

■ 페이징 정보를 얻어오자. 

@Test
public void paging2() {
    // 1) count 쿼리
    // 2) contents 쿼리
    QueryResults<Member> queryResults = queryFactory
            .selectFrom(member)
            .orderBy(member.username.desc())
            .offset(1)
            .limit(2)
            .fetchResults();

    assertThat(queryResults.getTotal()).isEqualTo(4);
    assertThat(queryResults.getLimit()).isEqualTo(2);
    assertThat(queryResults.getOffset()).isEqualTo(1);
    assertThat(queryResults.getResults().size()).isEqualTo(2);
}

■ 수행 Querydsl

total count 조회하는 쿼리가 별도로 수행된다.

select
    count(member1) 
from
    Member member1

select
    member1 
from
    Member member1 
order by
    member1.username desc

■ 수행 쿼리

select
    count(member0_.member_id) as col_0_0_ 
from
    member member0_

select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_ 
from
    member member0_ 
order by
    member0_.username desc limit ? offset ?

 

 

집계쿼리

Tuple로 조회하여 Tuple.get(member.count()) 방식으로 데이터를 꺼내올 수 있다.

■ age 의 집계를 조회하라.

/*
* COUNT(m), //회원수
* SUM(m.age), //나이 합
* AVG(m.age), //평균 나이
* MAX(m.age), //최대 나이
* MIN(m.age) //최소 나이
*/
@Test
public void aggregation() throws Exception {
    List<Tuple> result = queryFactory
            .select(member.count(),
                    member.age.sum(),
                    member.age.avg(),
                    member.age.max(),
                    member.age.min())
            .from(member)
            .fetch();

    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.avg())).isEqualTo(25);
    assertThat(tuple.get(member.age.max())).isEqualTo(40);
    assertThat(tuple.get(member.age.min())).isEqualTo(10);
}

■ 수행 Querydsl

select
    count(member1),
    sum(member1.age),
    avg(member1.age),
    max(member1.age),
    min(member1.age) 
from
    Member member1

■ 수행 쿼리

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

 

 

leftJoin()

■ member, team을 모두 출력하라. team 없는 member도 모두 출력하라.

@Test
public void join_on_filtering() throws Exception {
    /*
        t=[Member(id=3, username=member1, age=10), Team(id=1, name=teamA)]
        t=[Member(id=4, username=member2, age=20), Team(id=1, name=teamA)]
        t=[Member(id=5, username=member3, age=30), null]
        t=[Member(id=6, username=member4, age=40), null]
     */
    List<Tuple> result = queryFactory
            .select(member, team)
            .from(member)
            .leftJoin(member.team, team).on(team.name.eq("teamA"))
            .fetch();

    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

아래 쿼리와 동일하다.

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

■ 수행 Querydsl

select
    member1,
    team 
from
    Member member1   
left join
    member1.team as team with team.name = ?1

■ 수행 쿼리

select
    member0_.member_id as member_i1_1_0_,
    team1_.team_id as team_id1_2_1_,
    member0_.age as age2_1_0_,
    member0_.team_id as team_id4_1_0_,
    member0_.username as username3_1_0_,
    team1_.name as name2_2_1_ 
from
    member member0_ 
left outer join
    team team1_ 
        on member0_.team_id=team1_.team_id 
        and (
            team1_.name=?
        )