on
Querydsl
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 ... 검색 조건 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%’ 검색 결과 조회 //List List fetch = queryFactory .selectFrom(member) .fetch(); //단 건 Member findMember1 = queryFactory .selectFrom(member) .fetchOne(); //처음 한 건 조회 Member findMember2 = queryFactory .selectFrom(member) .fetchFirst(); //페이징에서 사용 QueryResults results = queryFactory .selectFrom(member) .fetchResults(); //count 쿼리로 변경 long count = queryFactory .selectFrom(member) .fetchCount();
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%’ 검색 //List List fetch = queryFactory .selectFrom(member) .fetch(); //단 건 Member findMember1 = queryFactory .selectFrom(member) .fetchOne(); //처음 한 건 조회 Member findMember2 = queryFactory .selectFrom(member) .fetchFirst(); //페이징에서 사용 QueryResults 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 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 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 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 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 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 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 result = queryFactory .select(Projections.constructor(MemberDto.class, member.username, member.age)) .from(member) .fetch(); for (MemberDto memberDto : result) { System.out.println("memberDto = " + memberDto); } }
// DTO - projections.bean @Test public void findDtoBySetter(){ List result = queryFactory .select(Projections.bean(MemberDto.class, member.username, member.age)) .from(member) .fetch(); for (MemberDto memberDto : result) { System.out.println("memberDto = " + memberDto); } } // DTO - field @Test public void findDtoBySetterField(){ List result = queryFactory .select(Projections.fields(MemberDto.class, member.username, member.age)) .from(member) .fetch(); for (MemberDto memberDto : result) { System.out.println("memberDto = " + memberDto); } } // DTO - constructor @Test public void findDtoBySetterConstructor(){ List 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 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 result = searchMember1(usernameParam, ageParam); assertThat(result.size()).isEqualTo(1); } private List 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 result = searchMember2(usernameParam, ageParam); assertThat(result.size()).isEqualTo(1); } private List 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); }
from http://jaehhh.tistory.com/21 by ccl(A) rewrite - 2021-10-04 05:28:00