SpringBoot 쿼리 메소드 사용하기

# 쿼리 메소드 사용하기

 

Keyword	Sample	JPQL snippet
And	findByLastnameAndFirstname	... where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	... where x.lastname = ?1 or x.firstname = ?2
Between	findByStartDateBetween	... where x.startDate between 1? and ?2
LessThan	findByAgeLessThan	... where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	... where x.age <= ?1
GreaterThan	findByAgeGreaterThan	... where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	... where x.age >= ?1
After	findByStartDateAfter	... where x.startDate > ?1
Before	findByStartDateBefore	... where x.startDate < ?1
IsNull	findByAgeIsNull	... where x.age is null
IsNotNull, NotNull	findByAge(Is)NotNull	... where x.age not null
Like	findByFirstnameLike	... where x.firstname like ?1
NotLike	findByFirstnameNotLike	... where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	... where x.firstname like ?1 (parameter bound wrapped in % )
EndingWith	findByFirstnameEndingWith	... where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	... where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	... where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	... where x.lastname <> ?1
In	findByAgeIn(Collection<age> ages)	... where x.age in ?1
NotIn	findByAgeNotIn(Collection <Age> age)	... where x.age not in ?1
True	findByActiveTrue()	... where x.active = true
False	findByActiveFalse()	... where x.active = false

 

# 예제

 

- public List<Board> findBoardByTitle(String title);

  

- public Collection<Board> findByWriter(String writer);

 

- public Collection<Board> findByWriterContaining(String writer);

 

- public Collection<Board> findByTitleContainingOrContentContaining(String title, String content);

 

- public Collection<Board> findByTitleContainingAndBnoGreaterThan(String keyword, Long num);

 

- public Collection<Board> findByBnoGreaterThanOrderByBnoDesc(Long bno);

 

- public List<Board> findByBnoGreaterThanOrderByBnoDesc(Long bno, Pageable paging);

 

- public List<Board> findByBnoGreaterThan(Long bno, Pageable paging);

 

- public Page<Board> findByBnoGreaterThan(Long bno, Pageable paging);

 

---

# @Query 이용하기

 

- 리턴 값이 반드시 엔티티 타입이 아니라 필요한 몇 개의 칼럼 값들만 추출할 수 있다.

- nativeQuery 속성을 지정해서 데이터베이스에 사용하는 SQL을 그대로 사용할 수 있다.

- repository에 지정된 엔티티 타입 뿐 아니라 필요한 엔티티 타입을 다양하게 사용할 수 있다.

 

# 예제 

 

- @Query("SELECT b FROM Board b WHERE b.title LIKE %?1% AND b.bno > 0 ORDER BY b.bno DESC")

  public List<Board> findByTitle

 

- @Query("SELECT b FROM Board b WHERE b.content LIKE %:content% AND b.bno > 0 ORDER BY b.bno DESC")

  public List<Board> findByContent(@Param("content") String content);

 

- @Query("SELECT b FROM #{#entityName} b WHERE b.writer LIKE %?1% AND b.bno >0 ORDER BY b.bno DESC")

  List<Board> findByWriter(String writer);

 

- @Query("SELECT b.bno, b.title, b.writer, b.regdate FROM Board b WHERE b.title LIKE %?1%

               AND b.bno > 0 ORDER By b.bno DESC)

  public List<Objectp[> findByTitle2(String title);

 

 

# nativeQuery 사용

 

- @Query(value="SELECT bno, title, writer from tb1_boards where title like CONCAT('%', ?1, '%') and bno > 0

             order by bno desc", nariveQuery = true)

  List<Object[]> findByTitle3(String title);

 

- @Query("SELECT p FROM Post AS p WHERE p.title = :title")

  List<Post>findByTitle(@Param("title") String title, Sort sort);

 

- @Query("SELECT p FROM #{#entityName} AS p WHERE p.title = :title")

  List<Post> findByTitle(@Param("title") String title, Sort sort);

 

- @Modifying(clearAutomatically = true, flushAutomatically = true)

  @Query("UPDATE Post p SET p.title = ?2 WHERE p.id = ?1")

  int updateTitle(Long id, String title);