QueryDSL 기본

김신영·2023년 2월 12일
HibernateJPAJPQLORMQuerydsl
0

JPA

목록 보기
9/14
post-thumbnail

QueryDSL 환경 설정

  1. build.gradle
  • com.querydsl:querydsl-jpa:5.0.0
    • QueryDSL 라이브러리
  • com.querydsl:querydsl-apt:5.0.0
    • QueryDSL 관련 코드 생성 기능 제공 (Annotation Processor Tool)
//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.gradle 예시
    buildscript {
	ext {
		queryDslVersion = "5.0.0"
		guavaVersion = "31.1-jre"
		apacheCommonLang3Version = "3.12.0"
		p6spySpringBootStarterVersion = "1.8.1"
	}
}

plugins {
	id 'java'
	id 'org.springframework.boot' version '2.7.7'
	id 'io.spring.dependency-management' version '1.0.15.RELEASE'
	id 'com.ewerk.gradle.plugins.querydsl' version '1.0.10'
}

group = 'com.example'
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'

	implementation "com.querydsl:querydsl-jpa:${queryDslVersion}"
	implementation "com.querydsl:querydsl-apt:${queryDslVersion}"

	implementation "com.github.gavlyukovskiy:p6spy-spring-boot-starter:${p6spySpringBootStarterVersion}"

	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'com.h2database:h2'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'

	implementation "com.google.guava:guava:${guavaVersion}"
	implementation "org.apache.commons:commons-lang3:${apacheCommonLang3Version}"
}

tasks.named('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 추가 끝
  1. Q 타입 생성
./gradlew clean compileQuerydsl

테스트 코드 세팅

Github 코드 참조

  • 예시 (JUnit5)
    @SpringBootTest
@Transactional
public class QueryDslBasicTest {

	@PersistenceContext
	private EntityManager em;

	JPAQueryFactory queryFactory;

	@BeforeEach
	void beforeEach() {
			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);
	
	    em.flush();
	    em.clear();
	}
}

Q-Type 활용

  • Q-Type 기본 인스턴스를 static import 하여 사용하는 방법
  • Q-Type 인스턴스를 직접 생성하여 사용하는 방법
    • SubQuery에 주로 사용
  • QueryDSL
@Test
void startQueryDSL() {
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    QMember m = new QMember("m");

    Member result = queryFactory.select(m)
        .from(m)
        .where(m.name.eq("member3"))
        .fetchOne();

    assertThat(result).isNotNull()
        .hasFieldOrPropertyWithValue("name", "member3")
        .hasFieldOrPropertyWithValue("age", 30);
}
  • JPQL
@Test
@Order(1)
void startJPQL() {
    String query = "select m from Member m where m.name = :name";

    Member result = em.createQuery(query, Member.class)
        .setParameter("name", "member3")
        .getSingleResult();

    assertThat(result).isNotNull()
        .hasFieldOrPropertyWithValue("name", "member3")
        .hasFieldOrPropertyWithValue("age", 30);
}

결과 조회 (Fetch Result)

  • fetch
    • 리스트 조회
    • 데이터 없으면 빈 리스트 반환
    • 예시 코드
      @Test
void fetch() {
    List<Member> result = queryFactory
        .selectFrom(member)
        .fetch();

    assertThat(result).hasSize(4);
}
  • fetchOne
    • 결과가 없으면, NULL
    • 결과가 둘 이상이면, com.querydsl.core.NonUniqueResultException
    • 예시 코드
      @Test
void fetchOne() {
    assertThatExceptionOfType(NonUniqueResultException.class).isThrownBy(() ->
            queryFactory
                .selectFrom(member)
                .fetchOne()
    );
}
  • fetchFirst() , limit(1).fetchOne()
    • 예시 코드
      @Test
void fetchFirst() {
    Member result = queryFactory.selectFrom(member)
        .orderBy(member.id.desc())
        .fetchFirst();

    assertThat(result).isNotNull()
        .hasFieldOrPropertyWithValue("name", "member4")
        .hasFieldOrPropertyWithValue("age", 40);
}
  • fetchRestuls() (Deprecated)
    • 페이징 정보 포함
    • total count 쿼리 추가 실행
    • 예시 코드
      @Test
@SuppressWarnings("deprecation")
void fetchResults() {
    QueryResults<Member> result = queryFactory.selectFrom(member)
        .fetchResults();    // Deprecated

    List<Member> results = result.getResults();

    assertThat(results).hasSize(4);
    assertThat(result.getTotal()).isEqualTo(4);
    assertThat(result.getOffset()).isEqualTo(0);
    System.out.println("result.getLimit() = " + result.getLimit());
}
  • fetchCount() (Deprecated)
    • count 쿼리로 변경해서 count 수 조회
    • 예시 코드
      @Test
@SuppressWarnings("deprecation")
void fetchCount() {
    long count = queryFactory.selectFrom(member)
        .fetchCount();// Deprecated

    assertThat(count).isEqualTo(4);
}

정렬 (Sort)

  • desc()
  • asc()
  • nullsLast()
    • null 데이터를 마지막으로
  • nullsFirst()
    • null 데이터를 처음으로
  • 예시 코드
    @Test
void sort() {
    Member memberNull = new Member(null, 100);
    Member member5 = new Member("member5", 100);
    Member member6  = new Member("member6", 100);

    em.persist(memberNull);
    em.persist(member5);
    em.persist(member6);

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

    assertThat(result).hasSize(3)
        .containsExactly(member5, member6, memberNull);
}

페이징 (Paging)

  • offset(int n)
  • limit(int n)
  • 예시 코드
    @Test
void paging() {
    List<Member> result = queryFactory.selectFrom(member)
        .orderBy(member.name.desc())
        .offset(1)  // zero base index
        .limit(2)
        .fetch();

    assertThat(result).hasSize(2);
}

Distinct

  • JPQL의 distinct와 같다.
  • 코드 예시
    @Test
void distinct() {
    List<String> result = queryFactory
        .select(member.name).distinct()
        .from(member)
        .fetch();

    assertThat(result).hasSize(4);
}

집합 (Aggregation)

집합 함수 (Aggregation Function)

JPQL이 제공하는 모든 집함함수를 제공한다.

  • count()
    • countDistinct()
  • sum()
  • avg()
  • max()
  • min()
  • 예시 코드
    @Test
void aggregation() {
    List<Tuple> result = queryFactory
        .select(
            member.countDistinct(),
            member.age.sum(),
            member.age.avg(),
            member.age.max(),
            member.age.min())
        .from(member)
        .fetch();

    assertThat(result).hasSize(1);
    Tuple tuple = result.get(0);

    assertThat(tuple.get(member.countDistinct())).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);
}

GroupBy

  • 예시 코드
    @Test
void grouping() {
    List<Tuple> result = queryFactory.select(team.name, member.age.avg())
        .from(member)
        .join(member.team, team)
        .groupBy(team.name)
        .orderBy(team.name.asc())
        .fetch();

    Long teamCount = queryFactory.select(team.count()).from(team).fetchOne();
    assertThat(teamCount).isNotNull().isPositive();
    assertThat(result).hasSize(teamCount.intValue());

    Tuple tupleOfTeamA = result.get(0);
    Tuple tupleOfTeamB = result.get(1);

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

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

Having

  • 예시 코드
    @Test
void having() {
    List<Tuple> result = queryFactory.select(team.name, member.age.avg())
        .from(member)
        .join(member.team, team)
        .groupBy(team.name)
        .having(member.age.avg().goe(20))
        .orderBy(team.name.asc())
        .fetch();

    assertThat(result).hasSize(1);

    Tuple tupleOfTeamB = result.get(0);

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

# 조인 (Join)

기본 조인 (Join)

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

  • 첫번째 파라미터에 조인 대상을 지정
  • 두번째 파라미터에 별칭(alias)으로 사용할 Q-Type을 지정
  • join(), innerJoin()
  • leftJoin()
  • rightJoin()
  • 예시 코드
    @Test
void join() {
    List<Member> result = queryFactory
        .selectFrom(member)
        .join(member.team, team)
        .where(team.name.eq("teamA"))
        .orderBy(member.name.desc())
        .fetch();

    assertThat(result).hasSize(2)
        .extracting(Member::getName)
        .containsExactly("member2", "member1");
}

세타 조인 (Theta Join)

연관관계가 없는 컬럼으로 조인

  • from(...) 함수에 여러 Q-Type을 파라미터로 설정
  • 예시 코드
```java
@Test
void thetaJoin() {
    em.persist(new Member("teamA"));
    em.persist(new Member("teamB"));

    List<Member> result = queryFactory
        .select(member)
        .from(member, team)
        .where(member.name.eq(team.name))
        .orderBy(member.name.asc())
        .fetch();

    assertThat(result).hasSize(2)
        .extracting(Member::getName)
        .containsExactly("teamA", "teamB");
}
```

조인 - on 절

1. 조인 대상 필터링

  • 예시 코드
    @Test
void joinOnFiltering() {
    List<Tuple> result = queryFactory.select(member, team)
        .from(member)
        .leftJoin(member.team, team).on(team.name.eq("teamA"))
        .fetch();

    result.forEach(tuple -> System.out.println("tuple = " + tuple));

    assertThat(result).hasSize(4);
}

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

  • hibernate 5.1부터 on 을 사용해서 서로 관계가 없는 필드로 외부 조인, 내부 조인 기능이 추가됨
  • 예시 코드
    @Test
void joinOnThetaJoin() {
    em.persist(new Member("teamA"));
    em.persist(new Member("teamB"));

    List<Tuple> result = queryFactory
        .select(member, team)
        .from(member)
				// cf) 일반 left 조인: leftJoin(member.team, team)
        .leftJoin(team).on(member.name.eq(team.name))
        .fetch();

    result.forEach(tuple -> System.out.println("tuple = " + tuple));

    assertThat(result).hasSize(6);
}

페치 조인 (Fetch Join)

  • join(member.team, team).fetchJoin()
  • ❌ 페치 조인은 SQL에서 제공하는 기능이 아니다.
  • 주로 성능 최적화를 위해 사용
  • 👎  페치 조인 미적용 예시 코드
    @Test
void withOutFetchJoin() {
    Member findMember = queryFactory.selectFrom(member)
        .where(member.name.eq("member1"))
        .fetchOne();

    assertThat(findMember).isNotNull();

    assertThat(Hibernate.isInitialized(findMember.getTeam())).isFalse();
    assertThat(emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam())).isFalse();

    System.out.println("findMember.getTeamName() = " + findMember.getTeamName());

    assertThat(Hibernate.isInitialized(findMember.getTeam())).isTrue();
    assertThat(emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam())).isTrue();
}
  • 👍  페치 조인 적용 예시 코드
    @Test
void fetchJoin() {
    Member findMember = queryFactory
        .selectFrom(member)
        .join(member.team, team).fetchJoin()
        .where(member.name.eq("member1"))
        .fetchOne();

    assertThat(findMember).isNotNull();
    assertThat(Hibernate.isInitialized(findMember.getTeam())).isTrue();
    assertThat(emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam())).isTrue();
}

서브 쿼리 (SubQuery)

  • com.querydsl.jpa.JPAExpressions 사용
    • 예시 코드
      @Test
void subQuery() {
    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);
}

@Test
void subQueryWithGoe() {
    QMember memberSub = new QMember("memberSub");

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

    assertThat(result).extracting("age")
        .containsExactly(30, 40);
}

@Test
void subQueryWithIn() {
    QMember memberSub = new QMember("memberSub");

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

    assertThat(result).extracting(Member::getAge)
        .containsExactly(20, 30, 40);
}
  • static import 사용 가능
    • 예시 코드
      @Test
void subQueryWithStaticImport() {
    QMember memberSub = new QMember("memberSub");

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

    assertThat(result)
        .extracting("age")
        .containsExactly(40);
}
  • ❌  from 절의 서브쿼리는 지원하지 않는다.
  • ✅  Hibernate의 경우, select 절의 서브쿼리는 지원한다.
    • 예시 코드
      @Test
void subQueryInSelect() {
    QMember memberSub = new QMember("memberSub");

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

    result.forEach(tuple -> {
        System.out.println("tuple = " + tuple);
        System.out.println("tuple.get(member.name) = " + tuple.get(member.name));
        System.out.println("tuple.get(0, String.class) = " + tuple.get(0, String.class));
        System.out.println("tuple.get(1, Double.class) = " + tuple.get(1, Double.class));
    });

    assertThat(result).hasSize(4)
        .extracting(tuple -> tuple.get(1, Double.class))
        .containsOnly(25.0);
}

from절 서브쿼리 한계점 해결 방안

  1. 서브쿼리를 join으로 변경한다.
  2. 애플리케이션에서 2번 분리해서 실행한다.
  3. nativeSQL을 사용한다.

Case 문

  • when(), then(), otherwise()
    • 코드 예시
      @Test
void caseQuery() {
    List<String> result = queryFactory
        .select(member.age
            .when(10).then("열살")
            .when(20).then("스무살")
            .otherwise("기타"))
        .from(member)
        .orderBy(member.age.asc())
        .fetch();

    assertThat(result)
        .hasSize(4)
        .containsExactly("열살", "스무살", "기타", "기타");

    result.forEach(System.out::println);
}
  • com.querydsl.core.types.dsl.CaseBuilder 를 통해 수행 가능
    • 코드 예시
      @Test
void caseQueryWithCaseBuilder() {
    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)
        .orderBy(member.age.asc())
        .fetch();

    assertThat(result)
        .hasSize(4)
        .containsExactly("0-20", "0-20", "21-30", "기타");

    result.forEach(System.out::println);
}
  • select, where, orderBy 에서 사용 가능
    • 코드 예시
      @Test
void caseQueryInSelect() {
    NumberExpression<Integer> rankPath = new CaseBuilder()
        .when(member.age.between(0, 20)).then(2)
        .when(member.age.between(21, 30)).then(1)
        .otherwise(3);

		// select 절에 case문 사용
    List<Tuple> result = queryFactory.select(member.name, member.age, rankPath)
        .from(member)
        .orderBy(rankPath.desc())  // orderBy 절에 case문 사용
        .fetch();

    assertThat(result).hasSize(4)
        .extracting(tuple -> tuple.get(rankPath))
        .containsExactly(3, 2, 2, 1);

    result.forEach(tuple -> {
        System.out.println("member.name = " + tuple.get(member.name));
        System.out.println("member.age = " + tuple.get(member.age));
        System.out.println("rankPath = " + tuple.get(rankPath));
    });
}

상수, 문자 더하기

  • 상수가 필요하다면, Expressions.constant(...) 사용
    • 코드 예시
      @Test
void constant() {
    Expression<String> constant = Expressions.constant("A");

    // 실제로 쿼리 수행할때는 DB쪽에 상수를 넘기지 않는다.
    Tuple result = queryFactory
        .select(member.name, constant)
        .from(member)
        .fetchFirst();
   
    assertThat(result.get(constant)).isEqualTo("A");
}
  • 문자가 아닌 다른 타입들을 문자로 변환
    • stringValue()
  • 문자 더하기 concat(...)
    • 코드 예시
      @Test
void constantConcat() {
    List<String> result = queryFactory
        .select(member.name.concat("_").concat(member.age.stringValue()))
        .from(member)
        .fetch();

    assertThat(result).hasSize(4);

    result.forEach(System.out::println);
}
profile
김신영
Hello velog!
이전 포스트

Chapter10. 객체지향 쿼리 (JPQL)

다음 포스트

QueryDSL 중급

0개의 댓글