안녕하세요. J4J입니다.
이번 포스팅은 JPA에서 QueryDSL 사용하는 방법에 대해 적어보는 시간을 가져보려고 합니다.
QueryDSL이란?
QueryDSL은 자바 코드를 이용하여 데이터베이스 query를 작성할 수 있게 도와줍니다.
일반적으로 JPA를 사용하여 개발하면 JPARepository를 상속받은 인터페이스 파일을 만들어 활용하고는 합니다.
하지만 JPARepository가 가지는 한계가 있기 때문에 상황에 따라 JPQL문을 직접 작성하기도 합니다.
여기서 문제점은 JPQL을 작성을 했지만 에러가 발생될 수 있는 코드임에도 불구하고 디버깅 단계 때 확인이 불가합니다.
결국 런타임까지 넘어갔을 때 에러가 발생될 것이고 그때서야 잘못된 코드임을 확인하여 수정작업이 이루어집니다.
게다가 개인적으로는 JPQL을 남발하면 MyBatis를 사용하는 것과 다름이 없지 않나라는 생각도 들기도 합니다.
이런 문제점들을 QueryDSL이 보완해줄 수 있습니다.
자바 코드로 작성이 되기 때문에 잘못된 코드를 작성할 경우 디버깅 단계때 확인이 가능해지며 안정성을 제공해 줍니다.
또한 JPARepository보다 더 유동적으로 query 코드를 작성할 수 있게 해줍니다.
하지만 특정 상황에서는 JPARepository가 더 편리하게 사용될 수 있습니다.
그렇기 때문에 JPARepository + JPQL을 같이 사용하던 것을 JPARepository + QueryDSL로 대체해주면 더 멋진 코드가 작성될 것이라고 생각합니다.
추가적으로 QueryDSL의 친구로 Criteria Query가 있습니다.
Criteria Query를 사용해본 적은 없어서 개인적인 의견은 말씀드릴 순 없지만 여러 레퍼런스들을 확인해보면 QueryDSL에서 더 좋은 사용 경험을 느끼신 것으로 보입니다.
사용 환경 구성
[ 1. dependency, plugin ]
<dependencies>
<!-- Query DSL -->
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<!-- QueryDSL -->
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
<options>
<querydsl.entityAccessors>true</querydsl.entityAccessors>
</options>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
[ 2. QueryDSL 설정 클래스 ]
package com.spring.querydsl.config;
import javax.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Configuration
public class QuerydslConfig {
@Autowired
EntityManager entityManager;
@Bean
public JPAQueryFactory jpaQueryFactory() {
return new JPAQueryFactory(entityManager);
}
}
[ 3. DB 스키마 ]
create table school (
no int auto_increment primary key,
name varchar(50),
address varchar(50)
);
create table student (
no int auto_increment primary key,
name varchar(50),
age int,
school_no int
);
[ 4. Entity 클래스 ]
package com.spring.querydsl.entity;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "school")
public class School {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int no;
private String name;
private String address;
@OneToMany
@JoinColumn(name = "schoolNo", referencedColumnName = "no")
private List<Student> studentList;
}
package com.spring.querydsl.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "student")
public class Student {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int no;
private String name;
private int age;
private int schoolNo;
}
[ 5. Repository 클래스 ]
package com.spring.querydsl.repository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
}
package com.spring.querydsl.repository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Repository
public class StudentQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFacotry;
}
[ 6. Q타입 자동 생성 ]
Repository까지 작성이 모두 완료되었다면 QueryDSL에 사용될 Q타입 파일을 자동 생성해줘야 합니다.
Q타입 파일은 작성된 엔티티 파일을 기준으로 생성이 되기에 저 같은 경우는 2개의 Q타입 파일이 생성될 예정이고 생성하는 방법은 다음과 같이 해주시면 됩니다. (STS 기준입니다.)
- 프로젝트 우 클릭 → Run As → Maven clean
- 프로젝트 우 클릭 → Run As → Maven Install
절차가 완료되면 다음과 같이 target 폴더에 Q타입 파일들이 생성되는 것을 확인할 수 있습니다.
혹시 생성이 되지 않으시는 분들은 다음 절차를 통해 maven 업데이트 진행을 해주시면 됩니다.
- 프로젝트 우 클릭 → Maven → Update Project...
Repository 작성 (1) - 일반 조회
SchoolQueryRepository를 기준으로 Repository 작성하는 여러 방법들에 대해 간단하게 작성해보겠습니다.
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* 일반 조회
*/
@Description("모든 데이터를 조회 (select * from school)")
public List<School> findAll() {
return jpaQueryFactory.selectFrom(QSchool.school)
.fetch();
}
@Description("조회된 데이터 중 첫 번째 데이터만 조회 (select * from school limit 0, 1)")
public School findFirst() {
return jpaQueryFactory.selectFrom(QSchool.school)
.fetchFirst();
}
@Description("모든 컬럼을 조회하지 않고 조회하고 싶은 컬럼만 조회 (select no, name from school)")
public List<School> findNoAndName() {
return jpaQueryFactory.select(Projections.fields(School.class,
QSchool.school.no,
QSchool.school.name)
)
.from(QSchool.school)
.fetch();
}
@Description("distinct를 이용한 조회 (select distinct address from school)")
public List<School> findDistinctAddress() {
return jpaQueryFactory.select(Projections.fields(School.class,
QSchool.school.address)
)
.from(QSchool.school)
.distinct()
.fetch();
}
}
Repository 작성 (2) - where절 (일치)
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* where절 (일치)
*/
@Description("데이터 한 개만 조회, 2개 이상 데이터가 조회될 경우 에러 발생 (select * from school where no = #{no})")
public School findOneByNo(int no) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.eq(no))
.fetchOne();
}
@Description("address값이 일치한 데이터만 조회 (select * from school where address = #{address})")
public List<School> findByAddress(String address) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.address.eq(address))
.fetch();
}
@Description("name, address값이 일치한 데이터만 조회 (select * from school where name = #{name} and address = #{address})")
public List<School> findByNameAndAddress(String name, String address) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.name.eq(name), QSchool.school.address.eq(address))
.fetch();
}
@Description("name이 일치하거나 address값이 일치한 데이터만 조회 (select * from school where name = #{name} or address = #{address})")
public List<School> findByNameOrAddress(String name, String address) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.name.eq(name).or(QSchool.school.address.eq(address)))
.fetch();
}
@Description("address가 일치하지 않은 데이터만 조회 (select * from school where address <> #{address}")
public List<School> findByNotAddress(String address) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.address.ne(address))
.fetch();
}
@Description("address가 null이 아닌 데이터만 조회 (select * from school where address is not null")
public List<School> findByAddressNotNull() {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.address.isNotNull())
.fetch();
}
}
Repository 작성 (3) - where절 (포함)
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* where절 (포함)
*/
@Description("특정 no가 포함된 데이터만 조회 (select * from school where no in (#{no1}, #{no2} ... )")
public List<School> findByInNo(List<Integer> noList) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.in(noList))
.fetch();
}
@Description("특정 no가 포함되지 않은 데이터만 조회 (select * from school where no not in (#{no1}, #{no2} ... )")
public List<School> findByNotInNo(List<Integer> noList) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.notIn(noList))
.fetch();
}
@Description("address에 특정 값이 포함된 데이터만 조회 (select * from school where address like #{address}")
public List<School> findByLikeAddress_1(String address) { // %사용은 parameter값에 추가
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.address.like(address))
.fetch();
}
@Description("address에 특정 값이 포함된 데이터만 조회 (select * from school where address like '%' || #{address} || '%'")
public List<School> findByLikeAddress_2(String address) { // 양방향 % 자동으로 추가
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.address.contains(address))
.fetch();
}
}
Repository 작성 (4) - where절 (부등호)
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* where절 (부등호)
*/
@Description("no값이 특정 기준 이상인 데이터만 조회 (select * from school where no >= #{no})")
public List<School> findByGreaterEqualNo(int no) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.goe(no))
.fetch();
}
@Description("no값이 특정 기준 초과인 데이터만 조회 (select * from school where no > #{no})")
public List<School> findByGreaterNo(int no) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.gt(no))
.fetch();
}
@Description("no값이 특정 기준 이하인 데이터만 조회 (select * from school where no <= #{no})")
public List<School> findByLessEqualNo(int no) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.loe(no))
.fetch();
}
@Description("no값이 특정 기준 미만인 데이터만 조회 (select * from school where no < #{no})")
public List<School> findByLessNo(int no) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.lt(no))
.fetch();
}
@Description("no값이 특정 기준 사이인 데이터만 조회 (select * from school where no between #{fromNo} and #{toNo}")
public List<School> findByBetweenNo(int fromNo, int toNo) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.between(fromNo, toNo))
.fetch();
}
}
Repository 작성 (5) - 정렬
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* 정렬
*/
@Description("조회된 데이터를 no를 기준으로 오름차순 정렬 (select * from school order by no asc)")
public List<School> findAllOrderByNo() {
return jpaQueryFactory.selectFrom(QSchool.school)
.orderBy(QSchool.school.no.asc())
.fetch();
}
@Description("조회된 데이터를 no를 기준으로 내림차순 정렬 (select * from school order by no desc)")
public List<School> findAllOrderByNoDesc() {
return jpaQueryFactory.selectFrom(QSchool.school)
.orderBy(QSchool.school.no.desc())
.fetch();
}
}
Repository 작성 (6) - 세타 조인
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* 세타 조인
*/
@Description("student와 theta join (select school.* from school, student where school.no = student.school_no)")
public List<School> thetaJoin() {
return jpaQueryFactory.select(QSchool.school)
.from(QSchool.school, QStudent.student)
.where(QSchool.school.no.eq(QStudent.student.schoolNo))
.distinct()
.fetch();
}
}
Repository 작성 (7) - 이너 조인
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* 이너 조인
*/
@Description("student와 inner join (select school.*, student.* from school inner join student on school_no = student.school_no)")
public List<School> innerJoin_1() { // 연관관계 매핑되어 있을 경우, 한번에 데이터를 조회함 (n+1문제 해결)
return jpaQueryFactory.selectFrom(QSchool.school)
.join(QSchool.school.studentList, QStudent.student)
.fetchJoin()
.fetch();
}
@Description("student와 inner join (select school.* from school inner join student on school_no = student.school_no)")
public List<School> innerJoin_2() { // 연관관계 매핑되어 있을 경우, school을 조회한 뒤 student를 조회함 (n+1문제 발생)
return jpaQueryFactory.selectFrom(QSchool.school)
.join(QSchool.school.studentList, QStudent.student)
.fetch();
}
@Description("student와 inner join (select school.* from school inner join student on school_no = student.school_no)")
public List<School> innerJoin_3() { // 연관관계 매핑되어 있지 않은 경우
return jpaQueryFactory.selectFrom(QSchool.school)
.join(QStudent.student)
.on(QSchool.school.no.eq(QStudent.student.no))
.fetch();
}
}
Repository 작성 (8) - 아우터 조인
아우터 조인에서는 left outer join만 코드를 작성합니다.
right outer join은 leftJoin대신 rightJoin로 변경해주기만 하면 됩니다.
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* 아우터 조인
*/
@Description("student와 outer left join (select school.*, student.* from school left outer join student on school_no = student.school_no)")
public List<School> leftJoin_1() { // 연관관계 매핑되어 있을 경우, 한번에 데이터를 조회함 (n+1문제 해결)
return jpaQueryFactory.selectFrom(QSchool.school)
.leftJoin(QSchool.school.studentList, QStudent.student)
.fetchJoin()
.fetch();
}
@Description("student와 outer left join (select school.* from school left outer join student on school_no = student.school_no)")
public List<School> leftJoin_2() { // 연관관계 매핑되어 있을 경우, school을 조회한 뒤 student를 조회함 (n+1문제 발생)
return jpaQueryFactory.selectFrom(QSchool.school)
.leftJoin(QSchool.school.studentList, QStudent.student)
.fetch();
}
@Description("student와 outer left join (select school.* from school left outer join student on school_no = student.school_no)")
public List<School> leftJoin_3() { // 연관관계 매핑되어 있지 않은 경우
return jpaQueryFactory.selectFrom(QSchool.school)
.leftJoin(QStudent.student)
.on(QSchool.school.no.eq(QStudent.student.schoolNo))
.fetch();
}
}
Repository 작성 (9) - 서브 쿼리
package com.spring.querydsl.repository;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Description;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spring.querydsl.entity.QSchool;
import com.spring.querydsl.entity.QStudent;
import com.spring.querydsl.entity.School;
@Repository
public class SchoolQueryRepository {
@Autowired
private JPAQueryFactory jpaQueryFactory;
/**
* 번외 (서브쿼리)
*/
@Description("student를 서브쿼리로 사용 (select * from school where no in (select school_no from student where name like '%' || #{studentName} || '%'))")
public List<School> subQuery(String studentName) {
return jpaQueryFactory.selectFrom(QSchool.school)
.where(QSchool.school.no.in(
jpaQueryFactory.selectFrom(QStudent.student)
.where(QStudent.student.name.contains(studentName))
.fetchAll()
.stream()
.flatMap(student -> Stream.of(student.getSchoolNo()))
.collect(Collectors.toList())
))
.fetch();
}
}
이상으로 JPA에서 QueryDSL 사용하는 방법에 대해 간단하게 알아보는 시간이었습니다.
읽어주셔서 감사합니다.
'Spring > SpringBoot' 카테고리의 다른 글
[SpringBoot] QueryDSL Projections로 결과값 핸들링하기 (0) | 2022.07.09 |
---|---|
[SpringBoot] Found shared references to a collection 에러 (0) | 2022.06.13 |
[SpringBoot] AWS S3에 파일 업로드하기 (1) | 2022.04.23 |
[SpringBoot] 환경 변수 파일 사용하기 (0) | 2022.03.28 |
[SpringBoot] GraphQL 설정 (0) | 2021.12.23 |
댓글