쿼리 메소드 -2

쿼리 메소드 Sample 및 JPQL snippet

Keyword	Sample	JPQL snippet
And	findByLastnameAndFirstname	... where X.lastname = ?1 and X.firstname = ?2
Or	findByLastnameOrFirstname	... where X.lastname = ?1 or X.firstname = ?2
Is, Equals	finfByFirstname findByFirstnameIs findByFirstnameEquals	... where X.firstname = ?1
Between	findByStartDateBetween	...where X.startDate between ?1 amd ?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, Null IsNotNull	findByAge(Is)Null	... where X.age is null
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 with appended %)
EndingWith	findByFirstnameEndingWith	... where X.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	... where X.firstname like ?1 (parameter bound with 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>ages)	... where X.age not in ?1
True	findByActiveTrue()	... where X.active = true
False	findByActiveFalse()	... where X.active = false
IgnoreCase	findByFirstnameIgnoreCase	... where UPPER(x.firstname) = UPPER(?1)

 

▶ 상품을 상품명, 상세 설명을 OR 조건 이용해 조회하는 쿼리 메소드

(com.shop.repository.ItemRepository.java)

public interface ItemRepository extends JpaRepository<Item, Long> {
    /* 코드 생략 */
	List<Item> findItemByitemDetail(String itemDetail); 
 }

(com.shop.repository.ItemRepositoryTest.java)

@SpringBootTest
@TestPropertySource(locations="classpath:application-test.properties")
class ItemRepositoryTests {

   /*코드 생략*/
   
	@Test
	@DisplayName("OR 조회 테스트")
	public void findByItemNmOritemDetailTests() {
		this.createItemList();
		List<Item> itemList 
		= itemRepository.findByItemNmOrItemDetail("테스트 상품1","테스트상품 상세3");
		
		for(Item item : itemList) {
			System.out.println(item.toString());
		}
	}
 }

- this.createItemList() : 기존에 만들었던 테스트상품 만드는 메소드 실행해 조회할 대상 만들어줌

---

▶ LessThan 조건 처리하기

(com.shop.repository.ItemRepository.java)

public interface ItemRepository extends JpaRepository<Item, Long> {
    /* 코드 생략 */
   List<Item> findByPriceLessThan(Integer price);
 }

- 파라미터로 넘어온 price 변수보다 값 작은 상품 데이터 조회 쿼리

 

(com.shop.repository.ItemRepositoryTest.java)

@SpringBootTest
@TestPropertySource(locations="classpath:application-test.properties")
class ItemRepositoryTests {

   /*코드 생략*/
   
	@Test
	@DisplayName("가격 LessThan 테스트")
    public void findByPriceLessThanTest() {
    	this.createItemList();
        List<Item> itemList = itemRepository.findByPriceLessThan(10005);
        for(Item item : itemList) {
        System.out.println(item.toString());
        }
     }
  }

현재 출력되는 가격 데이터는 10001 ~ 10004 차례로 출력.

---

OrderBy 키워드 이용하면 오름차순 / 내림차순 조회 가능

 

오름차순 : OrderBy + 속성명 + Asc 키워드

내림차순 : OrderBy + 속성명 + Desc 키워드

 

▶ OrderBy로 정렬 처리

(com.shop.repository.ItemRepository.java)

public interface ItemRepository extends JpaRepository<Item, Long> {
    /* 코드 생략 */
   List<Item> findByPriceLessThanOrderByPriceDesc(Integer price);
 }

 

(com.shop.repository.ItemRepositoryTest.java)

@SpringBootTest
@TestPropertySource(locations="classpath:application-test.properties")
class ItemRepositoryTests {

   /*코드 생략*/
   
	@Test
	@DisplayName("가격 내림차순 조회 테스트")
    public void findByPriceLessThanOrderByPriceDesc() {
    	this.createItemList();
        List<Item> itemList =
    itemRepository.findByPriceLessThanOrderByPriceDesc(10005);
    	for(Item item : itemList) {
    		System.out.println(item.toString());
   		 }
   	 }
 }