초기설정 + Querydsl 세팅(boot3.x이상)
// Query dsl 추가
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 Build Options
*/
def querydslDir = "src/main/generated"
sourceSets {
main.java.srcDirs += [ querydslDir ]
}
tasks.withType(JavaCompile).configureEach {
options.getGeneratedSourceOutputDirectory().set(file(querydslDir))
}
clean.doLast {
file(querydslDir).deleteDir()
}
- gitignore 추가
/src/main/generated/
로깅 라이브러리 추가
// P6Spy 의존성 추가
implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.9.0'
JPQL, Querydsl 사용법 비교
- JPQL
Member findMember = em.createQuery("select m from Member m "
+ "where m.username = :username", Member.class)
.setParameter("username", "member1")
.getSingleResult();
assertThat(findMember.getUsername()).isEqualTo("member1");
- Querydsl
@Autowired
EntityManager em;
JPAQueryFactory queryFactory;
@Test
public void startQuerydsl() {
queryFactory = new JPAQueryFactory(em);
QMember m = QMember.member;
Member findMember = queryFactory
.select(m)
.from(m)
.where(m.username.eq("member1"))
.fetchOne();
assertThat(findMember.getUsername()).isEqualTo("member1");
}
기본 조인
- 조인의 기본 문법은 첫 번째 파라미터에 조인 대상을 지정하고, 두 번째 파라미터에 별칭(alias)으로 사용할 Q타입을 지정하면 된다.
- join(조인 대상, 별칭으로 사용할 Q타입)
@Test
public void join() throws Exception {
QMember member = QMember.member;
QTeam team = QTeam.team;
List<Member> result = queryFactory
.selectFrom(member)
.join(member.team, team)
.where(team.name.eq("teamA"))
.fetch();
assertThat(result)
.extracting("username")
.containsExactly("member1", "member2");
}
세타 조인
- 연관관계가 없는 필드로 조인
List<Member> result = queryFactory
.select(member)
.from(member)
.join(team)
.on(member.username.eq(team.name))
.fetch();
List<Member> result2 = queryFactory
.select(member)
.from(member, team)
.where(member.username.eq(team.name))
.fetch();
연관관계 없는 엔티티 외부 조인
- 하이버네이트 5.1부터 on을 사용해서 서로 관계가 없는 필드로 외부 조인하는 기능이 추가되었음
- 일반조인 : leftJoin(조인대상, 별칭으로 사용할 Q타입)
- on조인 : from(조인대상1).leftJoin(조인대상2).on(조건)
List<Tuple> result = queryFactory
.select(member, team)
.from(member)
.leftJoin(team).on(member.username.eq(team.name))
.fetch();
페치 조인 적용
Member findMember = queryFactory
.selectFrom(member)
.join(member.team, team).fetchJoin()
.where(member.username.eq("member1"))
.fetchOne();
- join(), leftJoin() 등 조인 기능 뒤에 fetchJoin()이라고 추가하면 된다.
서브쿼리
- JPAExpressions
import com.querydsl.jpa.JPAExpressions;
QMember memberSub = new QMember("memberSub");
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.eq(
select(memberSub.age.max())
.from(memberSub)
))
.fetch();
CASE WHEN THEN
public void caseWhenThen() {
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();
}
/**
* 예를 들어 다음과 같은 임의의 순서로 회원을 출력하고 싶다면?
* 1. 0~30살이 아닌 회원을 가장 먼저 출력
* 2. 0~20살 회원 출력
* 3. 21~30살 회원 출력
*/
public void orderByAndCase() {
NumberExpression<Integer> rankPath = new CaseBuilder()
.when(member.age.between(0, 20)).then(2)
.when(member.age.between(21, 30)).then(1)
.otherwise(3);
List<Tuple> result = queryFactory
.select(member.username, member.age, rankPath)
.from(member)
.orderBy(rankPath.desc())
.fetch();
for (Tuple tuple : result) {
String username = tuple.get(member.username);
Integer age = tuple.get(member.age);
Integer rank = tuple.get(rankPath);
System.out.println("username = " + username + " age = " + age + " rank = " + rank);
}
}
상수, 문자 더하기(constant, concat)
/**
* 참고: 아래와 같이 최적화가 가능하면 SQL에 constant 값을 넘기지 않는다.
* 상수를 더하는 것처럼 최적화가 어려우면 SQL에 constant 값을 넘긴다.
*/
@Test
public void strPlus() {
Tuple result = queryFactory
.select(member.username, Expressions.constant("A"))
.from(member)
.fetchFirst();
}
/**
* 문자 더하기 concat
*/
@Test
public void concat() {
String result = queryFactory
.select(member.username.concat("_").concat(member.age.stringValue()))
.from(member)
.where(member.username.eq("member1"))
.fetchOne();
// 참고: member.age.stringValue() 부분이 중요한데, 문자가 아닌 다른 타입들은 stringValue()로 문자로
// 변환할 수 있다. 이 방법은 ENUM을 처리할 때도 자주 사용한다.
}
프로젝션 결과 반환
@Test
public void simpleProjection() {
List<String> result = queryFactory
.select(member.username)
.from(member)
.fetch();
}
@Test
public void tupleProjection() {
List<Tuple> result = queryFactory
.select(member.username, member.age)
.from(member)
.fetch();
}
JPQL DTO 프로젝션
List<MemberDto> result = em.createQuery
("select new study.querydsl.dto.MemberDto(m.username, m.age) from Member m",
MemberDto.class)
.getResultList();
Querydsl setter 프로젝션
List<MemberDto> result = queryFactory
.select(Projections.bean(MemberDto.class
, member.username, member.age))
.from(member)
.fetch();
Querydsl 필드 프로젝션
@Test
public void findDtoByField() { // getter setter가 없어도 됨, 필드에다 값을 넣어줌
List<MemberDto> result = queryFactory
.select(Projections.fields(MemberDto.class
, member.username, member.age))
.from(member)
.fetch();
}
Querydsl 생성자 프로젝션
@Test
public void findDtoByConstructor() {
List<MemberDto> result = queryFactory
.select(Projections.constructor(MemberDto.class
, member.username, member.age)) // 타입 순서가 맞아야 함(생성자)
.from(member)
.fetch();
}
필드명이 다를 경우 as 사용
List<UserDto> result = queryFactory
.select(Projections.constructor(UserDto.class
, member.username.as("name"), member.age)) // 타입 순서가 맞아야 함(생성자)
.from(member)
.fetch()
ExpressionUtils를 활용한 서브쿼리 + 프로젝션
import com.querydsl.core.types.ExpressionUtils;
QMember memberSub = new QMember("memberSub");
List<UserDto> result = queryFactory
.select(Projections.constructor(UserDto.class
, member.username.as("name")
, ExpressionUtils.as(JPAExpressions
.select(memberSub.age.max())
.from(memberSub), "age")
))
.from(member)
.fetch();
QueryProjection
@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();
}
동적 쿼리 - BooleanBuilder
@Test
public void dynamicQuery_BooleanBuilder() {
String usernameParam = "member1";
Integer ageParam = null;
List<Member> result = searchMember1(usernameParam, ageParam);
assertThat(result).hasSize(1);
}
private List<Member> searchMember1(String usernameCond, Integer ageCond) {
BooleanBuilder builder = new BooleanBuilder();
if (usernameCond != null) {
builder.and(member.username.eq(usernameCond));
}
if (ageCond != null) {
builder.and(member.age.eq(ageCond));
}
return queryFactory
.selectFrom(member)
.where(builder)
.fetch();
}
동적 쿼리 - Where 다중 파라미터 사용(1)
@Test
public void dynamicQuery_WhereParam() {
String usernameParam = "member1";
Integer ageParam = 10;
List<Member> result = searchMember2(usernameParam, ageParam);
assertThat(result).hasSize(1);
}
private List<Member> searchMember2(String usernameCond, Integer ageCond) {
return queryFactory
.selectFrom(member)
.where(usernameEq(usernameCond), ageEq(ageCond))
.fetch();
}
private Predicate usernameEq(String usernameCond) {
if (usernameCond == null) {
return null;
}
return member.username.eq(usernameCond);
}
private Predicate ageEq(Integer ageCond) {
if (ageCond == null) {
return null;
}
return member.age.eq(ageCond);
}
동적 쿼리 - Where 다중 파라미터 사용(2)
private Predicate usernameEq(String usernameCond) {
return usernameCond != null ? member.username.eq(usernameCond) : null;
}
동적 쿼리 - Where 다중 파라미터 사용(3) BooleanExpression
return queryFactory
.selectFrom(member)
.where(allEq(usernameCond, ageCond))
.fetch();
private BooleanExpression usernameEq(String usernameCond) {
return usernameCond != null ? member.username.eq(usernameCond) : null;
}
private BooleanExpression ageEq(Integer ageCond) {
return ageCond != null ? member.age.eq(ageCond) : null;
}
private BooleanExpression allEq(String usernameCond, Integer ageCond) {
return usernameEq(usernameCond).and(ageEq(ageCond));
}
수정, 삭제 벌크 연산
@Test
@Commit
public void bulkUpdate() {
// 영속성 컨텍스트 무시하고 쿼리가 나감
// DB 와 영속성 컨텍스트 간의 상태가 불일치
long count = queryFactory
.update(member)
.set(member.username, "비회원")
.where(member.age.lt(28))
.execute();
em.flush(); // 데이터 보내고
em.clear(); // 영속성 컨텍스트 비우기(초기화)
List<Member> result = queryFactory
.selectFrom(member)
.fetch();
for (Member member1 : result) {
System.out.println("member1 = " + member1);
}
}
@Test
public void bulkAdd() {
long count = queryFactory
.update(member)
.set(member.age, member.age.add(1))
.execute();
}
@Test
public void bulkMultiply() {
long count = queryFactory
.update(member)
.set(member.age, member.age.multiply(2))
.execute();
}
@Test
public void bulkDelete() {
long count = queryFactory
.delete(member)
.where(member.age.gt(18))
.execute();
}
SQL function 호출하기
@Test
public void sqlFunction() {
List<String> result = queryFactory
.select(
Expressions.stringTemplate(
"function('replace', {0}, {1}, {2})",
member.username, "member", "M"))
.from(member)
.fetch();
}
@Test
public void sqlFunction2() {
List<String> result = queryFactory
.select(member.username)
.from(member)
.where(member.username.eq(member.username.lower()))
.fetch();
}