Spring Data JPA [SELECT] 쿼리메소드

지난 글에서 만든 Guild 테이블과 길드 구성원의 데이터를 통해 

Spring Data JPA에서 데이터를 조회(SELECT) 하는 방법을 정리한다.

 

1. 쿼리메소드

Spring Data JPA는 메소드의 이름을 통해 쿼리를 실행하도록 지원한다.

이때 메소드 이름은 몇 가지의 규칙을 가지게 된다. 이 규칙이 곧 쿼리 문법이라 봐도 무방할 것 같다.

 

List<T> find[EntityName]By[Colunm]( String str ) ; 

 

Return의 경우 Collection<T> 를 사용하며 그 중 List<T> , Page<T> 를 자주 사용한다.

 

- 장점

 JPA의 특징인 "특정 DB에 종속되지 않는다"는 방식을 그대로 따르기에 

설정된 DB에 대한 SQL 자동 처리를 지원해준다. 즉, 생산성이 향상된다.

 

- 단점

쿼리메소드는 간단한 쿼리를 수행할 수 있는 기능을 제공하지만 실제로 실무의 쿼리는

단순한 [SELECT * FROM TBL WHERE COL1 = 'A'] 처럼 간단하지 않고 훨씬 복잡하다.

이때 쿼리메소드를 통한 조회는 한계가 있기에 QueryDsl , JPQL에 대해 공부해야 한다. 

 

* spring 공식 docs 참고

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.core-concepts

 

2. 쿼리메소드 조회 예제

가장 많이 쓰는 기능들에 대해 코드를 정리하며, 그 외의 조회 조건은 하단의 표를 참고하자.

Repository Interface 를 통해 이번에 정리할 내용을 대략적으로 볼 수 있다.

중요하게 봐야하는 부분은 다음과 같다.

 - 각 예제코드에서 guildRepo 객체를 통해 호출하는 메소드

 - 각 예제의 로그에서 볼 수 있는 생성된 쿼리문 (Hibernate)

public interface GuildRepository extends CrudRepository<Guild, Long>{

	// SELECT 조회
	public List<Guild> findGuildByUserName(String userName);
	
	// SELECT LIKE String% 구문 조회
	public Collection<Guild> findByUserJobStartingWith(String userJob);
		
	// SELECT LIKE %String 구문 조회
	public Collection<Guild> findByUserJobEndingWith(String userJob);
	
	// SELECT LIKE %String% 구문 조회
	public Collection<Guild> findByUserJobContaining(String userJob);
	
	// OR 조건 처리
	public Collection<Guild> findByUserJobContainingOrUserJobContaining(String userJob1, String userJob2);
	
	// AND 조건 처리
	public Collection<Guild> findByUserJobContainingAndUserJobContaining(String userJob1, String userJob2);
	
	// 부등호 처리 ( level > ? :: GreaterThan)
	public Collection<Guild> findByLevelGreaterThan(int level);
	
	// 부등호 처리 ( level < ? :: LessThan)
	public Collection<Guild> findByLevelLessThan(int level);
	
	// 부등호 처리 ( level <= ? :: LessThanEqual)
	public Collection<Guild> findByLevelLessThanEqual(int level);
	
	// userJob% AND Level > 40 Order by 처리
	public Collection<Guild> findByUserJobStartingWithAndLevelGreaterThanOrderByLevelDesc(String userJob, int level);
	
}

 

2-1 단순한 SELECT - WHERE

길드 유저의 이름을 통해 길드원을 조회

@Test
	public void testByTitle() {
		System.out.println(":::: find - By ::::");
		guildRepo.findGuildByUserName("cookie27").forEach(guild -> System.out.println(guild));
	}

:::: find - By ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.user_name=?
Guild(indexNo=27, level=27, userName=cookie27, userJob=도적, authority=길드원, contribution=540, guildRegDate=2022-11-05 15:21:48.0)

 

2-2 LIKE 조회

LIKE 검색은 와일드카드(%)의 위치에 따라 별도의 메소드를 사용한다. (String%, %String , %String%)

String% :: StartingWith

%String :: EndingWith

%String% :: Contatining

@Test
	public void testByUserJobStartingWith() {
		System.out.println(":::: Like String% ::::");
		Collection<Guild> results = guildRepo.findByUserJobStartingWith("기");
		
		results.forEach(guild -> System.out.println(guild));
	}
	
	@Test
	public void testByUserJobEndingWith() {
		System.out.println(":::: Like %String ::::");
		Collection<Guild> results = guildRepo.findByUserJobEndingWith("적");
		
		results.forEach(guild -> System.out.println(guild));
	}
	
	@Test
	public void testByUserJobContaining() {
		System.out.println(":::: Like %String% ::::");
		Collection<Guild> results = guildRepo.findByUserJobContaining("법");
		
		results.forEach(guild -> System.out.println(guild));
	}

:::: Like String% ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.user_job like ? escape ?
Guild(indexNo=3, level=3, userName=cookie3, userJob=기사, authority=길드원, contribution=60, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=4, level=4, userName=cookie4, userJob=기사, authority=길드원, contribution=80, guildRegDate=2022-11-05 15:21:47.0)


:::: Like %String ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.user_job like ? escape ?
Guild(indexNo=5, level=5, userName=cookie5, userJob=도적, authority=길드원, contribution=100, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=8, level=8, userName=cookie8, userJob=도적, authority=길드원, contribution=160, guildRegDate=2022-11-05 15:21:47.0)


:::: Like %String% ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.user_job like ? escape ?
Guild(indexNo=1, level=1, userName=cookie1, userJob=마법사, authority=길드원, contribution=20, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=2, level=2, userName=cookie2, userJob=마법사, authority=길드원, contribution=40, guildRegDate=2022-11-05 15:21:47.0)

 

2-3 OR / AND 연산

OR와 AND는 조건이 여러개인 경우를 나타내므로 [Colunm1] Or [Colunm2] 와 같이 메소드이름을 정한다.

@Test
	public void testByTitleContainingOrContentContaining() {
		System.out.println(":::: OR ::::");
		Collection<Guild> results = guildRepo.findByUserJobContainingOrUserJobContaining("도", "법사");
		results.forEach(guild -> System.out.println(guild));
	}
	
	@Test
	public void testFindByUserJobContainingAndUserJobContaining() {
		System.out.println(":::: AND ::::");
		Collection<Guild> results = guildRepo.findByUserJobContainingAndUserJobContaining("마", "법사");
		results.forEach(guild -> System.out.println(guild));
	}

:::: OR ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.user_job like ? escape ? or guild0_.user_job like ? escape ?
Guild(indexNo=1, level=1, userName=cookie1, userJob=마법사, authority=길드원, contribution=20, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=2, level=2, userName=cookie2, userJob=마법사, authority=길드원, contribution=40, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=5, level=5, userName=cookie5, userJob=도적, authority=길드원, contribution=100, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=7, level=7, userName=cookie7, userJob=마법사, authority=길드원, contribution=140, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=8, level=8, userName=cookie8, userJob=도적, authority=길드원, contribution=160, guildRegDate=2022-11-05 15:21:47.0)

:::: AND ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where (guild0_.user_job like ? escape ?) and (guild0_.user_job like ? escape ?)
Guild(indexNo=1, level=1, userName=cookie1, userJob=마법사, authority=길드원, contribution=20, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=2, level=2, userName=cookie2, userJob=마법사, authority=길드원, contribution=40, guildRegDate=2022-11-05 15:21:47.0)

 

2-4 부등호 연산

부등호 연산자를 처리하기 위해 아래와 같은 메소드를 지원한다.

 

초과 : num > 10  → GreaterThan

미만 : num < 10 → LessThan

이상 : num >= 10 → GreaterThanEquals

이하 : num <= 10 → LessThanEquals

	@Test
	public void testFindByLevelGreaterThan() {
		System.out.println(":::: Greater ::::");
		Collection<Guild> results = guildRepo.findByLevelGreaterThan(20);
		results.forEach(guild -> System.out.println(guild));

	}
	
	@Test
	public void testFindByLevelLessThan() {
		System.out.println(":::: Less ::::");
		Collection<Guild> results = guildRepo.findByLevelLessThan(20);
		results.forEach(guild -> System.out.println(guild));
	}
	
	@Test
	public void testFindByLevelLessThanEqual() {
		System.out.println(":::: LessThanEqual ::::");
		Collection<Guild> results = guildRepo.findByLevelLessThanEqual(20);
		results.forEach(guild -> System.out.println(guild));
	}

:::: Greater ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.level>?
Guild(indexNo=21, level=21, userName=cookie21, userJob=마법사, authority=길드원, contribution=420, guildRegDate=2022-11-05 15:21:48.0)
Guild(indexNo=22, level=22, userName=cookie22, userJob=마법사, authority=길드원, contribution=440, guildRegDate=2022-11-05 15:21:48.0)
Guild(indexNo=23, level=23, userName=cookie23, userJob=마법사, authority=길드원, contribution=460, guildRegDate=2022-11-05 15:21:48.0)


:::: Less ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.level<?
Guild(indexNo=1, level=1, userName=cookie1, userJob=마법사, authority=길드원, contribution=20, guildRegDate=2022-11-05 15:21:47.0)
Guild(indexNo=2, level=2, userName=cookie2, userJob=마법사, authority=길드원, contribution=40, guildRegDate=2022-11-05 15:21:47.0)


:::: LessThanEqual ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where guild0_.level<=?
Guild(indexNo=19, level=19, userName=cookie19, userJob=기사, authority=길드원, contribution=380, guildRegDate=2022-11-05 15:21:48.0)
Guild(indexNo=20, level=20, userName=cookie20, userJob=기사, authority=길드원, contribution=400, guildRegDate=2022-11-05 15:21:48.0)

 

2-5 ORDER BY (정렬)

정렬 기준이 되는 컬럼을 통해 메소드 명을 작성한다

find...By...OrderBy[Colunm][Asc/Desc] ();

 

이번 예는 조금 더 복잡하게 

"직업이 '도' 로 시작하며 레벨이 40 보다 높은 유저정보를 오름(내림)차순으로 정렬" 한 예이다. 

	@Test
	public void testFindByUserJobStartingWithAndLevelGreaterThanOrderByLevelDesc() {
		System.out.println(":::: LIKE + GREATER + ORDER BY(DESC) ::::");
		Collection<Guild> results = guildRepo.findByUserJobStartingWithAndLevelGreaterThanOrderByLevelDesc("도", 40);
		results.forEach(guild -> System.out.println(guild));
	}
	
	@Test
	public void testFindByUserJobStartingWithAndLevelGreaterThanOrderByLevelAsc() {
		System.out.println(":::: LIKE + GREATER + ORDER BY(ASC) ::::");
		Collection<Guild> results = guildRepo.findByUserJobStartingWithAndLevelGreaterThanOrderByLevelAsc("도", 40);
		results.forEach(guild -> System.out.println(guild));
	}

:::: LIKE + GREATER + ORDER BY(ASC) ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where (guild0_.user_job like ? escape ?) and guild0_.level>? order by guild0_.level asc
Guild(indexNo=43, level=43, userName=cookie43, userJob=도적, authority=길드원, contribution=860, guildRegDate=2022-11-05 15:21:48.0)
Guild(indexNo=44, level=44, userName=cookie44, userJob=도적, authority=길드원, contribution=880, guildRegDate=2022-11-05 15:21:48.0)
Guild(indexNo=46, level=46, userName=cookie46, userJob=도적, authority=길드원, contribution=920, guildRegDate=2022-11-05 15:21:48.0)


:::: LIKE + GREATER + ORDER BY(DESC) ::::
Hibernate: select guild0_.index_no as index_no1_0_, guild0_.authority as authorit2_0_, guild0_.contribution as contribu3_0_, guild0_.guild_reg_date as guild_re4_0_, guild0_.level as level5_0_, guild0_.user_job as user_job6_0_, guild0_.user_name as user_nam7_0_ from tbml_guild_info guild0_ where (guild0_.user_job like ? escape ?) and guild0_.level>? order by guild0_.level desc
Guild(indexNo=46, level=46, userName=cookie46, userJob=도적, authority=길드원, contribution=920, guildRegDate=2022-11-05 15:21:48.0)
Guild(indexNo=44, level=44, userName=cookie44, userJob=도적, authority=길드원, contribution=880, guildRegDate=2022-11-05 15:21:48.0)
Guild(indexNo=43, level=43, userName=cookie43, userJob=도적, authority=길드원, contribution=860, guildRegDate=2022-11-05 15:21:48.0)

 

# 더 많은 쿼리메소드 표현방식은 Spring 공식 docs에 정리되어 있다. (Qeury Creation 항목)

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods

Spring Data JPA - Reference Documentation

 

 

Table 3. Supported keywords inside method namesKeywordSampleJPQL snippet

Distinct	findDistinctByLastnameAndFirstname	select distinct …​ where x.lastname = ?1 and x.firstname = ?2
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is, Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
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, Null	findByAge(Is)Null	… 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 with appended %)
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> 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)

 

 

 

※ Spring Data JPA는 구멍가게코딩단의 [초급 개발자들을 위한 가볍고 넓은 스프링부트 스타트 스프링 부트2.0] 책을 참고하였습니다.