将JPA与多个AND运算结合使用

Question

我正在开发一个Spring应用程序，并在一个存储库上定义各种查找方法:

@Repository
public interface TicketRepository extends JpaRepository<TicketEntity, Long> {

List<TicketEntity> findByTicketId(@Param("ticketId") Long ticketId);

List<TicketEntity> findByTicketIdAndState(@Param("ticketId") Long ticketId, @Param("state") String state);

List<TicketEntity> findByTicketIdAndStateAndFlagged(@Param("ticketId") Long ticketId, @Param("state") String state, @Param("flagged") String Flagged);

}

问题是我有30列，可以选择对其进行筛选。这将导致存储库方法变得笨拙：

List<TicketEntity> findByTicketIdAndStateAndFlaggedAndCol4AndCol5AndCol6AndCol7AndCol8AndCol9AndCol10AndCol11AndCol12AndCol13AndCol14AndCol15AndCol16AndCol17AndCol18AndCol19AndCol120....);

JPA层应该如何设计以适应这种情况？

如果我创建一个具有属性的对象:

public class SearchObject {
   private String attribute1; 
   //Getter and Setters
.
.
.
.
}

我可以将＜code＞SearchObject＜/code＞传递到一个查找方法中，Spring JPA将根据哪些属性为空来确定要插入哪些属性和用于哪些属性的语句-如果属性不为空，则为该属性生成相应的语句。

Answer 1

使用Spring Data JPA规范

细节解决方案：耐心等待

首先，创建一个SpecificationCriteria类来定义您的准则。这意味着将列过滤为键，将值过滤为值

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class SpecificationCriteria {
  private String key;
  private Object value;
}

然后创建规范标准构建器以构建您的标准

@Service
public class SpecificationCriteriaBuilder {

  public List<SpecificationCriteria> buildCriterias(String name) {
    List<SpecificationCriteria> specificationCriterias = new ArrayList<SpecificationCriteria>();
    if (!StringUtils.isEmpty(name)) {
      specificationCriterias
          .add(SpecificationCriteria.builder().key("name")
              .value(name).build());
    }
    // Here you can add other filter one by one 
    return specificationCriterias;
  }
}

然后创建一个SpecificationBuilder类来构建您的规范。您可以从过滤器选项（标准）列表构建到规范列表

import java.util.List;
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 org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

@Service
public class SpecificationBuilder<T> {

  public Specification<T> buildSpecification(List<SpecificationCriteria> specificationCriterias) {
    if (ObjectUtils.isEmpty(specificationCriterias)) {
      return null;
    }
    Specification<T> specification = getSpecification(specificationCriterias.get(0));
    for (int index = 1; index < specificationCriterias.size(); index++) {
      SpecificationCriteria specificationCriteria = specificationCriterias.get(index);
      specification =
          Specification.where(specification).and(getSpecification(specificationCriteria));
    }
    return specification;
  }

  public Specification<T> getSpecification(SpecificationCriteria specificationCriteria) {
    Specification<T> specification = new Specification<T>() {
      private static final long serialVersionUID = 2089704018494438143L;
      @Override
      public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.equal(root.get(specificationCriteria.getKey()),
            specificationCriteria.getValue());
      }
    };
    return specification;
  }
}

在服务中首先构建标准，然后使用它们构建规范。然后在存储库调用中使用规范

@Service
@Transactional
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserService {

private final SpecificationCriteriaBuilder criteriaBuilder;
private final SpecificationBuilder<User> specificationBuilder;
private final UserRepository userRepository;

public List<User> getAll(String name) {
    List<SpecificationCriteria> specificationCriterias =
        criteriaBuilder.buildCriterias(name); // here you can pass other parameter as function argument 
    Specification<User> specification =
        specificationBuilder.buildSpecification(specificationCriterias);
    List<User> users = userRepository.findAll(specification);// pass the specifications
    return users;
  }

存储库扩展JpaSpecificationExecutor

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

}

Answer 2

1. 创建将包含所有可选列的过滤器对象，例如：

@AllArgsConstructor公共类TicketFilter{

private final String col1;
private final Integer col2;

public Optional<String> getCol1() {
    return Optional.ofNullable(col1);
}

public Optional<Integer> getCol2() {
    return Optional.ofNullable(col2);
}

}

创建规范类：

公共类票证规范实现规范 {

private final TicketFilter ticketFilter;

public TicketSpecification(TicketFilter ticketFilter) {
    this.ticketFilter = ticketFilter;
}

@Override
public Predicate toPredicate(Root<Ticket> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
    List<Predicate> predicates = new ArrayList<>();
    ticketFilter.getTitle().ifPresent(col1 -> predicates.add(getCol1Predicate(root, col1)));
    ticketFilter.getDescription().ifPresent(col2 -> predicates.add(getCol2Predicate(root, col2)));
    return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
}

私有谓词getCol1Predicate（根，根，字符串标题）{return Root.get（“col1”）.in（col1）；}

}

使用您的存储库：ticketRepository.findAll（规范）；