- QClass 생성시 참고사항
src/test/java에서 저걸 주석처리 해주고
gradle refresh, gradle tasks run 해준다.
1. 기본 Q-Type 활용
- Q클래스 인스턴스를 사용하는 2가지 방법
- QMember qMember = new QMember("m"); // 별칭을 직접 지정
- QMember qMember = QMember.member; // 기본 인스턴스를 사용
2. JPQL이 제공하는 검색 조건 제공
-
member.username.eqq("member2D) // username = 'member1'
-
member.username.ne("member") // 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 '%memeber%' 검색
-
member.username.startWith("member") // like 'member%' 검색
3. 결과 조회
- fetch() : 리스트 조회, 데이터 없으면 빈 리스트 반환
- fetchOne() : 단 건 조회
-> 결과가 없으면 : null
-> 결과가 둘 이상이면 : NotUniqueResultException - fetchFirst() : limit(1).fetchOne()
- fetchResults(): 페이징 정보를 포함, total count 쿼리 추가 실행.(권장하지 않음. 사용X)
- fetchCount() : count 쿼리로 변경해서 count 수 조회(권장하지 않음. 그냥 count를 쓰면 됨)
4. 정렬
- desc(), asc() : 일반 정렬
- nullsLast(), nullsFirst() : null 데이터 순서 부여
5. 집합
- count(m) : 회원수
- sum(m.age) : 나이 합
- avg(m.age) : 평균 나이
- max(m.age) : 최대 나이
- min(m.age) : 최소 나이
6. 조인 - on절
package com.codingbox.querydsl.main;
// static을 포함한 전체를 import한다.
import static com.codingbox.querydsl.entity.QMember.*;
import static com.codingbox.querydsl.entity.QTeam.*;
import java.util.List;
import com.codingbox.querydsl.entity.Member;
import com.codingbox.querydsl.entity.QMember;
import com.codingbox.querydsl.entity.Team;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQueryFactory;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.EntityTransaction;
import jakarta.persistence.Persistence;
public class Main06 {
public static void main(String[] args) {
EntityManagerFactory emf =
Persistence.createEntityManagerFactory("hello");
EntityManager em = emf.createEntityManager();
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
EntityTransaction tx = em.getTransaction();
tx.begin();
try {
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);
Member member5 = new Member(null, 100, teamB);
Member member6 = new Member("member6", 100, teamB);
Member member7 = new Member("member7", 100, teamB);
em.persist(member1);
em.persist(member2);
em.persist(member3);
em.persist(member4);
em.persist(member5);
em.persist(member6);
em.persist(member7);
// 초기화
em.flush();
em.clear();
// inner join
List<Tuple> result2 = queryFactory
.select(member, team)
.from(member)
.join(member.team, team).on(team.name.eq("teamA"))
.fetch();
for( Tuple tuple : result2 ) {
System.out.println("tuple = " + tuple);
}
// inner join에 on절과 where절 둘 다 써도 상관없지만 where절을 더 권장함.(가독성)
List<Tuple> result3 = queryFactory
.select(member, team)
.from(member)
.join(member.team, team)
.where(team.name.eq("teamA"))
.fetch();
for( Tuple tuple : result3 ) {
System.out.println("tupleWhere = " + tuple);
}
/*
* 회원과 팀을 조인하면서, 팀 이름이 teamA인 팀만 조인, 회원 모두 조회
* SQL : select m.*, t.*
* from Member m
* left (outer) join Team t
* ON m.team_id = t.id and t.name = '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)
// .leftJoin(member.team, team).on(team.name.eq("teamA"))
// .fetch();
// on절만 있는 것과 where절이 추가 된 것은 조회되는 row수가 다르다.
List<Tuple> result = queryFactory
.select(member, team)
.from(member)
.leftJoin(member.team, team).on(team.name.eq("teamA"))
.where(team.name.eq("teamA"))
.fetch();
for( Tuple tuple : result ) {
System.out.println("tuple = " + tuple);
}
tx.commit();
} catch(Exception e) {
tx.rollback();
} finally {
em.close();
emf.close();
}
}
}
7. 서브쿼리
- from절에 서브 쿼리 한계가 존재한다.
// select 절에서 subquery
QMember memberSub4 = new QMember("memberSub4");
List<Tuple> fetch = queryFactory
.select(member.username,
JPAExpressions
.select(memberSub4.age.avg())
.from(memberSub4))
.from(member)
.fetch();
for( Tuple tuple : fetch ) {
System.out.println("username = " + tuple.get(member.username));
System.out.println("age = " +
tuple.get(JPAExpressions.select(memberSub4.age.avg())
.from(memberSub4)));
}
// in 절
QMember memberSub3 = new QMember("memberSub3");
List<Member> result3 = queryFactory
.selectFrom(member)
.where(member.age.in(
JPAExpressions.select(memberSub3.age)
.from(memberSub3)
.where(memberSub3.age.gt(10))))
.fetch();
// 나이가 평균 나이 이상인 회원
QMember memberSub2 = new QMember("memberSub2");
List<Member> result2 = queryFactory
.selectFrom(member)
.where(member.age.goe(
JPAExpressions.select(memberSub2.age.avg())
.from(memberSub2)
))
.fetch();
System.out.println("result2 : " + result2.get(0).getAge());
// 서브쿼리 - 나이가 가장 많은 회원 조회
QMember memberSub = new QMember("memberSub");
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.eq(
JPAExpressions.select(memberSub.age.max())
.from(memberSub)
))
.fetch();
System.out.println("result : " + result.get(0).getAge());8. Case문
// Case 문
List<String> result = queryFactory
.select(
member.age
.when(10).then("열살")
.when(20).then("스무살")
.otherwise("기타")
)
.from(member)
.fetch();
// 복잡한 조건
List<String> result2 = 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 : result2 ) {
System.out.println("s : " + s);
}9. 프로젝션과 결과 반환
List<String>List<Member>List<Tuple>: 대상이 둘 이상일 때, 특정할 수 없을 때- 결과값 반환 : DTO
9-1. 결과값 반환 QueryDSL
- 프로퍼티 접근
- 필드 직접 접근
- 생성자 사용
개발자