[Spring] QueryDSL 문법

검색 조건

 

• 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 로 조건을 추가하면된다.

and 대신 , 로 대체 가능하다.

@Test
public void startQuerydsl2() {
    //member1을 찾아라.
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    Member findMember = queryFactory
            .select(member)
            .from(member)
            .where(member.username.eq("member1").and(member.age.eq(10)))//파라미터 바인딩 처리
            .fetchOne();
    // qtype은 static import로 쓰는걸 권장한다.
    assertThat(findMember.getUsername()).isEqualTo("member1");
}

@Test
public void startQuerydsl3() {
    //member1을 찾아라.
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    Member findMember = queryFactory
            .select(member)
            .from(member)
            .where(member.username.eq("member1"),
                    member.age.eq(10)) // and 대신 이렇게 끊어가도 된다.
            .fetchOne();
    // qtype은 static import로 쓰는걸 권장한다.
    assertThat(findMember.getUsername()).isEqualTo("member1");
}

또한 qtype은 QMember m = QMember("member1")를 생성하기보다는 static import로 쓰는걸 권장한다.

 

 

결과 조회

• fetch() : 리스트 조회, 데이터 없으면 빈 리스트 반환
• fetchOne() : 단 건 조회
• 결과가 없으면 : null
• 결과가 둘 이상이면 : com.querydsl.core.NonUniqueResultException
• fetchFirst() : limit(1).fetchOne()
• fetchResults() : 페이징 정보 포함, total count 쿼리 추가 실행
• fetchCount() : count 쿼리로 변경해서 count 수 조회

@Test
public void startQuerydsl4(){
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);

    //리스트 조회
    List<Member> fetch = queryFactory
            .selectFrom(member)
            .fetch();

    //단건 조회
    Member fetchOne = queryFactory
            .selectFrom(member)
            .fetchOne();

    //처음 한 건 조회
    Member findMember2 = queryFactory
            .selectFrom(member)
            .fetchFirst();

    //페이징에서 사용
    QueryResults<Member> results = queryFactory
            .selectFrom(member)
            .fetchResults();
    results.getTotal();
    List<Member> content = results.getResults();
    
    //count 쿼리로 변경
    long count = queryFactory
            .selectFrom(member)
            .fetchCount();

}

 

 

정렬

/**
 * 회원 정렬 순서
 * 1. 회원 나이 내림차순(desc)
 * 2. 회원 이름 올림차순(asc)
 * 단 2에서 회원 이름이 없으면 마지막에 출력(nulls last)
 */
@Test
public void sort() {
    
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    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();
    
    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();
}

 

페이징 조회 건수 제한

 

@Test
public void paging1(){
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    List<Member> result = queryFactory
            .selectFrom(member)
            .orderBy(member.username.desc())
            .offset(1) //몇개씩 skip할건지, 0부터 시작
            .limit(2) // 최대 2건 조회
            .fetch();

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

 

페이징 전체 조회

@Test
public void paging2() {
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    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);
}

 

집합

 

//집합 함수
/**
 * JPQL
 * select
 * COUNT(m), //회원수
 * SUM(m.age), //나이 합
 * AVG(m.age), //평균 나이
 * MAX(m.age), //최대 나이
 * MIN(m.age) //최소 나이
 * from Member m
 */

@Test
public void aggregation(){
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    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)
}

@Test
public void aggregation2(){
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    List<Tuple> results = queryFactory
            .select(team.name, member.age.avg())
            .from(member)
            .join(member.team, team) // member.team과 team을 join
            .groupBy(team.name) // team 이름으로 그룹핑(묶음)
            .fetch();

    Tuple teamA = results.get(0);
    Tuple teamB = results.get(1);

    assertThat(teamA.get(team.name)).isEqualTo("teamA");
    assertThat(teamA.get(member.age.avg())).isEqualTo(15);

    assertThat(teamB.get(team.name)).isEqualTo("teamB");
    assertThat(teamB.get(member.age.avg())).isEqualTo(35);
}

 

groupBy , 그룹화된 결과를 제한하려면 having

groupBy(), having() 예시

 

groupBy(item.price)

.having(item.price.gt(1000))