SpringData JpaRepository İle JpaSpecification - Karmaşık Select Sorguları İçindir

Giriş

Şu satırı dahil ederiz

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.domain.Specification;

Açıklaması şöyle. Dynamic DB Query içindir

JPA 2 introduces a criteria API that you can use to build queries programmatically. By writing a criteria, you define the where clause of a query for a domain class. Taking another step back, these criteria can be regarded as a predicate over the entity that is described by the JPA criteria API constraints.

Spring Data JPA takes the concept of a specification from Eric Evans' book, “Domain Driven Design”, following the same semantics and providing an API to define such specifications with the JPA criteria API. To support specifications, you can extend your repository interface with the JpaSpecificationExecutor interface

Specification Neden Lazım

Bir sürü isteğe bağlı parametre alan bir sorgumuz 

SQL ile şöyle yaparız. Her parametre isteğe bağlı olduğu için coalesce ile kullanmak gerekiyor.

@Query(
"select * from user u
left outer join education_detail e on u.id = e.user_id 
where (coalesce(:userStatuses) is null or (u.userStatus in (:userStatuses))) 
and (coalesce(:ageGreaterThanOrEqualTo) is null or (u.age >= (:ageGreaterThanOrEqualTo)))
and (coalesce(:gradeGreaterThanOrEqualTo) is null or 
  (e.grade in (:gradeGreaterThanOrEqualTo)))")
  List<User> findAllUsers(
            List<String> userStatuses,
            Integer ageGreaterThanOrEqualTo,
            Double gradeGreaterThanOrEqualTo
);

Derived query ile şöyle yaparız

List<User> findByUserStatusIn(List<String> userStatuses);

List<User> findByUserStatusInAndAgeGreaterThanOrEqualTo(List<String> userStatuses, 
  Integer ageGreaterThanOrEqualTo);

List<User>findByUserStatusInAndAgeGreaterThanOrEqualToAndGradeGreaterThanOrEqualTo(
  List<String> userStatuses, 
  Integer ageGreaterThanOrEqualTo, 
  Double gradeGreaterThanOrEqualTo);

Her ikisi de karışık çözümler

Bunun yerine org.springframework.data.jpa.domain.Specification arayüzünden kalıtan bir sınıf yazıyoruz. Sınıf şöyle

static Specification<User> userStatusesIn(List<String> userStatuses) {
    return (root, query, builder) -> {
        if(CollectionUtils.isEmpty(userStatuses)){
            return builder.conjunction();
        }
        return root.get(User_.status).in(userStatuses);
    };
}

Böylece şu SQL otomatik üretiliyor.

where (coalesce(:userStatuses) is null or (u.userStatus in (:userStatuses)))

Repository Tanımlama

Repository kodu artık şöyle oluyor

public interface CustomerRepository extends CrudRepository<Customer, Long>,
  JpaSpecificationExecutor<Customer> {
 …
}

Bu arayüz ile gelen metodlar  şöyle

public interface JpaSpecificationExecutor<T> {

  Optional<T> findOne(@Nullable Specification<T> spec);

  List<T> findAll(@Nullable Specification<T> spec);

  Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

  List<T> findAll(@Nullable Specification<T> spec, Sort sort);

  long count(@Nullable Specification<T> spec);
}

Specification Tanımlama

and(), not(), or(), toPredicate(), where() metodları var. Amacımız bir Specification nesnesi yaratıp bunu Repository nesnesinin findX() metodlarından birisine geçmek

toPredicate metodu

İmzası şöyle. Diğer metodlar ya default ya da static ancak eğer Specification arayüzünden kalıtırsak bu metodu kodlamak gerekiypr

public interface Specification<T> {
  Predicate toPredicate(Root<T> root, CriteriaQuery<?> query,
            CriteriaBuilder builder);
}

Örnek - Builder Kullanan Specification

Servis sınıfımız şöyle olsun

@Autowired
private EmployeeRepository employeeRepo;

@Transactional(readOnly = true)
public List<EmployeeVO> listEmployeesUsingSpecification(int page, int pageSize, 
  String sortField, Sort.Direction sortDirection, LocalDate birthDate,
  LocalDate hireDate, String title, Integer salary, String searchTerm) {

  Pageable pageable = pageable(page, pageSize, sortField, sortDirection);

  Specification<Employee> spec = EmployeeSpecification.builder()
    .birthDate(birthDate)
    .hireDate(hireDate)
    .salary(salary)
    .title(title)
    .searchTerm(searchTerm)
    .build();

  Page<Employee> employees = employeeRepo.findAll(spec, pageable);

  return employees.stream()
    .map(EmployeeMapper::map)
    .collect(Collectors.toList());
}

private Pageable pageable(int page, int pageSize, String sortField, 
  Direction sortDirection) {
  return PageRequest.of(page, pageSize, sortDirection, sortField);
}

Builder kullanan Specification için şöyle yaparız

@Data
@Builder
public class EmployeeSpecification implements Specification<Employee> {

  private static final LocalDate CURRENTLY_ACTIVE_ENTRY = LocalDate.of(9999, 1, 1);

  private LocalDate birthDate;
  private LocalDate hireDate;
  private Integer salary;
  private String title;

  private String searchTerm;

  @Override
  public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

    Predicate birthDatePred = ofNullable(birthDate)
      .map(b -> equals(cb, root.get("birthDate"), birthDate))
      .orElse(null);
    Predicate hireDatePred = ofNullable(hireDate)
      .map(h -> equals(cb, root.get("hireDate"), hireDate))
      .orElse(null);

    Predicate salaryPred = salaryPredicate(root, cb);
    Predicate titlePred = titlePredicate(root, cb);

    if (nonNull(salaryPred) || nonNull(titlePred)) {
      query.distinct(true);
    }

    Predicate searchPred = null;
    if (StringUtils.isNoneBlank(searchTerm)) {
      Predicate firstNamePred = like(cb, root.get("firstName"), searchTerm);
      Predicate lastNamePred = like(cb, root.get("lastName"), searchTerm);

      searchPred = cb.or(firstNamePred, lastNamePred);
    }

    List<Predicate> predicates = new ArrayList<>();

    ofNullable(birthDatePred).ifPresent(predicates::add);
    ofNullable(hireDatePred).ifPresent(predicates::add);
    ofNullable(salaryPred).ifPresent(predicates::add);
    ofNullable(titlePred).ifPresent(predicates::add);
    ofNullable(searchPred).ifPresent(predicates::add);
    return cb.and(predicates.toArray(new Predicate[predicates.size()]));
  }

  private Predicate salaryPredicate(Root<Employee> root, CriteriaBuilder cb) {
    if (isNull(salary)) {
      return null;
    }

    Join<Employee, Title> salaryJoin = root.join("salaries", JoinType.INNER);

    int salaryLow = Double.valueOf(salary * 0.9).intValue();
    int salaryHigh = Double.valueOf(salary * 1.1).intValue();

    return cb.and(
      between(cb, salaryJoin.get("salary"), salaryLow, salaryHigh),
      equals(cb, salaryJoin.get("to"), CURRENTLY_ACTIVE_ENTRY));
  }

  private Predicate titlePredicate(Root<Employee> root, CriteriaBuilder cb) {
    if (isAllBlank(title)) {
      return null;
    }

    Join<Employee, Title> titleJoin = root.join("titles", JoinType.INNER);

    return cb.and(
      like(cb, titleJoin.get("title"), title),
      equals(cb, titleJoin.get("to"), CURRENTLY_ACTIVE_ENTRY));
  }

  private Predicate equals(CriteriaBuilder cb, Path<Object> field, Object value) {
    return cb.equal(field, value);
  }

  private Predicate like(CriteriaBuilder cb, Path<String> field, String searchTerm) {
    return cb.like(cb.lower(field), "%" + searchTerm.toLowerCase() + "%");
  }

  private Predicate between(CriteriaBuilder cb, Path<Integer> field, int min, int max) {
    return cb.between(field, min, max);
  }
}

Örnek - in + greaterThanOrEqualTo + join

FilterDTO şöyle olsun

public class FilterDTO {
  List<UserStatus> userStatuses;
  Integer ageGreaterThanOrEqualTo; 
  Double gradeGreaterThanOrEqualTo;
}

Specification şöyle olsun

public static Specification<User> createSpecification(FilterDTO filter){
  return userStatusesIn(filter.getUserStatusIn())
    .ageGte(filter.getAgeGreaterThanOrEqualTo())   
    .gradeGte(filter.getGradeGreaterThanOrEqualTo())
}

private static Specification<User> userStatusesIn(List<String> userStatuses) {
  return (root, query, builder) -> {
    if(CollectionUtils.isEmpty(userStatuses)){
      return builder.conjunction();
    }
    return root.get(User_.status).in(userStatuses);
  };
}

private static Specification<User> ageGte(Integer ageGte) {
  return (root, query, builder) -> {
    if(Objects.isNull(ageGte)){
      return builder.conjunction();
    }
    return builder.greaterThanOrEqualTo(root.get(User_.age), 
                                        ageGte);
  };
}

private static Specification<User> gradeGte(Integer gradeGte) {
  return (root, query, builder) -> {
    if(Objects.isNull(gradeGte)){
      return builder.conjunction();
    }
    Join<User, EducationDetail> userEducationDetailJoin = 
      root.join(User_.educationDetail);

    return builder.greaterThanOrEqualTo(
      userEducationDetailJoin.get(EducationDetail_.grade),
      gradeGte);
  };
}

Repository şöyle olsun. Burada N+1 Select problemi için @EntittyGraph kullanılıyor.

@Repository
public interface UserRepository extends JpaRepository<User,UUID>, 
  JpaSpecificationExecutor<User> {  

  @EntityGraph(
    type = EntityGraphType.FETCH,
    attributePaths = {
      "educationDetail"
    }
  )
  List<User> findAll(Specification<User> specification);
}

Örnek - lessThan + like + between + equal

Repository şöyle olsun

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;


@Repository
public interface CommentRepository extends
        JpaRepository <Comment, UUID>,
        JpaSpecificationExecutor<Comment> {

  Optional<Page<Comment>> findAllByUserId(String userId, Pageable page);

  Page<Comment> findAll(Specification<Comment> specification, Pageable page);

  List<Comment> findAll(Specification<Comment> specification);

  Page<Comment> findAll(Pageable page);

  List<Comment> findAll();

}

Specification yaratan kodlar şöyle olsun. Burada her şeyi birleştiren kod conditionalSearchForUser()

@Component
public class CommentSpecification {

    public  Specification<Comment> isLongTermUser() {
        return  (root,  query,  cb) ->
           cb.lessThan(
               root.get("createdDate"),  
               LocalDate.of(2020, 01, 01)
                   .atStartOfDay(
                         ZoneId.of("Australia/Sydney")
                    )
                  .toInstant()
           );
    }

    
   public  Specification<Comment> commentLike (String searchText) {
        return (root, query, cb) -> 
           cb.like(
               root.get("description"), 
               "%"+ searchText + "%" 
           );
    }

    public  Specification<Comment> createdBetween ( 
                Instant fromDate, 
                Instant toDate) {
          return (root, query, cb) -> 
              cb.between(
                  root.get("createdDate"), 
                  fromDate, toDate 
         );
     }
    public  Specification<Comment> byUser ( String userId) {
        return (root, query, cb) -> 
              cb.equal(
                   root.get("userId"), 
                   userId 
              );
    }
    public Specification<Comment> conditionalSearchForUser( 
                         String searchText,
                         Instant fromDate,
                         Instant toDate,
                         String userId,
                         boolean checkIsLongTermUser
     ) {
     Specification spec = null;
     spec = where(byUser(userId));
     if(checkIsLongTermUser == true) 
       spec = spec.and(isLongTermUser());
        
     if(searchText!=null && !searchText.isBlank()) 
       spec = spec.and(commentLike(searchText));
     if(fromDate!= null && !fromDate.isBlank() 
        && toDate!= null && !toDate.isBlank()) {
           spec =  spec.and(
               createdBetween(
                   LocalDate.parse(fromDate).atStartOfDay(
                       ZoneId.of("Australia/Sydney")
                   )
                   .toInstant(),
                   LocalDate.parse(toDate).atStartOfDay(
                       ZoneId.of("Australia/Sydney")
                   )
                   .toInstant()
                )
           );
       }
        return  spec;
    }
}

Şöyle yaparız. Burada Specificaiton + Page kullanılıyor

commentRepository.findAll(
  commentSpecification.conditionalSearchForUser( 
                         searchText,  
                         fromDate,
                         toDate,
                         userId,  
                         checkIsLongTermUser),
                  page);

Örnek - like + join

Specification kodlarımız şöyle olsun

static Specification<Insurance> withTripSource(@Nullable String source) {
  return (root, cq, cb) -> title == null ? null : 
    cb.like(cb.lower(root.join("trip", JoinType.LEFT).get("source")), 
            cb.lower(cb.literal("%" + source + "%")));
}

Şöyle yaparız

public interface InsuranceRepository extends JpaRepository<Insurance, UUID>,
  JpaSpecificationExecutor<Insurance> {
}

Kullanmak için şöyle yaparız

Specification<Insurance> specs = Specification
        .where(withStatus(status))
        .and(withTripSource(source))
        .and(withTripDestination(destination))
        .and(withPostCompanyName(name));

Pageable paging = PageRequest.of(page, size);
return insuranceRepository.findAll(specs, paging);