2022/03/06 JPA 기술들

김석진·2022년 3월 6일

BookManager

목록 보기

2/3

QueryMethod 활용

public interface UserRepository extends JpaRepository<User, Long> {
    // return type User
    User findByName(String name);
}

이렇게 이름으로 객체를 반환하는 메소드를 추가하고 테스트코드를 작성해봤다

   @Test
    void select() {
        System.out.println(userRepository.findByName("park"));
    }

이렇게 실행을 했을때

이런 오류가 발생했다. 이유는 위 예제 .sql 코드에서 name이 park인 예제코드를 2개 작성했기때문이다

public interface UserRepository extends JpaRepository<User, Long> {
    // return type User
    List<User> findByName(String name);
}

리턴 타입을 List로 변경했을때

결과값
[User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T18:41:19.532788, updatedAt=2022-03-06T18:41:19.532788), User(id=5, name=park, email=park@gmail.com, createdAt=2022-03-06T18:41:19.533789, updatedAt=2022-03-06T18:41:19.533789)]
이렇게 리스트로 나옴을 확인할 수 있다.
한개의 값만 조회를 했을때 결과값도 알아보자
[User(id=1, name=kim, email=kimseokjin0324@gmail.com, createdAt=2022-03-06T18:42:37.203586, updatedAt=2022-03-06T18:42:37.203586)]
이렇게 하나도 리스트로 제대로 나옴을 알 수 있다.

예제

    List<User> findByEmailAndName(String email, String name);
    email과 name을 이용해서 select 쿼리 작성해보기
System.out.println("findByEmailAndName : " + userRepository.findByEmailAndName("park@gmail.com", "park"));

결과
 select
        user0_.id as id1_0_,
        user0_.created_at as created_2_0_,
        user0_.email as email3_0_,
        user0_.name as name4_0_,
        user0_.updated_at as updated_5_0_ 
    from
        user user0_ 
    where
        user0_.email=? 
        and user0_.name=?
findByEmailAndName : [User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T19:54:26.525810, updatedAt=2022-03-06T19:54:26.525810)]

findBy~Or~

List<User> findByEmailOrName(String email, String name);

 select
        user0_.id as id1_0_,
        user0_.created_at as created_2_0_,
        user0_.email as email3_0_,
        user0_.name as name4_0_,
        user0_.updated_at as updated_5_0_ 
    from
        user user0_ 
    where
        user0_.email=? 
        or user0_.name=?
findByEmailOrName : [User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T20:00:47.639510, updatedAt=2022-03-06T20:00:47.639510), User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T20:00:47.640511, updatedAt=2022-03-06T20:00:47.640511)]

After & Before

시간에 대한 조건이다.
우리의 예는 createdAt, updatedAt이 시간에 대한 필드이다

    List<User> findByCreatedAtAfter(LocalDateTime yesterday);

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.created_at as created_2_0_,
        user0_.email as email3_0_,
        user0_.name as name4_0_,
        user0_.updated_at as updated_5_0_ 
    from
        user user0_ 
    where
        user0_.created_at>?
findByCreatedAtAfter : [User(id=1, name=kim, email=kimseokjin0324@gmail.com, createdAt=2022-03-06T20:04:29.737579, updatedAt=2022-03-06T20:04:29.737579), User(id=2, name=lee, email=lee@gmail.com, createdAt=2022-03-06T20:04:29.754583, updatedAt=2022-03-06T20:04:29.754583), User(id=3, name=hong, email=hong@gmail.com, createdAt=2022-03-06T20:04:29.755583, updatedAt=2022-03-06T20:04:29.755583), User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T20:04:29.755583, updatedAt=2022-03-06T20:04:29.755583), User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T20:04:29.756582, updatedAt=2022-03-06T20:04:29.756582)]

Before는 반대이다

 System.out.println("findByIdAfter : " + userRepository.findByIdAfter(3L));
 이렇게 특정 값이후의 값들도 찾을 수 있다

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.created_at as created_2_0_,
        user0_.email as email3_0_,
        user0_.name as name4_0_,
        user0_.updated_at as updated_5_0_ 
    from
        user user0_ 
    where
        user0_.id>?
findByIdAfter : [User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T20:09:58.571206, updatedAt=2022-03-06T20:09:58.571206), User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T20:09:58.571206, updatedAt=2022-03-06T20:09:58.571206)]

findByCreatedAtGreaterThan& findByCreatedAtGreaterThanEqual


       System.out.println("findByCreatedAtGreaterThan : " + userRepository.findByCreatedAtGreaterThan(LocalDateTime.now().minusDays(1L)));
        System.out.println("findByCreatedAtGreaterThanEqual : " + userRepository.findByCreatedAtGreaterThanEqual(LocalDateTime.now().minusDays(1L)));

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.created_at as created_2_0_,
        user0_.email as email3_0_,
        user0_.name as name4_0_,
        user0_.updated_at as updated_5_0_ 
    from
        user user0_ 
    where
        user0_.created_at>?
findByCreatedAtGreaterThan : [User(id=1, name=kim, email=kimseokjin0324@gmail.com, createdAt=2022-03-06T20:16:32.861570, updatedAt=2022-03-06T20:16:32.861570), User(id=2, name=lee, email=lee@gmail.com, createdAt=2022-03-06T20:16:32.879575, updatedAt=2022-03-06T20:16:32.879575), User(id=3, name=hong, email=hong@gmail.com, createdAt=2022-03-06T20:16:32.880578, updatedAt=2022-03-06T20:16:32.880578), User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T20:16:32.880578, updatedAt=2022-03-06T20:16:32.880578), User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T20:16:32.880578, updatedAt=2022-03-06T20:16:32.880578)]
Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.created_at as created_2_0_,
        user0_.email as email3_0_,
        user0_.name as name4_0_,
        user0_.updated_at as updated_5_0_ 
    from
        user user0_ 
    where
        user0_.created_at>=?
findByCreatedAtGreaterThanEqual : [User(id=1, name=kim, email=kimseokjin0324@gmail.com, createdAt=2022-03-06T20:16:32.861570, updatedAt=2022-03-06T20:16:32.861570), User(id=2, name=lee, email=lee@gmail.com, createdAt=2022-03-06T20:16:32.879575, updatedAt=2022-03-06T20:16:32.879575), User(id=3, name=hong, email=hong@gmail.com, createdAt=2022-03-06T20:16:32.880578, updatedAt=2022-03-06T20:16:32.880578), User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T20:16:32.880578, updatedAt=2022-03-06T20:16:32.880578), User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T20:16:32.880578, updatedAt=2022-03-06T20:16:32.880578)]

빈값 체크

  System.out.println("findByIdIsNotNull : "+userRepository.findByIdIsNotNull());

Hibernate: 
    select
        user0_.id as id1_0_,
        user0_.created_at as created_2_0_,
        user0_.email as email3_0_,
        user0_.name as name4_0_,
        user0_.updated_at as updated_5_0_ 
    from
        user user0_ 
    where
        user0_.id is not null
findByIdIsNotNull : [User(id=1, name=kim, email=kimseokjin0324@gmail.com, createdAt=2022-03-06T20:21:57.323739, updatedAt=2022-03-06T20:21:57.323739), User(id=2, name=lee, email=lee@gmail.com, createdAt=2022-03-06T20:21:57.331741, updatedAt=2022-03-06T20:21:57.331741), User(id=3, name=hong, email=hong@gmail.com, createdAt=2022-03-06T20:21:57.331741, updatedAt=2022-03-06T20:21:57.331741), User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T20:21:57.331741, updatedAt=2022-03-06T20:21:57.331741), User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T20:21:57.331741, updatedAt=2022-03-06T20:21:57.331741)]

쿼리문을 보면 where절에 is not null이 있음을 볼 수 있다.

findByIdIsNotEmpty

System.out.println("findByIdIsNotEmpty : "+userRepository.findByIdIsNotEmpty());
        이 코드는 오류가 발생함
Caused by: java.lang.IllegalArgumentException: IsEmpty / IsNotEmpty can only be used on collection properties!
이렇게 보면 IsEmpty / IsNotEmpty는 컬렉션 프로퍼티에서 사용할 수 있음을 알 수 있다

IN/NOT IN

생각 보다 많이 사용됨

        System.out.println("findByNameIn : "+userRepository.findByNameIn(Lists.newArrayList("kim","lee")));

 select
        address0_.user_id as user_id1_2_0_,
        address0_.address_id as address_2_2_0_,
        address1_.id as id1_0_1_ 
    from
        user_address address0_ 
    inner join
        address address1_ 
            on address0_.address_id=address1_.id 
    where
        address0_.user_id=?
findByNameIn : [User(id=1, name=kim, email=kimseokjin0324@gmail.com, createdAt=2022-03-06T21:03:19.959728, updatedAt=2022-03-06T21:03:19.959728, address=[]), User(id=2, name=lee, email=lee@gmail.com, createdAt=2022-03-06T21:03:19.969730, updatedAt=2022-03-06T21:03:19.969730, address=[])]

일반적으로는 다른 쿼리 결과값을 다시 쿼리에 넣기 위해서 많이 사용함
IN절안의 List의 길이가 길어지면 성능적인 저하가 발생하기 때문에 List의 길이를 알때 사용한다

쿼리메소드로 정렬시켜 보기

List<User> findTop1ByName(String name);
List<User> findLast1ByName(String name);

    @Test
    void pagingAndSortingTest() {
        System.out.println("findTop1ByName : " + userRepository.findTop1ByName("park"));
        System.out.println("findLast1ByName : " + userRepository.findLast1ByName("park"));  //- 이키워드는 무시되어서 findByName 쿼리가 실행이 된다

    }
    
    
    Hibernate: 
    select
        user0_.id as id1_1_,
        user0_.created_at as created_2_1_,
        user0_.email as email3_1_,
        user0_.name as name4_1_,
        user0_.updated_at as updated_5_1_ 
    from
        user user0_ 
    where
        user0_.name=? limit ?
findTop1ByName : [User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T21:27:39.691640, updatedAt=2022-03-06T21:27:39.691640)]
Hibernate: 
    select
        user0_.id as id1_1_,
        user0_.created_at as created_2_1_,
        user0_.email as email3_1_,
        user0_.name as name4_1_,
        user0_.updated_at as updated_5_1_ 
    from
        user user0_ 
    where
        user0_.name=?
findLast1ByName : [User(id=4, name=park, email=park@gmail.com, createdAt=2022-03-06T21:27:39.691640, updatedAt=2022-03-06T21:27:39.691640), User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T21:27:39.692638, updatedAt=2022-03-06T21:27:39.692638)]

findLast1ByName은 무시가되어 findByName메소드와 같은 쿼리를 가지게 된다
이를 수정하여 원래대로 만들어 보자

위 코드를 정상적인 기능을 하는 코드를 작성해보자
List<User> findTop1ByNameOrderByIdDesc(String name);
즉 Name으로 내림차순으로 정렬을 한것중 이름으로 검색한 처음을 가져온다라는 메소드 뜻이다. 

결과
Hibernate: 
    select
        user0_.id as id1_1_,
        user0_.created_at as created_2_1_,
        user0_.email as email3_1_,
        user0_.name as name4_1_,
        user0_.updated_at as updated_5_1_ 
    from
        user user0_ 
    where
        user0_.name=? 
    order by
        user0_.id desc limit ?
findTop1ByNameOrderByIdDesc : [User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T21:32:05.762727, updatedAt=2022-03-06T21:32:05.762727)]

조건을 두개를 사용해보자

List<User> findFirstByNameOrderByIdDescEmailAsc(String name);       //-아이디는 내림차순, email은 오름차순

Hibernate: 
    select
        user0_.id as id1_1_,
        user0_.created_at as created_2_1_,
        user0_.email as email3_1_,
        user0_.name as name4_1_,
        user0_.updated_at as updated_5_1_ 
    from
        user user0_ 
    where
        user0_.name=? 
    order by
        user0_.id desc,
        user0_.email asc limit ?
findFirstByNameOrderByIdDescEmailAsc :  [User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T21:38:47.354383, updatedAt=2022-03-06T21:38:47.354383)]

이렇게 사용하면 id는 내림차순 email은 오름차순인 값을 정렬한다.

Sort클래스를 활용해서 Sort파라미터를 제공하여 정렬

    List<User> findFirstByName(String name, Sort sort);
            System.out.println("findFirstByNameWithSortParams : "+userRepository.findFirstByName("park",Sort.by(Sort.Order.desc("id"))));

Hibernate: 
    select
        user0_.id as id1_1_,
        user0_.created_at as created_2_1_,
        user0_.email as email3_1_,
        user0_.name as name4_1_,
        user0_.updated_at as updated_5_1_ 
    from
        user user0_ 
    where
        user0_.name=? 
    order by
        user0_.id desc limit ?
findFirstByNameWithSortParams : [User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-06T22:07:02.660971, updatedAt=2022-03-06T22:07:02.660971)]

이렇게 Sort의 종류를 파라미터로 준다면 메소드 명이 작아질수 있는 장점이 있고 Sort의 종류를 사용하고 싶은 종류를 선택할 수 있는 자유성을 보장한다.

쿼리메소드로 페이징 처리하기

우리가 사용하는 데이터는 ID값을 사용하거나 유니크 조건을 이용해서 하나를 식별해서 가져오기도 하지만 굉장히 많은경우 리스트 형태로 조회한다 ex) 블로그, 게시판, 댓글 조회시

  Page<User> findByName(String name, Pageable pageable);

Slice 인터페이스 는 데이터 묶음의 일부 덩어리이다.
Slice가 제공하는 정보는 현재 슬라이스에 대한 값이다.

        System.out.println("findByNameWithPaging : "+userRepository.findByName("park", PageRequest.of(0,1,Sort.by(Sort.Order.desc("id"))))); //-page값은 0인덱스 값임을 알아 놔야함

Hibernate: 
    select
        user0_.id as id1_1_,
        user0_.created_at as created_2_1_,
        user0_.email as email3_1_,
        user0_.name as name4_1_,
        user0_.updated_at as updated_5_1_ 
    from
        user user0_ 
    where
        user0_.name=? 
    order by
        user0_.id desc limit ?
Hibernate: 
    select
        count(user0_.id) as col_0_0_ 
    from
        user user0_ 
    where
        user0_.name=?
findByNameWithPaging : Page 1 of 2 containing com.example.jpa.bookmanager.domain.User instances
이렇게 하면 우리가 보고싶은 값이 나오지 않는다 그래서 뒤에 코드를 추가한다

System.out.println("findByNameWithPaging : "+userRepository.findByName("park", PageRequest.of(0,1,Sort.by(Sort.Order.desc("id")))).getContent()); //-page값은 0인덱스 값임을 알아 놔야함

Hibernate: 
    select
        user0_.id as id1_1_,
        user0_.created_at as created_2_1_,
        user0_.email as email3_1_,
        user0_.name as name4_1_,
        user0_.updated_at as updated_5_1_ 
    from
        user user0_ 
    where
        user0_.name=? 
    order by
        user0_.id desc limit ?
Hibernate: 
    select
        count(user0_.id) as col_0_0_ 
    from
        user user0_ 
    where
        user0_.name=?
findByNameWithPaging : [User(id=5, name=park, email=park2@gmail.com, createdAt=2022-03-07T00:17:06.706326, updatedAt=2022-03-07T00:17:06.706326)]
쿼리가 두개 실행되고 우리가 원하는 값이 나옴을 알 수 있다