buildscript{
ext{
queryDslVersion = "5.0.0"
}
}
plugins{
id 'org.springframework.boot' version '2.6.2'
id 'io.spring.dependency-management' version '1.0.11.RELEASE'
// querydsl 추가
id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
id 'java'
}
group = 'study'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations{
compileOnly{
extendsFrom annotationProcessor
}
}
repositories{
mavenCentral()
}
dependencies{
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
implementation 'org.springframework-boot:spring-boot-starter-web'
// querydsl 추가
implementation "com.querydsl:querydsl-jpa:${queryDslVersion}"
annotationProcessor "com.querydsl:querydsl-apt:${queryDslVersion}"
compileOnly 'org.projectlombok:lombok'
runtimeOnly 'com.h2database:h2'
annotationProcessor 'org.projectlombok:lombok'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
test{
useJUnitPlatform()
}
// Querydsl 설정부, default는 build 디렉토리에 생성되나 IntelliJ IDLE을 사용시 gradle에서 한번 읽고 IntelliJ에서 중복 읽음으로써 버그 발생하는 현상을 없애기 위해
def generated = 'src/main/generated'
// query QClass 파일 생성 위치를 지정
tasks.withType(JavaCompile){
options.getGeneratedSourceOutputDirectory().set(file(generated))
}
// java source set에 querydsl QClass 위치 추가
sourceSets{
main.java.srcDirs += [ generated ]
}
// gradle clean시에 QClass 디렉토리 삭제
clean{
delete file(generated)
}
gralde 설정이 완료되었다면
Gradle - Tasks - build - build 실행하여 Q~ 라고 자동으로 생성된 클래스를 확인할 수 있다.
@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of = {"id", "username", "age"})
public class Member{
@Id @GeneratedValue
private Long id;
@Setter private String useraname;
@Setter private int age;
@Setter
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "team_id")
private Team team;
}
@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of = {"id", "name"})
public class Team {
@Id @GeneratedValue
private Long id;
@Setter private String name;
@Setter
@OneToMany(mappedBy = "team")
private List<Member> members = new ArrayList<>();
public Team(String name){
this.name = name;
}
}
@Test
void startJPQL(){
Member findByJPQL = em.createQuery(
"select m from Member m " +
"where m.username = :username", Member.class)
.setParameter("username", "member1")
.getSingleResult();
assertThat(findByJPQL.getUsername()).isEqualTo("member1");
}
JPQL을 작성하려면 문자열로 작성해야 한다. 문자열은 컴파일 단계에서 오류를 발견할 수 없다. 사소한 띄어쓰기로 예외가 발생할 수 있다. 실제로 코드가 동작하는 순간가지 에러를 발견할 수 없을 것이다.
@Test
void startQuerydsl(){
QMember m = new QMember("m");
Member findMember = jpaQueryFactory
.select(m)
.from(m)
.where(m.username.eq("member1"))
.fetchOne();
assertThat(findMember.getUsername()).isEqualTo("member1");
}
JPQL과 동일한 결과를 가져오는 테스트 코드다. Querydsl은 쿼리에서 사용하는 명령어를 자바코드로 작성할 수 있고 컴파일러가 오류를 검증 할 수 있다.
QMember qMember = new QMember("m"); // 별칭 직접 지정
QMember qMember = QMember.member; // 기본 인스턴스 사용(권장, static import와 함께 사용하는 것을 권장)
application.yml에 spring.jpa.properties.hibernate.use_sql_comments: true
@Test
void search(){
Member findMember = jpaQueryFactory
.selectFrom(member)
.where(member.username.eq("member1")
.and(member.age.eq(10)))
.fetchOne();
assertThat(findMember.getUsername()).isEqualTo("member1");
}
검색조건은 메서드 체인으로 연결할 수 있고 모든 검색 조건을 제공하고 있다.
member.username.eq("member1") | username = 'member1' |
---|---|
member.username.ne("member1") | username != 'member1' |
member.username.eq("member1").not() | username != 'mebber1' |
member.username.isNotNull() | username 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%' 검색 |
member.username.contains("member") | like '%member%' 검색 |
member.username.startsWith("member") | like 'member%' 검색 |
@Test
void searchAndParam(){
Member findMember = jpaQueryFactory
.selectFrom(member)
.where(
member.username.eq("member1"),
member.age.eq(10)
)
.fetchOne();
assertThat(findMember.getUsername()).isEqualTo("member1");
}
where()
에 파라미터로 검색조건을 추가하면 AND
조건이 추가된다.
이 경우 NULL 값은 무시한다.
@Test
void aggregation(){
List<Tuple> result = queryFactory
.select(member.count(),
member.age.sum(),
member.age.avg(),
member.age.max(),
member.age.min())
.from(member)
.fetch();
Tuple tuple = result.get(0);
assertThat(tuple.get(member.age.sum())).isEqualTo(100);
assertThat(tuple.get(member.age.avg())).isEqualTo(25);
assertThat(tuple.get(member.age.max())).isEqualTo(40);
}
조인의 기본 문법은 첫 번째 파라미터에 조인 대상을 지정하고 두 번째 파라미터에 별칭(alias)으로 사용할 Q 타입을 지정하면 된다.
join(조인 대상, 별칭으로 사용할 Q타입)
@Test
void join(){
List<Member> result = queryFactory
.selectFrom(member)
.join(member.team, team)
.where(team.name.eq("teamA"))
.fetch();
assertThat(result)
.extracting("username")
.containsExactly("member1", "member2");
}
@Test
void theta_join(){
em.persist(new Member("teamA"));
em.persist(new Member("teamB"));
List<Member> result = queryFactory
.select(member)
.from(member, team)
.where(member.username.eq(team.name))
.fetch();
assertThat(result)
.extracting("username")
.containsExactly("teamA", "teamB");
}
@Test
void join_on_filtering(){
List<Tuple> result = queryFactory
.select(member, team)
.from(member)
.leftJoin(member.team, team).on(team.name.eq("teamA"))
.fetch();
for(Tuple tuple : result){
System.out.println("tuple = " + tuple);
}
}
@Test
void join_on_no_relation(){
em.persist(new Member("teamA"));
em.persist(new Member("teamB"));
em.persist(new Member("teamC"));
List<Tuple> result = queryFactory
.select(member, team)
.from(member)
.leftJoin(team).on(member.username.eq(team.name))
.fetch();
for(Tuple tuple : result){
System.out.println("tuple = " + tuple);
}
}
@Test
void fetchJoinUse(){
em.flush();
em.clear();
Member findMember = queryFactory
.selectFrom(member)
.join(member.team, team).fetchJoin()
.where(membe.username.eq("member1"))
.fetchOne();
boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam());
assertThat(loaded).as("페치 조인 적용").isTrue();
}
@Test
void subQuery(){
QMember memberSub = new QMember("memberSub");
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.eq(
JPAExpressions
.select(memberSub.age.max())
.from(memberSub)
))
.fetch();
assertThat(result)
.extracting("age")
.containsExactly(40);
}
@Test
void subQueryGoe(){
QMember memberSub = new QMember("memberSub");
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.goe(
JPAExpressions
.select(memberSub.age.avg())
.from(memberSub)
))
.fetch();
assertThat(result)
.extracting("age")
.containsExactly(30, 40);
}
@Test
void subQueryIn(){
QMember memberSub = new QMember("memberSub");
List<Member> result = queryFactory
.selectFrom(member)
.where(member.age.in(
JPAExpressions
.select(memberSub.age)
.from(memberSub)
.where(memberSub.age.gt(10))
))
.fetch();
assertThat(result)
.extracting("age")
.containsExactly(20, 30, 40);
}
@Test
void selectSubQuery(){
QMember memberSub = new QMember("memberSub");
List<Tuple> result = queryFactory
.select(member.username,
JPAExpressions
.select(memberSub.age.avg())
.from(memberSub)
)
.from(member)
.fetch();
for(Tuple tuple : result){
System.out.println("tuple = " + tuple);
}
}
@Test
void basicCase(){
List<String> result = queryFactory
.select(member.age
.when(10).then("10")
.when(20).then("20")
.otherwise("00")
)
.from(member)
.fetch();
}
@Test
void complexCase(){
List<String> result = queryFactory
.select(
new CaseBuilder()
.when(member.age.between(0, 20)).then("0~20")
.when(member.age.between(21, 30)).tehn("20~30")
.otherwise("00")
)
.from(member)
.fetch();
}
@Test
void constant(){
List<Tuple> result = queryFactory
.select(member.username, Expressions.constant("A"))
.from(member)
.fetch();
}
@Test
void concat(){
List<String> result = queryFactory
.select(member.username.concat("_").concat(member.age.stringValue()))
.from(member)
.where(member.username.eq("member1"))
.fetch();
}