Spring Data JPA : 쿼리 메서드

Spring Data JPA 에서 사용되는 키워드

• And, Or
• Is, Equals
• LessThan, LessThanEqual, GreaterThan, GreaterThanEqual
• After, Before
• IsNull, IsNotNull, NotNull
• Like, NotLike
• StartingWith, EndingWith, Containing
• OrderBy
• Not, In, NotIn
• True, False
• IgnoreCase

---

public interface PostRepository extends JpaRepository<Post,Long> {

    List<Post> findByTitleStartsWith(String title);
}

@Test
    public void findByTitleStartsWith(){
        Post post = new Post();
        post.setTitle("Spring data JPA");

        Post savedPost = postRepository.save(post);

        List<Post> byTitleStartsWith = postRepository.findByTitleStartsWith("Spring");

        assertThat(byTitleStartsWith.size()).isEqualTo(1);
        assertThat(byTitleStartsWith.get(0).getTitle()).isEqualTo("Spring data JPA");
    }

Hibernate: 
    select
        post0_.id as id1_0_,
        post0_.created as created2_0_,
        post0_.title as title3_0_ 
    from
        post post0_ 
    where
        post0_.title like ? escape ?
2020-11-18 18:50:39.180 TRACE 12492 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Spring%]

---

@NamedQuery, @NamedNativeQuery

• Entity 클래스에 미리 메서드에 따른 쿼리를 정의해 놓는 방법
• @NamedQueries 는 @NamedQuery 의 배열로 정의되며 JPQL 을 따른다.
• @NamedNativeQueries 는 @NamedNativeQuery 의 배열로 정의되며 SQL 문을 따른다.
• name 속성 값에는 Entity명.메서드 명을 기술, query 속성 값에는 쿼리문을 기술한다.
• 정의 된 메서드를 리포지토리에 선언하면 된다.

@NamedQueries > @NamedQuery 사용

@Entity
@NamedQueries(
        @NamedQuery(name = "Post.findByTitle", query = "SELECT P FROM Post AS P WHERE P.title = ?1")
)
public class Post {

    @Id @GeneratedValue
    private Long id;

    private String title;

    @Temporal(TemporalType.TIMESTAMP)
    private Date created;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    @Override
    public String toString() {
        return "Post{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", created=" + created +
                '}';
    }
}

public interface PostRepository extends JpaRepository<Post,Long> {

    List<Post> findByTitleStartsWith(String title);

    Optional<Post> findByTitle(String title);
}

결과

Hibernate: 
    call next value for hibernate_sequence
Hibernate: 
    insert 
    into
        post
        (created, title, id) 
    values
        (?, ?, ?)
2020-11-18 19:31:27.118 TRACE 17488 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [TIMESTAMP] - [null]
2020-11-18 19:31:27.120 TRACE 17488 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [Spring data JPA]
2020-11-18 19:31:27.121 TRACE 17488 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [1]
Hibernate: 
    select
        post0_.id as id1_0_,
        post0_.created as created2_0_,
        post0_.title as title3_0_ 
    from
        post post0_ 
    where
        post0_.title=?
2020-11-18 19:31:27.130 TRACE 17488 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Spring data JPA]
2020-11-18 19:31:27.134 TRACE 17488 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_0_] : [BIGINT]) - [1]

---

@NamedNativeQuery 와 @SqlResultSetMapping

• 이 부분은 궁금해서 스스로 해본 결과로, 정확한 사용 방법이라고는 못하겠다.
• @NamedNativeQuery 에 resultSetMapping 속성에 맞는 @SqlResultSetMapping 을 선언한다고 보면 됨
• @SqlResultSetMapping 은 하나의 Mapper 를 보는 듯하며, name 에는 맵퍼이름, entities 에는 @EntityResult의 배열을 선언해서 Entity를 맵핑해주게 된다.
• @EntityResult 는 entityClass 속성에 맵핑할 엔티티를 선언, fields 속성에 @FieldResult 의 배열로 프로퍼티와 컬럼을 맵핑한다.

 

@Entity
@NamedNativeQueries(
        @NamedNativeQuery(name = "Post.findByCreated", query = "SELECT * FROM Post WHERE created = ?1",resultSetMapping = "findByIdMapper")
)
@SqlResultSetMapping(name = "findByIdMapper", entities =
        @EntityResult(entityClass = Post.class,
                fields = {
                    @FieldResult(name = "id", column = "id"),
                    @FieldResult(name = "title",column = "title"),
                    @FieldResult(name = "created",column = "created")
                        }
                    )
)
public class Post {

    @Id @GeneratedValue
    private Long id;

    private String title;

    @Temporal(TemporalType.TIMESTAMP)
    private Date created;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    @Override
    public String toString() {
        return "Post{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", created=" + created +
                '}';
    }
}

public interface PostRepository extends JpaRepository<Post,Long> {

    List<Post> findByTitleStartsWith(String title);

    Optional<Post> findByTitle(String title);

    @Query("SELECT P FROM Post AS P WHERE P.id = ?1")
    Optional<Post> findById(Long id);

    Optional<Post> findByCreated(Date date);
}

잘 맵핑되었다. @SqlResultSetMapping 은 하나의 Mapper 를 정의하는 것이고, entities 속성에는 맵핑할 엔티티들을 정하는 모양이다.

Hibernate: 
    SELECT
        * 
    FROM
        Post 
    WHERE
        created = ?
2020-11-18 19:39:36.286 TRACE 9892 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [TIMESTAMP] - [Wed Nov 18 19:39:35 KST 2020]
2020-11-18 19:39:36.292 TRACE 9892 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id] : [BIGINT]) - [1]
======
Post{id=1, title='Spring data JPA', created=Wed Nov 18 19:39:35 KST 2020}

---

사실상 @Query 를 쓰는 게 코드가 지저분 하지 않아 보이는데, 쓸 일이 반드시 생긴다고 본다.

@Query("SELECT P FROM Post AS P WHERE P.id = ?1")
    Optional<Post> findById(Long id);