[JPA] QueryMethod 활용

JpaRepository 에서 기본적으로 제공해주는 메소드 이외에 우리가 직접 만들어서 사용 할 수 있는 QueryMethod의 종류와 방법을 알아 보려 한다

 

기본적인 테이블 생성과정은 생략 하며 repository 에서의 정의와 실제 어떻게 동작하는지 쿼리를 확인해 보기 위해

Test를 진행해 볼 것이다

 

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#appendix.query.method.subject

Spring Data JPA - Reference Documentation

자세한 사항은 위에 레퍼런스를 보면 좋을 것 같다

레퍼런스를 본 뒤에 아래 메소드 정의들을 보면 더 이해가 쉽게 될 것이다

 

여기서는 기본적으로 가장 많이 쓰이는 것들만 알아 보려 한다

import com.example.jpaexample.domain.Board;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;

import java.time.LocalDateTime;
import java.util.List;

public interface BoardRepository extends JpaRepository<Board, Integer> {

    List<Board> findByContent(String content);

    Board findByTitle(String title);

    Board getByTitle(String title);

    Board readByTitle(String title);

    Board queryByTitle(String title);

    Board searchByTitle(String title);

    Board streamByTitle(String title);

    Board findBoardByTitle(String title);

    List<Board> findFirst3ByContent(String content);

    List<Board> findTop3ByContent(String content);

    List<Board> findByNoticeAndSecret(String notice, String secret);

    List<Board> findByNoticeOrSecret(String notice, String secret);

    List<Board> findByCreatedAtAfter(LocalDateTime createdAt);

    List<Board> findByIdxAfter(int idx);

    List<Board> findByCreatedAtGreaterThan(LocalDateTime createdAt);

    List<Board> findByCreatedAtGreaterThanEqual(LocalDateTime createdAt);

    List<Board> findByCreatedAtBetween(LocalDateTime beforeDate, LocalDateTime afterDate);

    List<Board> findByIdxBetween(int beforeIdx, int afterIdx);

    List<Board> findByIdxGreaterThanEqualAndIdxLessThanEqual(int beforeIdx, int afterIdx);

    List<Board> findByIdxIsNotNull();

    List<Board> findByIdxIsNull();

    List<Board> findByTitleIn(List<String> titles);

    List<Board> findByTitleNotIn(List<String> titles);

    List<Board> findByWriterStartingWith(String writer);

    List<Board> findByWriterEndingWith(String writer);

    List<Board> findByWriterContains(String writer);

    Board findTop1ByContentOrderByIdxDesc(String content);

    List<Board> findFirstByContentOrderByIdxDescTitleAsc(String content);

    List<Board> findFirstByWriterStartingWith(String writer, Sort sort);

    Page<Board> findByContent(String Content, Pageable pageable);

}

종류가 상당히 많은데 사실 메소드명만 봐도 어떤 동작을 하는 쿼리인지 대충 짐작이 될 것이다

 

그럼 실제로 어떤 쿼리가 동작하는지 알아보기 위해 Test를 돌려보았다

import org.assertj.core.util.Lists;
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.PageRequest;
import org.springframework.data.domain.Sort;

import java.time.LocalDateTime;

@SpringBootTest
public class BoardRepositoryTest {

    @Autowired
    private BoardRepository boardRepository;

    @Test
    void select() {
        // select * from board where title = 'test6'
        System.out.println("findByTitle : " + boardRepository.findByTitle("test6"));
        System.out.println("getByTitle : " + boardRepository.getByTitle("test6"));
        System.out.println("readByTitle : " + boardRepository.readByTitle("test6"));
        System.out.println("queryByTitle : " + boardRepository.queryByTitle("test6"));
        System.out.println("searchByTitle : " + boardRepository.searchByTitle("test6"));
        System.out.println("streamByTitle : " + boardRepository.streamByTitle("test6"));
        System.out.println("findBoardByTitle : " + boardRepository.findBoardByTitle("test6"));

        // select * from board where content = 'test-content' limit 3
        System.out.println("findFirst3ByContent : " + boardRepository.findFirst3ByContent("test-content"));
        System.out.println("findTop3ByContent : " + boardRepository.findTop3ByContent("test-content"));

        // select * from board where notice = 'Y' and secret = 'N'
        System.out.println("findByNoticeAndSecret : " + boardRepository.findByNoticeAndSecret("Y", "N"));

        // select * from board where notice = 'Y' or secret = 'N'
        System.out.println("findByNoticeOrSecret : " + boardRepository.findByNoticeOrSecret("Y", "N"));

        // select * from board where idx > 4
        System.out.println("findByIdxAfter : " + boardRepository.findByIdxAfter(4));

        // select * from board where created_at > 어제
        System.out.println("findByCreatedAtAfter : " + boardRepository.findByCreatedAtAfter(LocalDateTime.now().minusDays(1L)));
        System.out.println("findByCreatedAtGreaterThan : " + boardRepository.findByCreatedAtGreaterThan(LocalDateTime.now().minusDays(1L)));

        // select * from board where created_at >= 어제
        System.out.println("findByCreatedAtGreaterThanEqual : " + boardRepository.findByCreatedAtGreaterThanEqual(LocalDateTime.now().minusDays(1L)));

        // select * from board where created_at between 어제 and 내일
        System.out.println("findByCreatedAtBetween : " + boardRepository.findByCreatedAtBetween(LocalDateTime.now().minusDays(1L), LocalDateTime.now().plusDays(1l)));

        // select * from board where idx between 2 and 4
        System.out.println("findByIdxBetween : " + boardRepository.findByIdxBetween(2, 4));

        // select * from board where idx >= 2 and idx <= 4
        System.out.println("findByIdxGreaterThanEqualAndIdxLessThanEqual : " + boardRepository.findByIdxGreaterThanEqualAndIdxLessThanEqual(2, 4));

        // select * from board where idx is not null
        System.out.println("findByIdxIsNotNull : " + boardRepository.findByIdxIsNotNull());

        // select * from board where idx is null
        System.out.println("findByIdxIsNull : " + boardRepository.findByIdxIsNull());

        // select * from board where title in ('test1', 'test2', 'test3')
        System.out.println("findByTitleIn : " + boardRepository.findByTitleIn(Lists.newArrayList("test1", "test2", "test3")));

        // select * from board where title not in ('test1' , 'test2', 'test3')
        System.out.println("findByTitleNotIn : " + boardRepository.findByTitleNotIn(Lists.newArrayList("test1", "test2", "test3")));

        // select * from board where writer like 'te%'
        System.out.println("findByWriterStartingWith : " + boardRepository.findByWriterStartingWith("te"));

        // select * from board where writer like '%r4'
        System.out.println("findByWriterEndingWith : " + boardRepository.findByWriterEndingWith("r4"));

        // select * from board where writer like '%tU%'
        System.out.println("findByWriterContains : " + boardRepository.findByWriterContains("tU"));

        // select * from board where content = 'test-content' order by idx desc limit 1
        System.out.println("findTop1ByContentOrderByIdxDesc : " + boardRepository.findTop1ByContentOrderByIdxDesc("test-content"));

        // select * from board where content = 'test-content' order by idx desc, title asc limit 1
        System.out.println("findFirstByContentOrderByIdxDescTitleAsc : " + boardRepository.findFirstByContentOrderByIdxDescTitleAsc("test-content"));

        // select * from board where writer like 'test%' order by idx desc, title asc limit 1
        System.out.println("findFirstByWriterStartingWith : " + boardRepository.findFirstByWriterStartingWith("test", Sort.by(Sort.Order.desc("idx"), Sort.Order.asc("title"))));

        // select * from board where content = 'test-content' order by idx desc limit 0, 2
        // select count(*) from board where content = 'test-content'
        System.out.println("findByContentPaging : " + boardRepository.findByContent("test-content", PageRequest.of(0, 2, Sort.by(Sort.Order.desc("idx")))));

    }
}

왠만한 쿼리는 QueryMethod를 통해서 구현이 가능하다는 걸 확인 할 수 있었다

 

우선 이정도만 파악하더라도 JPA를 더욱 쉽고 친근하게 사용 할 수 있을 것 같은 기분이다