SpringBoot with JPA 프로젝트(N:1) 7.검색,JPQL 설정,Tuple,BooleanBuilder

mingki·2022년 2월 23일
JPAJPQLSpringbootmysqln+1tuple
0

SpringBoot & JPA

목록 보기
17/26


📚 공부한 책 : 코드로배우는 스프링 부트 웹프로젝트
❤️ github 주소 : https://github.com/qkralswl689/LearnFromCode/tree/main/board2022

1.JPQL로 검색

FK를 이용해 @ManyToOne과 같은 연관관계를 작성했을 때 가장 어려운것 : 검색에 필요한 JPQL을 구성하는 것

  • 여러 엔티티 타입을 JPQL로 직접 처리하는 경우 Object[] 타입으로 나오기 때문에 작성하는 방법이 보잡하다

1-1.Querydsl설정하기

  • build.gradle 파일 변경
plugins {
	id 'org.springframework.boot' version '2.6.3'
	id 'io.spring.dependency-management' version '1.0.11.RELEASE'
	id 'java'
	id 'com.ewerk.gradle.plugins.querydsl' version '1.0.10' // querydsl 사용을 위해 추가된 항목
}

group = 'com.example'
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-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	compileOnly 'org.projectlombok:lombok'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	runtimeOnly 'mysql:mysql-connector-java'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'

	// querydsl 추가
	implementation 'com.querydsl:querydsl-jpa'

}

tasks.named('test') {
	useJUnitPlatform()
}

// querydsl 추가 시작
def querydslDir = "$buildDir/generated/querydsl"

querydsl {
	jpa = true
	querydslSourcesDir = querydslDir
}

sourceSets {
	main.java.srcDir querydslDir
}

//  (gradle 5.0 이상부터는 옵션을 이렇게 넣어주어야 함)
configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
	querydsl.extendsFrom compileClasspath
}

compileQuerydsl {
	options.annotationProcessorPath = configurations.querydsl
}
// querydsl 추가 끝

2.Repository 확장

JPA의 Repository를 확장하기 위한 단계

  • 쿼리 메서드나 @Query 등으로 처리할 수 없는 기능은 별도의 인터페이스로 설계한다
  • 별도의 인터페이스에 대한 구현 클래스를 작성한다 -> 이때 QuerydslRepositorySupport클래스를 부모 클래스로 사용한다
  • 쿠현 클래스에 인터페이스의 기능을 Q도메인 클래스와 JPQLQuery를 이용해서 구현한다
    ★QuerydslRepositorySupport 클래스 : JPA에 포함된 클래스로 Querydsl 라이브러리를 이용해 직접 무언가를 구현할때 사용한다

2-1.Interface 작성

import com.example.board2022.entity.Board;

public interface SearchBoardRepository {

    Board search1();
}

2-2.Interface 구현 클래스 작성

구현 클래스에서 가장 중요한 것 : QuerydslRepositorySupport 클래스를 상속해야 하는 것
-> QuerydslRepositorySupport는 생성자가 존재하므로 클래스 내에서 super()를 이용해 호출해야 한다


import com.example.board2022.entity.Board;
import lombok.extern.log4j.Log4j2;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

@Log4j2
public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport implements SearchBoardRepository {

    public SearchBoardRepositoryImpl() { // QuerydslRepositorySupport는 생성자가 존재하므로 클래스 내에서 super()를 이용해 호출해야 한다
        super(Board.class);
    }

    @Override
    public Board search1() {

        log.info("search1............");

        return null;
    }
}

2-3.테스트 클래스 작성


import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    //... 생략
    
    @Test
    public void testSearch1() {
        boardRepository.search1();
    }
}
  • 실행확인

    2022-02-19 19:45:48.173 INFO 94679 --- [ main] c.e.b.r.s.SearchBoardRepositoryImpl : search1............
    -> 동작하는것을 확인할 수 있다

2022-02-19 19:45:46.027  WARN 94679 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2022-02-19 19:45:47.115  INFO 94679 --- [           main] o.s.b.a.w.s.WelcomePageHandlerMapping    : Adding welcome page: class path resource [static/index.html]
2022-02-19 19:45:47.848  INFO 94679 --- [           main] c.e.b.repository.BoardRepositoryTests    : Started BoardRepositoryTests in 13.746 seconds (JVM running for 19.925)
2022-02-19 19:45:48.173  INFO 94679 --- [           main] c.e.b.r.s.SearchBoardRepositoryImpl      : search1............
2022-02-19 19:45:48.213  INFO 94679 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

3.JPQLQuery 객체

실제 JPQL을 작성해 실행해 본다 -> Querydsl 라이브러리 내에는 JPQLQuery라는 인터페이스를 활용한다

3-1.RepositoryImpl 작성

import com.example.board2022.entity.Board;
import com.example.board2022.entity.QBoard;
import com.querydsl.jpa.JPQLQuery;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

import java.util.List;


public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport implements SearchBoardRepository {

    public SearchBoardRepositoryImpl() { // QuerydslRepositorySupport는 생성자가 존재하므로 클래스 내에서 super()를 이용해 호출해야 한다
        super(Board.class);
    }

    @Override
    public Board search1() {

        QBoard board = QBoard.board;

        JPQLQuery<Board> jpqlQuery = from(board);

        jpqlQuery.select(board).where(board.bno.eq(1L));

        List<Board> result = jpqlQuery.fetch();

        return null;
    }
}

★ QBoard 엔티티 클래스가 생성되지 않는 오류가 생겼을 경우 해결방법

build.gradle에 buildscript 추가 및 dependencies 를 수정해준다

//querydsl 추가 - 수정한부분
buildscript {
	ext {
		queryDslVersion = "5.0.0"
	}
}

plugins {
	id 'org.springframework.boot' version '2.6.3'
	id 'io.spring.dependency-management' version '1.0.11.RELEASE'
	id 'java'
	id "com.ewerk.gradle.plugins.querydsl" version "1.0.10" // querydsl 사용을 위해 추가된 항목
}

group = 'com.example'
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-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	compileOnly 'org.projectlombok:lombok'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	runtimeOnly 'mysql:mysql-connector-java'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'

	// querydsl 추가 - 수정한부분
	implementation "com.querydsl:querydsl-jpa:${queryDslVersion}"
	implementation "com.querydsl:querydsl-apt:${queryDslVersion}"

}

//.......생략

3-2.테스트 실행

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    //... 생략
    
    @Test
    public void testSearch1() {
        boardRepository.search1();
    }
}
  • 실행결과

    위 테스트 코드를 다시 실행하면 아래와 같이 실제 SQL이 실행되는 것을 확인할 수 있다

Hibernate: 
    select
        board0_.bno as bno1_0_,
        board0_.moddate as moddate2_0_,
        board0_.regdate as regdate3_0_,
        board0_.content as content4_0_,
        board0_.title as title5_0_,
        board0_.writer_email as writer_e6_0_ 
    from
        board board0_ 
    where
        board0_.bno=?

4.JQPLQuery의 leftJoin()/on()

JPQLQuery로 다른 엔티티와 조인을 처리하기 위해서는 join() OR leftJoin(),rightJoin()등을 이용하고 필요한 경우 on()을 이용해 조인에 필요한 부분을 완성할 수 있다

4-1.RepositoryImpl 수정

Board는 Reply와 left(outer)join을 이용

import com.example.board2022.entity.Board;
import com.example.board2022.entity.QBoard;
import com.example.board2022.entity.QReply;
import com.querydsl.jpa.JPQLQuery;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

import java.util.List;


public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport implements SearchBoardRepository {


    @Override
    public Board search1() {

        // log.info("search1............");

        QBoard board = QBoard.board;
        QReply reply = QReply.reply;

        JPQLQuery<Board> jpqlQuery = from(board);
        jpqlQuery.leftJoin(reply).on(reply.board.eq(board));

        jpqlQuery.select(board).where(board.bno.eq(1L));

        List<Board> result = jpqlQuery.fetch();

        return null;
    }
}

4-2.테스트 실행

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    //... 생략
    
    @Test
    public void testSearch1() {
        boardRepository.search1();
    }
}
  • 실행결과

    3-2의 결과와 다르게 left out join 구문이 추가 된다

Hibernate: 
    select
        board0_.bno as bno1_0_,
        board0_.moddate as moddate2_0_,
        board0_.regdate as regdate3_0_,
        board0_.content as content4_0_,
        board0_.title as title5_0_,
        board0_.writer_email as writer_e6_0_ 
    from
        board board0_ 
    left outer join
        reply reply1_ 
            on (
                reply1_.board_bno=board0_.bno
            ) 
    where
        board0_.bno=?

5.Tuple 객체

정해진 엔티티 객체 단위가 아니라 각가의 데이터를 추출하는 경우 Tuple 객체를 이용한다

5-1.RepositoryImpl 수정

select()의 결과를 JPQLQuery을 이용해 처리하도록 변경하고 result 변수의 타입도 List 타입으로 변경했다

import com.example.board2022.entity.Board;
import com.example.board2022.entity.QBoard;
import com.example.board2022.entity.QMember;
import com.example.board2022.entity.QReply;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.JPQLQuery;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

import java.util.List;

public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport implements SearchBoardRepository {


    @Override
    public Board search1() {

        // log.info("search1............");

        QBoard board = QBoard.board;
        QReply reply = QReply.reply;
        QMember member = QMember.member;

        JPQLQuery<Board> jpqlQuery = from(board);
        jpqlQuery.leftJoin(member).on(board.writer.eq(member));
        jpqlQuery.leftJoin(reply).on(reply.board.eq(board));

        JPQLQuery<Tuple> tuple = jpqlQuery.select(board,member.email,reply.count());
        tuple.groupBy(board);

        List<Tuple> result = tuple.fetch();

        return null;
    }
}

5-2.테스트 실행

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    //... 생략
    
    @Test
    public void testSearch1() {
        boardRepository.search1();
    }
}
  • 실행결과
Hibernate: 
    select
        board0_.bno as col_0_0_,
        member1_.email as col_1_0_,
        count(reply2_.rno) as col_2_0_,
        board0_.bno as bno1_0_,
        board0_.moddate as moddate2_0_,
        board0_.regdate as regdate3_0_,
        board0_.content as content4_0_,
        board0_.title as title5_0_,
        board0_.writer_email as writer_e6_0_ 
    from
        board board0_ 
    left outer join
        member member1_ 
            on (
                board0_.writer_email=member1_.email
            ) 
    left outer join
        reply reply2_ 
            on (
                reply2_.board_bno=board0_.bno
            ) 
    group by
        board0_.bno

6.JPQLQuery로 Page<Object[]> 처리

원하는 파라미터(Pageable)를 전송하고 Page<Object[]>를 만들어 반환한다.

6-1.Repository interface 작성

searchPage()는 검색 타입, 키워드,페이지 정보를 파라미터로 추가한다

  • PageRequestDTO 자체를 파라미터로 처리하지 않는 이유 : DTO를 가능하면 Repository 영역에서 다루지 않기 위해
import com.example.board2022.entity.Board;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

public interface SearchBoardRepository {

    Board search1();
    
    Page<Object[]> searchPage(String type, String keyword, Pageable pageable);
}

6-2.RepositoryImpl 작성

파라미터에 따라 검색조건을 추가할 수 있도록 BooleanBuilder 와 BooleanEspression들이 추가됐다

import com.example.board2022.entity.Board;
import com.example.board2022.entity.QBoard;
import com.example.board2022.entity.QMember;
import com.example.board2022.entity.QReply;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.JPQLQuery;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;

import java.util.List;


public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport implements SearchBoardRepository {
	
  	// ....생략
  
    @Override
    public Page<Object[]> searchPage(String type, String keyword, Pageable pageable) {

        QBoard board = QBoard.board;
        QReply reply = QReply.reply;
        QMember member = QMember.member;

        JPQLQuery<Board> jpqlQuery = from(board);
        jpqlQuery.leftJoin(member).on(board.writer.eq(member));
        jpqlQuery.leftJoin(reply).on(reply.board.eq(board));

        JPQLQuery<Tuple> tuple = jpqlQuery.select(board,member,reply.count());

        BooleanBuilder booleanBuilder = new BooleanBuilder();
        BooleanExpression expression = board.bno.gt(0L);

        booleanBuilder.and(expression);

        if(type != null) {
            String[] typeArr = type.split("");

            // 검색 조건 작성
            BooleanBuilder conditionBuilder = new BooleanBuilder();

            for(String t : typeArr) {
                switch (t){
                    case "t" :
                        conditionBuilder.or(board.title.contains(keyword));
                        break;
                    case "w" :
                        conditionBuilder.or(member.email.contains(keyword));
                        break;
                    case "c" :
                        conditionBuilder.or(board.content.contains(keyword));
                        break;
                }
            }
            booleanBuilder.and(conditionBuilder);
        }

        tuple.where(booleanBuilder);

        tuple.groupBy(board);

        List<Tuple> result = tuple.fetch();


        return null;
    }
}

6-3.테스트 코드 작성

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    //...	생략
   
    @Test
    public void testSearchPage(){

        Pageable pageable = PageRequest.of(0,10,Sort.by("bno").descending());

        Page<Object[]> result = boardRepository.searchPage("t","1",pageable);
    }
}
  • 실행 결과

    검색조건이 변경되면 where 조건절도 같이 변경 된다

Hibernate: 
    select
        board0_.bno as col_0_0_,
        member1_.email as col_1_0_,
        count(reply2_.rno) as col_2_0_,
        board0_.bno as bno1_0_0_,
        member1_.email as email1_1_1_,
        board0_.moddate as moddate2_0_0_,
        board0_.regdate as regdate3_0_0_,
        board0_.content as content4_0_0_,
        board0_.title as title5_0_0_,
        board0_.writer_email as writer_e6_0_0_,
        member1_.moddate as moddate2_1_1_,
        member1_.regdate as regdate3_1_1_,
        member1_.name as name4_1_1_,
        member1_.password as password5_1_1_ 
    from
        board board0_ 
    left outer join
        member member1_ 
            on (
                board0_.writer_email=member1_.email
            ) 
    left outer join
        reply reply2_ 
            on (
                reply2_.board_bno=board0_.bno
            ) 
    where
        board0_.bno>? 
        and (
            board0_.title like ? escape '!' // 제목으로 검색되는 조건이 추가됨
        ) 
    group by
        board0_.bno
profile
mingki
비전공초보개발자
이전 포스트

SpringBoot with JPA 프로젝트(N:1) 6.게시물리스트,게시물등록,게시물조회,게시물수정/삭제

다음 포스트

SpringBoot with JPA 프로젝트(N:1) 8.sort/count 처리,검색처리

0개의 댓글