如何通过条件api过滤实体和@OneToMany关系

Question

下面的示例过滤器"实体2"；根据给定的标准。但是，@OneToMany关系的列表仍然未过滤(所有实体都包括在内，无论它们是否与过滤器匹配(。如何展开过滤器，以便这些过滤器也被过滤？在该示例中，entity2.entities 1可以仅包括Entity1；值"；大于或等于给定值。

(postgres(数据库结构：

table1
-------------------
id character
id_table2 character
value integer
table2                                      
--------------                              
id character                                
name character                              
deletedat timestamp without time zone

实体模型：

@Entity
@Table(name = "table1")
public class Entity1 {
@Id
private String id;
@Column(name = "value")
private Integer value;
// ...
}
@Entity
@Table(name = "table2")
@Where(clause = "deletedat is null")
public class Entity2 {
@Id
private String id;
@Column(name = "name")
private String name;
@OneToMany
@JoinColumn(name = "id_table1", updatable = false)
private List<Entity1> entities1;
// ...
}

调用和规格：

public Page<Entity2> findByFilter(SomeFilter filter) {
Pageable pageable = //...
return entity2Repository.findAll(createSpecification(filter), pageable);
}
private Specification<Entity2> createSpecification(SomeFilter filter) {
return (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
predicates.add(criteriaBuilder.equal(root.get("name"), filter.getName()));
root.join("entities1").on(criteriaBuilder.greaterThanOrEqualTo(join.get("value"), filter.getMinValue()));
query.distinct(true);
return criteriaBuilder.and(predicates.stream().toArray(Predicate[]::new));
};
}

我已经厌倦了这个问题的解决方案，但没有成功：

• JPA条件Join OneToMany表中子句不起作用
• JPA标准生成器一对多限制
• https://stackoverflow.com/a/48441456/3615209

Answer 1

首先，最好在两个类中定义关系。

因此，从Entity1到Entity2存在@ManyToOne关系，并且从Entity2到Entity1存在@OneToMany关系。

@Entity
@Table(name = "table1")
public class Entity1 {
@Id
private String id;
@Column(name = "value")
private Integer value;
@ManyToOne
@JoinColumn(name = "id_table2", referencedColumnName = "id", updatable = false)
private Entity2 entity2;
// ...
}
@Entity
@Table(name = "table2")
@Where(clause = "deletedat is null")
public class Entity2 {
@Id
private String id;
@Column(name = "name")
private String name;
@OneToMany(mappedBy = "entity2")
private List<Entity1> entities1;
// ...
}

最后，您必须返回实体1的列表，因此您可以反转选择，然后从实体1开始。通过这种方式，您可以使用fetch来避免额外的查询，并且可以使用get进行导航。

private Specification<Entity1> createSpecification(SomeFilter filter) {
return (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
root.fetch("entity2");
predicates.add(criteriaBuilder.equal(root.get("entity2").get("name"), 
filter.getName()));
predicates.add(criteriaBuilder.greaterThanOrEqualTo(
root.get("value"), 
filter.getMinValue()));
query.distinct(true);
return criteriaBuilder.and(predicates.stream().toArray(Predicate[]::new));
};
}