본문 바로가기
Spring/SpringBoot

[SpringBoot] JPA에서 QueryDSL 사용하기

by J4J 2022. 4. 24.
300x250
반응형

안녕하세요. 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타입 파일들이 생성되는 것을 확인할 수 있습니다.

 

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

 

 

728x90

 

 

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 사용하는 방법에 대해 간단하게 알아보는 시간이었습니다.

 

읽어주셔서 감사합니다.

 

 

 

728x90
반응형

댓글