[강의] 실전! Querydsl 1

https://www.inflearn.com/course/querydsl-%EC%8B%A4%EC%A0%84

실전! Querydsl - 인프런 | 강의

 

0. Querydsl 소개

쿼리를 자바 코드로 작성하게 도와주어 문법 오류를 컴파일 시점에 잡을 수 있음

동적 쿼리를 작성할 수 있음

단순 반복 코드가 줄어든다

 

1. 프로젝트 환경설정

항상 하던 환경 설정을 해준다

gradle도 intelij로

혹시 안됐다면 enable annotation processing도 켜준다

 

테스트 용으로 엔티티 하나 만들고 저 gradle에 있는 complieQuerydsl 따닥!

혹은 해당 프로젝트에서 ./gradlew clean이나 complieJava 명령어 이용해서 빌드 가능

 

위와 같은 오류가 난다면 아래처럼 해보자

buildscript {
   ext {
      queryDslVersion = "5.0.0"
   }
}

plugins {
   id 'org.springframework.boot' version '2.6.0'
   id 'io.spring.dependency-management' version '1.0.11.RELEASE'
   //querydsl 추가
   id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
   id 'java'
}

group = 'study'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

configurations {
   compileOnly {
      extendsFrom annotationProcessor
   }
}

repositories {
   mavenCentral()
}

dependencies {
   implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
   implementation 'org.springframework.boot:spring-boot-starter-web'
   compileOnly 'org.projectlombok:lombok'
   runtimeOnly 'com.h2database:h2'
   //querydsl 추가
   implementation "com.querydsl:querydsl-jpa:${queryDslVersion}"
   implementation "com.querydsl:querydsl-apt:${queryDslVersion}"


   annotationProcessor 'org.projectlombok:lombok'
   testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

test {
   useJUnitPlatform()
}

//querydsl 추가 시작
def querydslDir = "$buildDir/generated/querydsl"

querydsl {
   jpa = true
   querydslSourcesDir = querydslDir
}
sourceSets {
   main.java.srcDir querydslDir
}
compileQuerydsl{
   options.annotationProcessorPath = configurations.querydsl
}
configurations {
   compileOnly {
      extendsFrom annotationProcessor
   }
   querydsl.extendsFrom compileClasspath
}
//querydsl 추가 끝

 

그럼 build 폴더와 이런게 생긴다.

얘는 자동으로 generate 되는 거라 git에 올리면 안된다.(git ignore 해야함, 보통 기본으로 되어있다)

 

사용은 이렇게!

@SpringBootTest
@Transactional
class QuerydslApplicationTests {


	@Autowired
	EntityManager em;

	@Test
	void contextLoads() {
		Hello hello = new Hello();
		em.persist(hello);
		JPAQueryFactory query = new JPAQueryFactory(em);
		QHello qHello = QHello.hello;
		Hello result = query
				.selectFrom(qHello)
				.fetchOne();
		Assertions.assertThat(result).isEqualTo(hello);
		Assertions.assertThat(result.getId()).isEqualTo(hello.getId());
	}

}

 

http://querydsl.com/

Querydsl - Unified Queries for Java

 

아래 둘 중 하나를 선택하면 쿼리의 ? 값이 나온다 (성능 저하될 수 있으니 괜찮은지 보고 사용)

implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.5.8'

org.hibernate.type: trace

2. 예제 도메인 모델

 

맞춰서 엔티티 만들고 테스트 굿

@SpringBootTest
@Transactional
//@Commit 없으면 롤백을 해버림 하지만 다른 테스트와 꼬일 수 있음
public class MemberTest {
    @PersistenceContext
    EntityManager em;
    @Test
    public void testEntity() {
        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);

        //초기화
        em.flush();
        em.clear();

        //확인
        List<Member> members = em.createQuery("select m from Member m",
                        Member.class)
                .getResultList();
        for (Member member : members) {
            System.out.println("member=" + member);
            System.out.println("-> member.team=" + member.getTeam());
        }
    }
}

3. 기본 문법

- JPQL vs Querydsl

@Test
public void startJPQL() {

    String qlString =
            "select m from Member m " +
                    "where m.username = :username";
    Member findMember = em.createQuery(qlString, Member.class)
            .setParameter("username", "member1")
            .getSingleResult();
    Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
}

@Test
public void startQuerydsl() {
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    QMember m = new QMember("m");
    Member findMember = queryFactory
            .select(m)
            .from(m)
            .where(m.username.eq("member1"))//파라미터 바인딩 처리
            .fetchOne();
    Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
}

JPQL은 쿼리가 틀리면 실행 해야 알려줌

Querydls은 컴파일 시점에 오류를 잡아준다.

package study.querydsl;

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import study.querydsl.entity.Member;
import study.querydsl.entity.QMember;
import study.querydsl.entity.Team;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;

@SpringBootTest
@Transactional
public class QuerydslBasicTest {
    @PersistenceContext
    EntityManager em;

    JPAQueryFactory queryFactory;
    
    @BeforeEach
    public void before() {
        //멀티스레드에 문제 없게 em에서 잘 되어있어서 괜찮음
        //동시성 문제 안생김
        queryFactory = 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);
    }

    @Test
    public void startJPQL() {

        String qlString =
                "select m from Member m " +
                        "where m.username = :username";
        Member findMember = em.createQuery(qlString, Member.class)
                .setParameter("username", "member1")
                .getSingleResult();
        Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
    }
    
    @Test
    public void startQuerydsl() {
        QMember m = new QMember("m");
        Member findMember = queryFactory
                .select(m)
                .from(m)
                .where(m.username.eq("member1"))//파라미터 바인딩 처리
                .fetchOne();
        Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
    }
}

- 기본 Q-Type 활용

    @Test
    public void startQuerydsl() {
        Member findMember = queryFactory
                .select(member)
                .from(member)
                .where(member.username.eq("member1"))//파라미터 바인딩 처리
                .fetchOne();
        Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
    }

코드정리

QMember에 기본적으로 선언되어있는 member 사용(static import)

 

- 검색 조건 쿼리

 

@Test
public void startQuerydsl() {
    Member findMember = queryFactory
            .select(member)
            .from(member)
            .where(member.username.eq("member1")
                    .and(member.age.eq(10)))
            .fetchOne();
    Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
}

and는 이렇게도 가능하다

@Test
public void startQuerydsl() {
    Member findMember = queryFactory
            .select(member)
            .from(member)
            .where(
                    member.username.eq("member1")
                    ,member.age.eq(10)
            )
            .fetchOne();
    Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
}

온갖 조건들

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%’ 검색

- 결과 조회

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

리스트. 팀 쿼리 개수만큼 나감

fetchOne은 여러개라 오류남 생략

하나만 쿼리 내고 말았음

count 쿼리

 

- 정렬

@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();
    Member member5 = result.get(0);
    Member member6 = result.get(1);
    Member memberNull = result.get(2);
    
    //이름이 null인 사람이 가장 마지막에 나와야 한다 (.nullsLast())
    //반대는 nullFirst도 있음
    assertThat(member5.getUsername()).isEqualTo("member5");
    assertThat(member6.getUsername()).isEqualTo("member6");
    assertThat(memberNull.getUsername()).isNull();
}

- 페이징

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

@Test
    public void paging2() {
        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);
    }

- 집합

@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);
}

@Test
public void group() throws Exception {
    List<Tuple> result = queryFactory
            .select(team.name, member.age.avg())
            .from(member)
            .join(member.team, team)
            .groupBy(team.name)
            .fetch();
    Tuple teamA = result.get(0);
    Tuple teamB = result.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);
}

sql과 동일하게 사용할 수 있다 having도 사용 가능

 

- 조인 - 기본조인

join() , innerJoin() : 내부 조인(inner join)
leftJoin() : left 외부 조인(left outer join)
rightJoin() : rigth 외부 조인(rigth outer join)

@Test
public void join() throws Exception {
    QMember member = QMember.member;
    QTeam team = QTeam.team;
    List<Member> result = queryFactory
            .selectFrom(member)
            .join(member.team, team)
            .where(team.name.eq("teamA"))
            .fetch();
    assertThat(result)
            .extracting("username")
            .containsExactly("member1", "member2");
}

thetaJoin : 연관 관계가 없는 조인

from에 여러 엔티티를 선택하여 냅다 곱하기

외부 조인 불가능, 조인 on절을 사용하면 외부 조인도 가능

 

- 조인 - on 절

ON절을 활용한 조인(JPA 2.1부터 지원)
1. 조인 대상 필터링
2. 연관관계 없는 엔티티 외부 조인

 

예시

* JPQL: SELECT m, t FROM Member m LEFT JOIN m.team t on t.name = 'teamA'
* SQL: SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.TEAM_ID=t.id and t.name='teamA'

@Test
public void join_on_filtering() throws Exception {
    //select가 여러가지 타입이라 튜플임
    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);
    }
}

내부 조인일 때는 on과 where은 기능이 똑같음

더 익숙한 where을 주로 사용하도록!

 

* JPQL: SELECT m, t FROM Member m LEFT JOIN Team t on m.username = t.name
* SQL: SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.username = t.name

@Test
public void join_on_no_relation() throws Exception {
    em.persist(new Member("teamA"));
    em.persist(new Member("teamB"));
    List<Tuple> result = queryFactory
            .select(member, team)
            .from(member)
            .leftJoin(team).on(member.username.eq(team.name))
            .fetch();
    for (Tuple tuple : result) {
        System.out.println("t=" + tuple);
    }
}

leftjoin에 team 하나만 들어가는 것이 특징

어차피 비교해서 붙이는 게 아니라 두 개 넣을 필요가 없음

 

- 조인 - 페치조인

성능 최적화에 주로 사용하는 방법

@PersistenceUnit
EntityManagerFactory emf;

@Test
public void fetchJoinNo() throws Exception {
    em.flush();
    em.clear();
    Member findMember = queryFactory
            .selectFrom(member)
            .join(member.team, team)
            .fetchJoin()//이걸 붙이면 fetch가 된다
            .where(member.username.eq("member1"))
            .fetchOne();
    boolean loaded =
            emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam());//페치 됐는지 안됐는지 여부

}

- 서브 쿼리

@Test
public void subQuery() throws Exception {
    //겹치면 안되는 경우에는 새로 선언(ex. 서브쿼리용)
    QMember memberSub = new QMember("memberSub");
    
    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.eq(
                    JPAExpressions
                            .select(memberSub.age.max())
                            .from(memberSub)
            ))
            .fetch();
    assertThat(result).extracting("age")
            .containsExactly(40);
}

eq뿐 아니라 goe, in도 똑같이 가능하다

 

select 절 속 서브쿼리(JPAExpressions static import)

@Test
public void select_subQuery() throws Exception {
    
    QMember memberSub = new QMember("memberSub");
    List<Tuple> fetch = queryFactory
            .select(member.username,
                    select(memberSub.age.avg())
                            .from(memberSub)
            ).from(member)
            .fetch();
    for (Tuple tuple : fetch) {
        System.out.println("username = " + tuple.get(member.username));
        System.out.println("age = " +
                tuple.get(select(memberSub.age.avg())
                        .from(memberSub)));
    }
}

from 절의 서브쿼리는 안된다.

-> 해결

1. join으로 변경

2. 쿼리2번 분리

3. native SQL 사용(이건 JPA의 한계)

 

한 방 쿼리보다는 여러 쿼리로 나눠서 호출하는 것이 훨씬 효율적일 때가 있다

 

- case문

할 수는 있지만 왠만하면 DB에서 처리하지 말자

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

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

public void complexCase(){
    List<String> result = queryFactory
            .select(new CaseBuilder()
                    .when(member.age.between(0, 20)).then("0~20살")
                    .when(member.age.between(21, 30)).then("21~30살")
                    .otherwise("기타"))
            .from(member)
            .fetch();

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

- 상수, 문자 더하기

//냅다 상수 붙이기
@Test
public void constant(){
    Tuple result = queryFactory
            .select(member.username, Expressions.constant("A"))
            .from(member)
            .fetchFirst();
}

@Test
public void concat(){
    //.stringValue()로 숫자 -> 문자 전환
    String result = queryFactory
            .select(member.username.concat("_").concat(member.age.stringValue())) //{username_age}
            .from(member)
            .where(member.username.eq("member1"))
            .fetchOne();
}