Querydsl

Querydsl 설정

• build.gradle

plugins{ //querydsl 추가 id "com.ewerk.gradle.plugins.querydsl" version "1.0.10" } dependencies { //querydsl 추가 implementation 'com.querydsl:querydsl-jpa' } //querydsl 추가 시작 def querydslDir = "$buildDir/generated/querydsl" querydsl { jpa = true querydslSourcesDir = querydslDir } sourceSets { main.java.srcDir querydslDir } configurations { querydsl.extendsFrom compileClasspath} compileQuerydsl { options.annotationProcessorPath = configurations.querydsl } //querydsl 추가 끝

• 검증
  • 우측 실행 : Gradle -Tasks - other - compileQuerydsl
  • 생성 : /generated/querydsl

사용법

• JPAQueryFactory queryFactory = new JPAQueryFactory(em); // 초기에 생성해서 없애기
• QMember m = new QMember("m"); // import 사용으로 없애기
  • queryFactory
    • select(member)
    • from ...
  1. 검색 조건
  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%’ 검색
  1. 결과 조회
  //List List<Member> fetch = queryFactory .selectFrom(member) .fetch(); //단 건 Member findMember1 = queryFactory .selectFrom(member) .fetchOne(); //처음 한 건 조회 Member findMember2 = queryFactory .selectFrom(member) .fetchFirst(); //페이징에서 사용 QueryResults<Member> results = queryFactory .selectFrom(member) .fetchResults(); //count 쿼리로 변경 long count = queryFactory .selectFrom(member) .fetchCount();

페이징

• offset // 시작위치 : 시작
• limit // 개수 제한 : 종료

fetchjoin

@PersistenceUnit EntityManagerFactory emf; @Test public void fetchJoinUse(){ Member findMember = queryFactory .selectFrom(member) .join(member.team, team).fetchJoin() .where(member.username.eq("member1")) .fetchOne(); boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam()); assertThat(loaded).as("페지 조인").isTrue(); }

서브쿼리

• JPAExpressions

@Test public void subQuery(){ QMember memberSub = new QMember("memberSub"); Member result = queryFactory .selectFrom(QMember.member) .where(QMember.member.age.eq( JPAExpressions .select(memberSub.age.max()) .from(memberSub) )) .fetchOne(); assertThat(result.getAge()).isEqualTo(40); }

CASE

• when, then
• otherwise

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

상수더하기

// 상수 더하기 @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); } } // username_age @Test public void concat(){ List<String> result = queryFactory .select(member.username.concat("_").concat(member.age.stringValue())) .from(member) .fetch(); for (String s : result) { System.out.println("s = " + s); } }

프로젝션

• 튜플로 받기

@Test public void tupleProjection(){ List<Tuple> result = queryFactory .select(member.username, member.age) .from(member) .fetch(); for (Tuple tuple : result) { String s = tuple.get(member.username); Integer integer = tuple.get(member.age); System.out.println("s = " + s); System.out.println("integer = " + integer); } }

• ★★ DTO로 받기 ★★
  • setter
  // DTO - projections.bean @Test public void findDtoBySetter(){ List<MemberDto> result = queryFactory .select(Projections.bean(MemberDto.class, member.username, member.age)) .from(member) .fetch(); for (MemberDto memberDto : result) { System.out.println("memberDto = " + memberDto); } }
  • field
    • 이름이 다르면 .as(member.username, "name")
  // DTO - field @Test public void findDtoBySetterField(){ List<MemberDto> result = queryFactory .select(Projections.fields(MemberDto.class, member.username, member.age)) .from(member) .fetch(); for (MemberDto memberDto : result) { System.out.println("memberDto = " + memberDto); } }
  • constructor
  // DTO - constructor @Test public void findDtoBySetterConstructor(){ List<MemberDto> result = queryFactory .select(Projections.constructor(MemberDto.class, member.username, member.age)) .from(member) .fetch(); for (MemberDto memberDto : result) { System.out.println("memberDto = " + memberDto); } }
• @QueryProjection
  • compileQuerydsl 실행해야함.
  • querydsl에 의존적이라는 단점
  • 쿼리 DTO를 생성해야 한다는 단점

@QueryProjection public MemberDto(String username, int age) { this.username = username; this.age = age; } @Test public void findDtoByQueryProjection(){ List<MemberDto> result = queryFactory .select(new QMemberDto(member.username, member.age)) .from(member) .fetch(); for (MemberDto memberDto : result) { System.out.println("memberDto = " + memberDto); } }

동적쿼리

• BooleanBuilder
  • where구문을 추가

@Test public void dynamicQuery_BooleanBuilder(){ String usernameParam = "member1"; Integer ageParam = null; List<Member> result = searchMember1(usernameParam, ageParam); assertThat(result.size()).isEqualTo(1); } private List<Member> searchMember1(String usernameParam, Integer ageParam) { BooleanBuilder builder = new BooleanBuilder(); if (usernameParam != null) { //null이 아니면 and구문 추가 builder.and(member.username.eq(usernameParam)); } if (ageParam != null) { //null이 아니면 and구문 추가 builder.and(member.age.eq(ageParam)); } return queryFactory .selectFrom(member) .where(builder) // 추가 .fetch(); }

• Where 다중 파라미터 ★
  • 쿼리문만 보고 파악하기 쉬움
  • 조합이 가능해짐(함수)

// 동적쿼리 - 2 @Test public void dynamicQuery_WhereParam(){ String usernameParam = "member1"; Integer ageParam = null; List<Member> result = searchMember2(usernameParam, ageParam); assertThat(result.size()).isEqualTo(1); } private List<Member> searchMember2(String usernameParam, Integer ageParam) { return queryFactory .selectFrom(member) .where(usernameEq(usernameParam), ageEq(ageParam)) .fetch(); } private Predicate usernameEq(String usernameParam) { return usernameParam == null ? null : member.username.eq(usernameParam); } private Predicate ageEq(Integer ageParam) { return ageParam == null ? null : member.age.eq(ageParam); }