[Querydsl] 환경설정 및 기본 문법

환경설정

연습용 프로젝트 준비 

• start.spring.io에서 Dependencies 추가
  • spring.web
  •  lombok
  • h2
  • data jpa
• build.gradle에 querydsl 추가 

plugins {
    // queryDSL 추가
	id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
}
// spring boot 3.x 버전 이상 Querydsl 추가
dependencies {
    implementation 'com.querydsl:querydsl-jpa:5.0.0:jakarta'
    annotationProcessor "com.querydsl:querydsl-apt:${dependencyManagement.importedProperties['querydsl.version']}:jakarta"
    annotationProcessor "jakarta.annotation:jakarta.annotation-api"
    annotationProcessor "jakarta.persistence:jakarta.persistence-api"
}

//QueryDSL 설정
def generated = 'src/main/generated' // 개발 단계에서는 gitignore에 generated 포함시키기, 배포하는 경우 주석처리
//def generated = layout.buildDirectory.dir("generated/querydsl").get().asFile  // 배포시 이걸로 바꿔주어야함.

tasks.withType(JavaCompile) {
	options.generatedSourceOutputDirectory = file(generated)
}

---

공부할 때 사용할 예제 Entity 

• 멤버와 팀은 다대일 양방향 관계 (지연로딩)

Member Entity

package study.querydsl.entity;

import jakarta.persistence.*;
import lombok.*;

@Entity
@Getter @Setter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of ={"id","username","age"})
public class Member {
    @Id @GeneratedValue
    @Column(name = "member_id")
    private Long id;
    private String username;
    private int age;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn
    private Team team;
    public Member(String username, int age) {
        this(username,age,null);
    }
    public Member(String username) {
        this(username,0);
    }
    public Member(String username, int age, Team team) {
        this.username = username;
        this.age = age;
        if(team!=null){
            changeTeam(team);
        }
    }
    private void changeTeam(Team team) {
        this.team = team;
        team.getMembers().add(this);
    }
}

Team Entity

package study.querydsl.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import lombok.*;

import java.util.ArrayList;
import java.util.List;

@Entity
@Getter @Setter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of ={"id","name"})
public class Team {
    @Id @GeneratedValue
    private Long id;
    private String name;

    @OneToMany(mappedBy = "team")
    private List<Member> members = new ArrayList<>();

    public Team(String name) {
        this.name = name;
    }
}

.

Q Entity가 생성됐는지 확인해주어야함.

 

// aplication.yml
spring:
  datasource:
    url: jdbc:h2:tcp://localhost/~/querydsl
    username: sa
    password:
    driver-class-name: org.h2.Driver

  jpa:
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
        use_sql_comments: true
        show_sql: true
        format_sql: true

logging.lever:
  org.gibernate.SQL: debug

테스트 데이터

@SpringBootTest
@Transactional 
public class QuerydslBasicTest {

    @Autowired
    EntityManager em;
	
    JPAQueryFactory queryFactory;

    @BeforeEach // 테스트 전 생성할 데이터 
    public void before(){
        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);

    }
}

---

시작 - JPQL  vs Querydsl

• member1을 찾는 쿼리

JPQL

@Test
public void startJPQL(){
    // member1을 찾아라.
    Member findMember = em.createQuery("select m from Member m" +
                    " where m.username =:username", Member.class)
            .setParameter("username", "member1")
            .getSingleResult();

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

Querydsl

• QMember m를 만들어주어야 하는데 static import로 생략이 가능함. 
• JPQL은 오타가 있을 경우 실행해 보기 전까진 오류가 있는지 모르지만, Querydsl 같은 경우에는 메서드로 작동하기 때문에 빌드 전 오류를 확인할 수 있으며, 개인적으론 어떤 데이터를 찾는지 직관적으로 확인할 수 있는 거 같다.

    @Test // queryDsl
    public void startQuerydsl(){ 
//        QMember m = new QMember("m");
//        Member findMember = queryFactory
//                .select(m)
//                .from(m)
//                .where(m.username.eq("member1"))
//                .fetchOne();
        // alt 엔터로 스태틱 임폴트 가능
        Member findMember = queryFactory
                .select(member)
                .from(member)
                .where(member.username.eq("member1"))
                .fetchOne();
        Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
    }

---

다양한 검색조건 쿼리

    @Test // 검색 조건 쿼리
    public void search(){ 
        // member는 Qmember를 의미함.
        Member findMember = queryFactory
                .selectFrom(member)
                .where(member.username.eq("member1")
                        .and(member.age.eq(10))).fetchOne();

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

//         다양한 조건
//        member.username.eq("member1") / username = 'member1'
//        member.username.nq("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(3) // age >= 30
//        member.age.gt(30) // age > 30
//        member.age.loe(30) // age <=30
//        member.age.lt(30) // age < 30

//        membre.username.like("member%") // like 검색
//        member.username.contains("member") // like%$member% 검색
//        member.username.startsWith("member") // like 'member%' 검색
    }

---

검색 결과 조회 

• fetchResults는 단순한 쿼리에서만 사용해야 하며 복잡한 쿼리를 사용할 경우 total count는 따로 구해주어야 함. 

@Test // 검색 결과조회
public void resultFetch(){
    List<Member> fetch = queryFactory
            .selectFrom(member)
            .fetch();

    Member fetchOne = queryFactory
            .selectFrom(member)
            .where(member.username.eq("member1"))
            .fetchOne();

    Member fetchFirst = queryFactory.selectFrom(member)
            .fetchFirst();

    QueryResults<Member> results = queryFactory
            .selectFrom(member)
            .fetchResults(); // 현재 지원X, 단순한 쿼리에서만 사용해주어야함.
    results.getTotal();
    List<Member> content = results.getResults();
}

---

정렬

• member.age.eq() 이런 식으로 메서드를 사용해서 JPQL보다 쉽게 사용할 수 있는 거 같다. 

@Test // 정렬
public void sort(){
    // 회원 정렬 순서
    // 1. 회원 나이 내림차순(desc)
    // 2. 회원 이름 오름차순(asc)
    // 단 2에서 회원 이름이 없으면 마지막에 출력(nulls last)

    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);
    Assertions.assertThat(member5.getUsername()).isEqualTo("member5");
    Assertions.assertThat(member6.getUsername()).isEqualTo("member6");
    Assertions.assertThat(memberNull.getUsername()).isNull();
}

---

페이징

@Test // 페이징
public void paging1(){
    List<Member> result = queryFactory.selectFrom(member)
            .orderBy(member.username.desc())
            .offset(1) // 0부터 시작
            .limit(2)
            .fetch();
    Assertions.assertThat(result.size()).isEqualTo(2);
    Assertions.assertThat(result.size()).isEqualTo(2);
    Assertions.assertThat(result.size()).isEqualTo(2);
}
@Test // 페이징
public void paging2(){
    QueryResults<Member> queryResults = queryFactory.selectFrom(member)
            .orderBy(member.username.desc())
            .offset(1) // 0부터 시작
            .limit(2)
            .fetchResults();
    Assertions.assertThat(queryResults.getTotal()).isEqualTo(4);
    Assertions.assertThat(queryResults.getLimit()).isEqualTo(2);
    Assertions.assertThat(queryResults.getOffset()).isEqualTo(1);
    Assertions.assertThat(queryResults.getResults().size()).isEqualTo(2);
}

---

집합

• Tuple은 Querydsl이 지원해 주는 Tuple을 import 해줌. 
• 반환 타입이 1개가 아닌 경우엔 Tuple을 사용해 줌. 

@Test // 집합
public void aggregation(){
    List<Tuple> result = queryFactory // 쿼리dsl에 있는 Tuple
            .select(
                    member.count(),
                    member.age.sum(),
                    member.age.avg(),
                    member.age.max(),
                    member.age.min()
            ).from(member)
            .fetch();
    // 실무에서는 Tuple을 많이 사용하지는 않음. dto로 조회를 많이 함.
    Tuple tuple = result.get(0);
    Assertions.assertThat(tuple.get(member.count())).isEqualTo(4);
    Assertions.assertThat(tuple.get(member.age.sum())).isEqualTo(100);
    Assertions.assertThat(tuple.get(member.age.avg())).isEqualTo(25);
    Assertions.assertThat(tuple.get(member.age.max())).isEqualTo(40);
    Assertions.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);

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

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

---

Join

Join - on절

    @Test // 조인 - on 절
    public void join_on_filtering(){
        // 예) 회원과 팀을 조인하면서, 팀 이름이 teamA인 팀만 조인, 회원은 모두 조회
        // JPQL: select m, t from Member m left join m.team t on t.name = 'teamA'
        List<Tuple> result = queryFactory
                .select(member, team)
                .from(member)
//                .join(member.team, team).on(team.name.eq("teamA")) // member의 team 이름이 teamA인 것들만 조회
                .leftJoin(member.team, team).on(team.name.eq("teamA")) // member의 team의 이름이 teamA가 아니면 null
                .fetch();

//                .join(member.team, team).on(team.name.eq("teamA")) ==
//                .join(member.team, team).where(team.name.eq("teamA")와 결과가 같음

        for(Tuple tuple:result){
            System.out.println("tuple = " + tuple);
        }
    }
    @Test
    public void join_on_no_relation(){
        // 연관관계가 없는 엔티티 외부 조인
        // 회원의 이름이 팀 이름과 같은 대상 외부 조인
        em.persist(new Member("teamA"));
        em.persist(new Member("teamB"));
        em.persist(new Member("teamC"));

        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("tuple = "+ tuple);
        }
    }

.leftJoin(member.team, team).on(team.name.eq("teamA")) 결과,

세타조인

@PersistenceUnit
EntityManagerFactory emf;

@Test // 페치조인
public void fetchJoinNo(){
    em.flush();
    em.clear();

    Member findMember = queryFactory
            .selectFrom(member)
            .where(member.username.eq("member1"))
            .fetchOne();

    boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam()); // team이 불러와졌는지 확인해주는 메서드

    Assertions.assertThat(loaded).as("페치조인 미적용").isFalse();

    em.flush();
    em.clear();
    // 페치조인
    Member findMember2 = queryFactory
            .selectFrom(member)
            .join(member.team, team).fetchJoin()
            .where(member.username.eq("member1"))
            .fetchOne();

    boolean loaded2 = emf.getPersistenceUnitUtil().isLoaded(findMember2.getTeam()); // team이 불러와졌는지 확인해주는 메서드
    Assertions.assertThat(loaded2).as("페치조인 적용").isTrue();
}

---

서브쿼리

@Test // 서브쿼리
public 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();
    Assertions.assertThat(result).extracting("age").containsExactly(40);
    // 나이가 평균 이상인 회원
    List<Member> result2 = queryFactory
            .selectFrom(member)
            .where(member.age.goe(
                    JPAExpressions
                            .select(memberSub.age.avg())
                            .from(memberSub)
            )).fetch();
    Assertions.assertThat(result2).extracting("age").containsExactly(30,40);
}

• member의 이름과 member의 평균 나이를 같이 출력해 주는 예제 (억지 예제)

@Test // select 서브쿼리
public void selectSubQuery(){
    QMember memberSUb = new QMember("memberSub");
    List<Tuple> result = queryFactory
            .select(member.username,
                    JPAExpressions // static import 가능
                            .select(memberSUb.age.avg())
                            .from(memberSUb))
            .from(member)
            .fetch();
    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

---

case문, 문자와 상수 더하기 

case문

@Test // case문
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);
    }
}

@Test
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(){
    List<Tuple> result = queryFactory
            .select(member.username, Expressions.constant("A"))
            .from(member)
            .fetch();
    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

@Test
public void concat(){
    List<String> result = queryFactory
            .select(member.username.concat("_").concat(member.age.stringValue()))
            .from(member)
            .where(member.username.eq("member1"))
            .fetch();
    for (String s : result) {
        System.out.println("s = " + s);
    }
}

---

JPQL을 직접 사용할 때보다 재밌다. 뭔가 진짜 객체를 가지고 데이터를 조회하는 느낌이 매우 괜찮다고 느낌. 

앞으로 공부 더 예정