QDSL - 기본 문법
2022. 12. 8. 09:42ㆍBackEnd(Java)/QueryDSL
이 글은 김영한님의 QueryDSL 강의를 듣고 정리한 글입니다.
where 조건을 사용한 특정 멤버 찾기(JPQL VS Querydsl )
@DisplayName("특정 멤버를 찾는 JPQL 예시")
@Test
public void startJPQL() {
//given, then
//member1을 찾아라
Member findMember = em.createQuery("select m from Member m where m.username =:username", Member.class)
.setParameter("username", "member1")
.getSingleResult();
//then
assertThat(findMember.getUsername()).isEqualTo("member1");
}
@DisplayName("특정 멤버를 찾는 Qdsl 예시")
@Test
public void startQdsl() {
/**
* Q Type은 아래 코드 처럼 static import 하여 깔끔하게 사용하도록 하자
*/
Member findMember = queryFactory.selectFrom(member)
.where(member.username.eq("member1"))
.fetchOne();
assertThat(findMember.getUsername()).isEqualTo("member1");
}
where 조건을 사용한 특정 멤버 찾기
@DisplayName("where 필터 조건 and 사용")
@Test
public void search() {
/**
* 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%’ 검색
*/
//given
//when
Member findMember = queryFactory.selectFrom(member)
.where(member.username.eq("member1").
and(member.age.eq(10))
).fetchOne();
List<Member> memberList = queryFactory.selectFrom(member)
.where(member.username.startsWith("member"))
.fetch();
memberList.forEach(System.out::println);
//then
assertThat(findMember.getUsername()).isEqualTo("member1");
assertThat(findMember.getAge()).isEqualTo(10);
}
@DisplayName("where의 and를 ,(쉼표)를 사용하여 구분하는 방법")
@Test
public void searchAndParam() {
//given
//when
Member findMember = queryFactory.selectFrom(member)
.where(
member.username.eq("member1"), //and를 안쓰고 ,(쉼표)로 구분이 가능함
member.age.eq(10)
).fetchOne();
//then
System.out.println("findMember = " + findMember);
}
결과 조회(fetch, fetchFrist, fetchResult 등...)
@DisplayName("fetch")
@Test
public void resultFetch() {
//fetch() : List로 조회
List<Member> members = queryFactory.selectFrom(member)
.fetch();
//fetchOne() : 단건 조회
Member fetchOne = queryFactory.selectFrom(member)
// .where(member.username.eq("member1")) //단건 조회가 아니면 NonUniqueResultException: query did not return a unique result 예외 발생
.fetchOne();
Member fetchFirst = queryFactory.selectFrom(member)
.fetchFirst();
//페이징 + 데이터 => select 쿼리 + count 쿼리가 같이 나간다
QueryResults<Member> results = queryFactory.selectFrom(member)
.fetchResults();
long total = results.getTotal();
List<Member> results1 = results.getResults();
long limit = results.getLimit();
//count 쿼리
long count = queryFactory.selectFrom(member)
.fetchCount();
/**
* fetch() : 리스트 조회, 데이터 없으면 빈 리스트 반환
* fetchOne() : 단 건 조회,
* -> 결과가 없으면 : null
* -> 결과가 둘 이상이면 : com.querydsl.core.NonUniqueResultException
* fetchFirst() : limit(1).fetchOne()
* fetchResults() : 페이징 정보 포함, total count 쿼리 추가 실행
* fetchCount() : count 쿼리로 변경해서 count 수 조회
*/
}
정렬
/**
* 회원 정렬 순서
* 1. 회원 나이 내림차순
* 2. 회원 이름 오름차순
* 단 2에서 회원 이름이 없으면 마지막에 출력(nulls last)
*/
@DisplayName("정렬")
@Test
public void sort() {
//given
em.persist(new Member(null, 100));
em.persist(new Member("member5", 100));
em.persist(new Member("member6", 100));
//when
List<Member> members = queryFactory.selectFrom(member)
.where(member.age.eq(100))
.orderBy(member.age.desc(), member.username.asc().nullsLast())
.fetch();
members.forEach(System.out::println);
//then
assertThat(members.get(0).getUsername()).isEqualTo("member5");
assertThat(members.get(1).getUsername()).isEqualTo("member6");
assertThat(members.get(2).getUsername()).isNull();
}
페이징
@DisplayName("페이징 1")
@Test
public void paging1() {
/**
* select
* member0_.member_id as member_i1_1_,
* member0_.age as age2_1_,
* member0_.team_id as team_id4_1_,
* member0_.username as username3_1_
* from
* member member0_
* order by
* member0_.username desc limit ? offset ?
*/
List<Member> members = queryFactory
.selectFrom(member)
.orderBy(member.username.desc())
.offset(1)
.limit(2)
.fetch();
assertThat(members.size()).isEqualTo(2);
/**
* Select * from 테이블명 orders LIMIT 숫자(★) OFFSET 숫자(♥);
* LIMIT 숫자 : 출력할 행의 수
* OFFSET 숫자 : 몇번째 row부터 출력할 지. (1번째 row면 0)
*
* Ex) 10행씩 출력
* 1페이지 : select * from member ORDERS LIMIT 10 OFFSET 0;
* 2페이지 : select * from member ORDERS LIMIT 10 OFFSET 10;
*/
}
@DisplayName("페이징 2")
@Test
public void paging2() {
//given
//when
QueryResults<Member> fetchResults = queryFactory
.selectFrom(member)
.orderBy(member.username.desc())
.offset(1)
.limit(3)
.fetchResults();
fetchResults.getResults()
.forEach(System.out::println);
//then
assertThat(fetchResults.getTotal()).isEqualTo(4);
assertThat(fetchResults.getLimit()).isEqualTo(3);
assertThat(fetchResults.getOffset()).isEqualTo(1);
}
집합
@DisplayName("집합")
@Test
public void aggregation() {
//given
List<Tuple> result = queryFactory
.select(
member.count(),
member.age.sum(),
member.age.avg()
)
.from(member)
.fetch();
//when
Tuple tuple = result.get(0);
Long count = tuple.get(member.count());
Integer age = tuple.get(member.age.sum());
Double avg = tuple.get(member.age.avg());
/**
* select
* count(member0_.member_id) as col_0_0_,
* sum(member0_.age) as col_1_0_,
* avg(cast(member0_.age as double)) as col_2_0_
* from
* member member0_
*/
//then
}
집합 - 그룹바이
/**
* 팀의 이름과 각 팀의 평균 연령을 구해라
*/
@DisplayName("집합 - 그룹바이")
@Test
public void groupby() {
//given
List<Tuple> result = queryFactory
.select(team.name, member.age.avg())
.from(member)
.join(member.team, team)
.groupBy(team.name)
.fetch();
/**
* select
* team1_.name as col_0_0_,
* avg(cast(member0_.age as double)) as col_1_0_
* from
* member member0_
* inner join
* team team1_
* on member0_.team_id=team1_.id
* group by
* team1_.name
*/
//when
Tuple teamA = result.get(0);
Tuple teamB = result.get(1);
//then
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);
}
조인
@DisplayName("팀 A에 소속된 모든 회원")
@Test
public void join() {
//given
List<Member> members = queryFactory
.selectFrom(member)
.join(member.team, team)
.where(member.team.name.eq("TeamA"))
.fetch();
//when
members.forEach(System.out::println);
//then
assertThat(members)
.extracting("username")
.containsExactly("member1", "member2");
}
세타 조인
@DisplayName("세타조인 - 회원의 이름이 팀 이름과 같은 회원 조회")
@Test
public void thetaJoin() {
//given
em.persist(new Member("teamA"));
em.persist(new Member("teamB"));
em.persist(new Member("teamC"));
//when
List<Member> result = queryFactory
.select(member)
.from(member, team)
.where(member.username.eq(team.name))
.fetch();
/**
* select
* member0_.member_id as member_i1_1_,
* member0_.age as age2_1_,
* member0_.team_id as team_id4_1_,
* member0_.username as username3_1_
* from
* member member0_ cross
* join
* team team1_
* where
* member0_.username=team1_.name
*/
//then
assertThat(result)
.extracting("username")
.containsExactly("teamA", "teamB");
}
아우터 조인
/**
* JPQL : select m, t from Member m left join m.team t on t.name ='teamA'
*/
@DisplayName("회원과 팀을 조인하면서, 팀 이름이 teamA인 팀만 조인 회원은 모두 조회")
@Test
public void join_on_filtering() {
//given
List<Tuple> result = queryFactory
.select(member, team) //select가 여러가지 타입을 리턴해서 tuple을 리턴함
.from(member)
.leftJoin(member.team, team).on(team.name.eq("teamA"))
.fetch();
//when
result.forEach(System.out::println);
/**
* select
* member0_.member_id as member_i1_1_0_,
* team1_.id as id1_2_1_,
* member0_.age as age2_1_0_,
* member0_.team_id as team_id4_1_0_,
* member0_.username as username3_1_0_,
* team1_.name as name2_2_1_
* from
* member member0_
* left outer join
* team team1_
* on member0_.team_id=team1_.id and (team1_.name='teamA')
*/
//then
}
패치 조인(미사용, 사용)
@DisplayName("fetch조인 미사용")
@Test
public void fetchJoinNo() {
//given
em.flush();
em.clear();
//when
Member findMember = queryFactory
.selectFrom(member)
.where(member.username.eq("member1"))
.fetchOne();
// String teamName = findMember.getTeam().getName();
// log.warn("teamName = {} ",teamName);
boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam());//로딩된 엔티티인지 확인
log.warn("loaded = {} ", loaded);
//then
assertThat(findMember.getUsername()).isEqualTo("member1");
}
@DisplayName("fetch조인 사용")
@Test
public void fetchJoinUse() {
//given
em.flush();
em.clear();
//when
Member findMember = queryFactory
.selectFrom(member)
.join(member.team, team).fetchJoin()
.where(member.username.eq("member1"))
.fetchOne();
boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam());//로딩된 엔티티인지 확인
log.warn("loaded = {} ", loaded);
/**
* 패치 조인 쿼리
* select
* member0_.member_id as member_i1_1_0_,
* team1_.id as id1_2_1_,
* member0_.age as age2_1_0_,
* member0_.team_id as team_id4_1_0_,
* member0_.username as username3_1_0_,
* team1_.name as name2_2_1_
* from
* member member0_
* inner join
* team team1_
* on member0_.team_id=team1_.id
* where
* member0_.username=?
*/
//then
assertThat(findMember.getUsername()).isEqualTo("member1");
assertThat(loaded).isTrue();
}
서브쿼리
@DisplayName("나이가 가장 많은 회원 조회")
@Test
public void subQuery() {
//given
QMember memberSub = new QMember("memberSub");
//when
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.eq(
JPAExpressions.
select(memberSub.age.max())
.from(memberSub)
))
.fetch();
/**
* select
* member0_.member_id as member_i1_1_,
* member0_.age as age2_1_,
* member0_.team_id as team_id4_1_,
* member0_.username as username3_1_
* from
* member member0_
* where
* member0_.age=(
* select
* max(member1_.age)
* from
* member member1_
* )
*/
//then
assertThat(result).extracting("age").containsExactly(40);
}
@DisplayName("나이가 평균 이상인 회원")
@Test
public void subQuerGoe() {
//given
QMember memberSub = new QMember("memberSub");
//when
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.goe(
JPAExpressions.
select(memberSub.age.avg())
.from(memberSub)
))
.fetch();
/**
* select
* member0_.member_id as member_i1_1_,
* member0_.age as age2_1_,
* member0_.team_id as team_id4_1_,
* member0_.username as username3_1_
* from
* member member0_
* where
* member0_.age>=(
* select
* avg(cast(member1_.age as double))
* from
* member member1_
* )
*/
assertThat(result).extracting("age").containsExactly(30, 40);
}
@DisplayName("in 쿼리 예시")
@Test
public void subQueryIn() {
//given
QMember memberSub = new QMember("memberSub");
//when
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.in(
JPAExpressions
.select(memberSub.age)
.from(memberSub)
.where(memberSub.age.gt(10))
))
.fetch();
/**
* select
* member0_.member_id as member_i1_1_,
* member0_.age as age2_1_,
* member0_.team_id as team_id4_1_,
* member0_.username as username3_1_
* from
* member member0_
* where
* member0_.age in (
* select
* member1_.age
* from
* member member1_
* where
* member1_.age>?
* )
*/
assertThat(result).extracting("age").containsExactly(20, 30, 40);
}
JPQL 서브쿼리 이슈
/**
* [이슈]
* JPA - JPQL 서브쿼리의 한계점으로 from절 서브쿼리(인라인 뷰)는 지원하지 않는다. 당연히 QDSL도 지원안함
* <p>
* [해결 방안]
* 1. 서브쿼리를 join으로 변경한다
* 2. 쿼리를 2번 분리해서 실행한다
* 3. nativeSQL을 사용한다.
*/
@DisplayName("스칼라 서브(select 절)쿼리")
@Test
public void selectSubQuery() {
//given
QMember memberSub = new QMember("memberSub");
//when
List<Tuple> result = queryFactory
.select(member.username,
JPAExpressions
.select(memberSub.age.avg())
.from(memberSub))
.from(member)
.fetch();
/**
* select
* member0_.username as col_0_0_,
* (select
* avg(cast(member1_.age as double))
* from
* member member1_) as col_1_0_
* from
* member member0_
*/
//then
result.forEach(System.out::println);
}
CASE문
@DisplayName("case문 예시")
@Test
public void basicCase() {
//given
List<String> result = queryFactory
.select(member.age
.when(10).then("열살")
.when(20).then("스무살")
.otherwise("기타"))
.from(member)
.fetch();
/**
* select
* case
* when member0_.age=? then ?
* when member0_.age=? then ?
* else '기타'
* end as col_0_0_
* from
* member member0
*/
//when
result.forEach(System.out::println);
//then
}
/**
* DB에서는 raw 데이터를 필터링하고, 그룹핑하고, 필요하면 계산하지만 DB에 부하가 안되게 최소한의 쿼리를 작성한다.(DB 비용이 크닌깐)
* WAS(Application)에서 갖고와서 해결해야한다!
*/
@DisplayName("복잡한 case문")
@Test
public void complexCase() {
//given
CaseBuilder caseBuilder = new CaseBuilder();
List<String> result = queryFactory
.select(caseBuilder
.when(member.age.between(0, 20)).then("0~20살")
.when(member.age.between(0, 20)).then("0~20살")
.otherwise("늙은이")
).from(member)
.fetch();
//when
result.forEach(System.out::println);
//then
}참고자료
https://www.inflearn.com/course/querydsl-%EC%8B%A4%EC%A0%84/dashboard
반응형