개발환경
OS: Windows 11
IDE: Spring Tool Suite 4.14.1
JAVA: 17
a. Specification
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;
import com.armton.history.entity.ServiceHistory;
public class HistorySpecification {
/**
* @description equal 조건문 생성
* @param successYn {String} 조건값
* @return {Specification<ServiceHistory>} Specification 객체
*/
public static Specification<ServiceHistory> equalSuccessYn(String successYn) {
return (root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("successYn"), successYn);
}
}
criteriaBuilder.equal(엔티티 객체, 조건값)
메소드를 사용한다.root
: 엔티티 객체query
: 지정 쿼리criteriaBuilder
: 객체 지향 쿼리 빌더b. 사용
import org.springframework.data.jpa.domain.Specification;
@RestController
public class HistoryController {
/**
* @description 로그 리스트 조회(ajax)
* @param request
* @param historyParam{HistoryDTO} where 조건 모델
* @param pageable {Pageable} jpa 페이징 파라미터
* @return result {Map<String, Object>} 조회 리스트
*/
@RequestMapping("/json/getHistoryList.json")
@ResponseBody
public Map<String, Object> getHistoryList(HttpServletRequest request, @RequestBody HistoryDTO historyParam, Pageable pageable) {
...
Specification<ServiceHistory> spec = (root, query, criteriaBuilder) -> null;
if (historyParam.getSuccessYn() != null)
spec = spec.and(HistorySpecification.equalSuccessYn(historyParam.getSuccessYn()));
}
List<ServiceHistory> list = historyRepository.findAll(spec);
...
}
c. Repository
import java.util.List;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.armton.history.entity.ServiceHistory;
public interface HistoryRepository extends JpaRepository<ServiceHistory, Long>, JpaSpecificationExecutor<ServiceHistory> {
List<ServiceHistory> findAll(Specification<ServiceHistory> spec);
}
a. Specification
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;
import com.armton.history.entity.ServiceHistory;
public class HistorySpecification {
/**
* @description like 조건문 생성
* @param logData{String} 조건값
* @return {Specification<ServiceHistory>} Specification 객체
*/
public static Specification<ServiceHistory> likeLogData(String logData) {
return (root, query, criteriaBuilder) -> criteriaBuilder.like(root.get("logData"), '%' + logData + '%');
}
}
criteriaBuilder.like(엔티티 객체, 조건값)
메소드를 사용한다.like
조건의 경우 직접 바인딩될 값에 ‘%’
를 붙여줘야 한다.root
: 엔티티 객체query
: 지정 쿼리criteriaBuilder
: 객체 지향 쿼리 빌더b. 사용
import org.springframework.data.jpa.domain.Specification;
@RestController
public class HistoryController {
/**
* @description 로그 리스트 조회(ajax)
* @param request
* @param historyParam{HistoryDTO} where 조건 모델
* @param pageable {Pageable} jpa 페이징 파라미터
* @return result {Map<String, Object>} 조회 리스트
*/
@RequestMapping("/json/getHistoryList.json")
@ResponseBody
public Map<String, Object> getHistoryList(HttpServletRequest request, @RequestBody HistoryDTO historyParam, Pageable pageable) {
...
Specification<ServiceHistory> spec = (root, query, criteriaBuilder) -> null;
if (historyParam.getLogData() != null)
spec = spec.and(HistorySpecification.likeLogData(historyParam.getLogData()));
List<ServiceHistory> list = historyRepository.findAll(spec);
...
}
and
혹은 or
로 바인딩(여기선 and
를 사용)c. Repository
import java.util.List;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.armton.history.entity.ServiceHistory;
public interface HistoryRepository extends JpaRepository<ServiceHistory, Long>, JpaSpecificationExecutor<ServiceHistory> {
List<ServiceHistory> findAll(Specification<ServiceHistory> spec);
}
a. Specification
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;
import com.armton.history.entity.ServiceHistory;
public class HistorySpecification {
/**
* @description like 조건문 생성
* @param serviceType {List<String>} 조건값
* @return {Specification<ServiceHistory>} Specification 객체
*/
public static Specification<ServiceHistory> inServiceType(List<String> serviceType) {
return new Specification<ServiceHistory>() {
@Override
public Predicate toPredicate(Root<ServiceHistory> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
return criteriaBuilder.and(root.get("serviceType").in(serviceType));
}
};
}
}
Collection 타입
이어야 한다.public static Specification<ServiceHistory> inServiceType(List<String> serviceType) {
return criteriaBuilder.in(root.get("serviceType").in(serviceType));
}
criteriaBuilder.in();
메소드를 사용하여 리턴하게 되면 QuerySyntaxException: unexpected token
에러가 발생함. 이때 생성된 쿼리를 보면 where 조건에 generatedAlias0.serviceType in (:param2, :param3) in (null)
처럼 in 조건이 두번 들어가버리게 되는 것을 발견할 수 있다.b. 사용
import org.springframework.data.jpa.domain.Specification;
@RestController
public class HistoryController {
/**
* @description 로그 리스트 조회(ajax)
* @param request
* @param historyParam{HistoryDTO} where 조건 모델
* @param pageable {Pageable} jpa 페이징 파라미터
* @return result {Map<String, Object>} 조회 리스트
*/
@RequestMapping("/json/getHistoryList.json")
@ResponseBody
public Map<String, Object> getHistoryList(HttpServletRequest request, @RequestBody HistoryDTO historyParam, Pageable pageable) {
...
Specification<ServiceHistory> spec = (root, query, criteriaBuilder) -> null;
if (historyParam.getServiceType() != null) {
List<String> serviceTypeParamList = Arrays.asList(historyParam.getServiceType());
spec = spec.and(HistorySpecification.inServiceType(serviceTypeParamList));
}
List<ServiceHistory> list = historyRepository.findAll(spec);
...
}
c. Repository
import java.util.List;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.armton.history.entity.ServiceHistory;
public interface HistoryRepository extends JpaRepository<ServiceHistory, Long>, JpaSpecificationExecutor<ServiceHistory> {
List<ServiceHistory> findAll(Specification<ServiceHistory> spec);
}
a. Specification
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;
import com.armton.history.entity.ServiceHistory;
public class HistorySpecification {
/**
* @description between조건문 생성
* @param logDtStart{String} 조건 시작값
* @param logDtEnd{String} 조건 끝값
* @return {Specification<ServiceHistory>} Specification 객체
*/
public static Specification<ServiceHistory> betweenLogDt(String logDtStart, String logDtEnd) {
return (root, query, criteriaBuilder) -> criteriaBuilder.between(root.get("logDt"), logDtStart, logDtEnd);
}
}
Specification<엔티티 객체>
로 반환하는 static 메소드를 만들고 criteriaBuilder.between(엔티티 객체, 시작값, 끝값)
메소드를 사용한다.root
: 엔티티 객체query
: 지정 쿼리criteriaBuilder
: 객체 지향 쿼리 빌더b. 사용
import org.springframework.data.jpa.domain.Specification;
@RestController
public class HistoryController {
/**
* @description 로그 리스트 조회(ajax)
* @param request
* @param historyParam{HistoryDTO} where 조건 모델
* @param pageable {Pageable} jpa 페이징 파라미터
* @return result {Map<String, Object>} 조회 리스트
*/
@RequestMapping("/json/getHistoryList.json")
@ResponseBody
public Map<String, Object> getHistoryList(HttpServletRequest request, @RequestBody HistoryDTO historyParam, Pageable pageable) {
...
Specification<ServiceHistory> spec = (root, query, criteriaBuilder) -> null;
if (historyParam.getLogDt_START() != null && historyParam.getLogDt_END() != null)
spec = spec.and(HistorySpecification.betweenLogDt(historyParam.getLogDt_START(), historyParam.getLogDt_END()));
List<ServiceHistory> list = historyRepository.findAll(spec);
...
}
c. Repository
import java.util.List;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.armton.history.entity.ServiceHistory;
public interface HistoryRepository extends JpaRepository<ServiceHistory, Long>, JpaSpecificationExecutor<ServiceHistory> {
List<ServiceHistory> findAll(Specification<ServiceHistory> spec);
}
a. org.springframework.data.domain.PageRequest를 반환할 객체 생성
package com.armton.util;
import org.springframework.data.domain.Sort.Direction;
/**
* @author Armton
* @description JPA 페이징 파라미터 생성
*/
public class PageRequest {
private int page = 1; // 페이지 번호
private int size = 10; // limit 개수
public void setPage(int page) {
this.page = page <= 0 ? 1 : page;
}
public void setSize(int size) {
int DEFAULT_SIZE = 10;
int MAX_SIZE = 500;
this.size = size > MAX_SIZE ? DEFAULT_SIZE : size;
}
// Pageble에 바인딩 할 PageRequest 값
public org.springframework.data.domain.PageRequest of() {
return org.springframework.data.domain.PageRequest.of(page - 1, size);
}
}
b. 사용
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
@RestController
public class HistoryController {
/**
* @description 로그 리스트 조회(ajax)
* @param request
* @param historyParam{HistoryDTO} where 조건 모델
* @param pageable {Pageable} jpa 페이징 파라미터
* @return result {Map<String, Object>} 조회 리스트
*/
@RequestMapping("/json/getHistoryList.json")
@ResponseBody
public Map<String, Object> getHistoryList(HttpServletRequest request, @RequestBody HistoryDTO historyParam, Pageable pageable) {
...
PageRequest pageRequest = new PageRequest();
pageRequest.setPage(historyParam.getPage());
pageRequest.setSize(historyParam.getSize());
pageable = pageRequest.of();
Page<ServiceHistory> list1 = historyRepository.findAll(spec, pageable);
...
}
c. 레파지토리
import java.util.List;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.armton.history.entity.ServiceHistory;
public interface HistoryRepository extends JpaRepository<ServiceHistory, Long>, JpaSpecificationExecutor<ServiceHistory> {
Page<ServiceHistory> findAllBy(Pageable pageable);
Page<ServiceHistory> findAllBy(Specification<ServiceHistory> spec, Pageable pageable); // 조건문 사용 시
}
package com.armton.util;
import org.springframework.data.domain.Sort.Direction;
/**
* @author Armton
* @description JPA 페이징 파라미터 생성
*/
public class PageRequest {
private int page = 1; // 페이지 번호
private int size = 10; // limit 개수
private Direction direction = Direction.ASC; // 기본 소트 방향 설정
public void setPage(int page) {
this.page = page <= 0 ? 1 : page;
}
public void setSize(int size) {
int DEFAULT_SIZE = 10;
int MAX_SIZE = 500;
this.size = size > MAX_SIZE ? DEFAULT_SIZE : size;
}
// Pageble에 바인딩 할 PageRequest 값
public org.springframework.data.domain.PageRequest of() {
return org.springframework.data.domain.PageRequest.of(page - 1, size);
}
----------------------추가----------------------
// setter
public void setDirection(Direction direction) {
this.direction = direction;
}
/**
* @description 오버로딩
* @param request
* @param dir {Direction} 소트 방향
* @param col {String} 소트 기준 컬럼
* @return {PageRequest}
*/
public org.springframework.data.domain.PageRequest of(Direction dir, String col) {
if(col == null) col = "id";
if(dir == null) dir = direction;
return org.springframework.data.domain.PageRequest.of(page - 1, size, dir, col);
}
}
a. 사용
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
@RestController
public class HistoryController {
/**
* @description 로그 리스트 조회(ajax)
* @param request
* @param historyParam{HistoryDTO} where 조건 모델
* @param pageable {Pageable} jpa 페이징 파라미터
* @return result {Map<String, Object>} 조회 리스트
*/
@RequestMapping("/json/getHistoryList.json")
@ResponseBody
public Map<String, Object> getHistoryList(HttpServletRequest request, @RequestBody HistoryDTO historyParam, Pageable pageable) {
...
PageRequest pageRequest = new PageRequest();
pageRequest.setPage(historyParam.getPage());
pageRequest.setSize(historyParam.getSize());
pageable = pageRequest.of(Direction.DESC, "id"); // order by id desc
Page<ServiceHistory> list1 = historyRepository.findAll(spec, pageable);
...
}
b. 레파지토리
import java.util.List;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.armton.history.entity.ServiceHistory;
public interface HistoryRepository extends JpaRepository<ServiceHistory, Long>, JpaSpecificationExecutor<ServiceHistory> {
Page<ServiceHistory> findAllBy(Pageable pageable);
Page<ServiceHistory> findAllBy(Specification<ServiceHistory> spec, Pageable pageable); // 조건문 사용 시
}