将 JPA 与多个 AND 运算结合使用 [英] Using JPA with multiple AND operations

问题描述

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

I'm working on a Spring app and defining various find methods on a repository:

@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 列可以选择过滤.这将导致存储库方法变得笨拙:

The problem is that I have 30 columns which can be optionally filtered on. This is will result in the repository methods becoming unwieldy:

List<TicketEntity> findByTicketIdAndStateAndFlaggedAndCol4AndCol5AndCol6AndCol7AndCol8AndCol9AndCol10AndCol11AndCol12AndCol13AndCol14AndCol15AndCol16AndCol17AndCol18AndCol19AndCol120....);

应该如何设计 JPA 层来满足这种情况?

How should the JPA layer be designed to cater for this scenario ?

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

If I create an object with attributes:

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

我可以将 SearchObject 传递给 find 方法，Spring JPA 将根据哪些属性为 Null 来确定要为哪些属性插入 AND 语句 - 如果该属性不为 null，则为该属性生成相应的 AND属性.

Can I pass SearchObject into a a find method and Spring JPA will determine which attributes to insert AND statements for depending on which attributes are Null - if the attribute is not null a corresponding AND is generated for that attribute.

推荐答案

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

@AllArgsConstructor公共类 TicketFilter {

@AllArgsConstructor public class 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);
}

}

1. 使用 JpaSpecificationExecutor
扩展您的 Respoitory

2. 创建规范类:

1. Extend your Respoitory with JpaSpecificationExecutor

2. Create specification class:

公共类 TicketSpecification 实现规范{

public class TicketSpecification implements Specification {

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]));
}

private Predicate getCol1Predicate(Root root, String title) {返回 root.get("col1").in(col1);}

private Predicate getCol1Predicate(Root root, String title) { return root.get("col1").in(col1); }

}

使用你的仓库:ticketRepository.findAll(specification);

这篇关于将 JPA 与多个 AND 运算结合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！