使用JPA CriteriaBuilder生成查询，其中属性在列表中或为空 [英] Using JPA CriteriaBuilder to generate query where attribute is either in a list or is empty

问题描述

我正在尝试使用JPA CriteriaBuilder生成对名为"TestContact"的实体的查询，该实体与另一个名为"SystemGroup"的实体具有多对多联接，该联接的属性称为"groups".该查询的目的是从"TestContact"实体中检索记录，其中"groups"属性位于列表中或为空.

I am trying to use the JPA CriteriaBuilder to generate a query for an entity called "TestContact" that has a many-to-many join with another entity called "SystemGroup" where the attribute for this join called "groups". The objective of the query is to retrieve records from the "TestContact" entity where the "groups" attribute is either in a list or is empty.

我正在使用的代码如下

public List<TestContact> findWithCriteriaQuery(List<SystemGroup> groups) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TestContact> cq = cb.createQuery(TestContact.class);
    Root<TestContact> testContact = cq.from(TestContact.class);
    cq.select(testContact);

    Path<List<SystemGroup>> groupPath = testContact.get("groups");

    // cq.where(groupPath.in(groups));
    // cq.where(cb.isEmpty(groupPath));
    cq.where(cb.or(cb.isEmpty(groupPath), groupPath.in(groups)));

    TypedQuery<TestContact> tq = em.createQuery(cq);

    return tq.getResultList();
}

问题是此查询仅返回组位于列表组"中的结果，但由于某些原因也不会返回组为空的结果(即联接表中没有条目)

The problem is this query only returns results where group is in the list "groups" but for some reason isn't also returning the results where group is empty (i.e. there is no entry in the join table)

如果将where子句更改为cq.where(cb.isEmpty(groupPath));，则查询正确返回组为空的结果.

If I change the where clause to cq.where(cb.isEmpty(groupPath)); then the query correctly returns the results where group is empty.

如果我将where子句更改为cq.where(groupPath.in(groups));，则查询正确返回了该组在组"列表中的结果.

If I change the where clause to cq.where(groupPath.in(groups)); then the query correctly returns the results where the group is in the list "groups".

我不明白的是为什么当我尝试使用CriteriaBuilder或方法将这两个谓词组合在一起时，结果不包括该组在列表中还是空的记录.

What I don't understand is why when I try to combine these two predicates using the CriteriaBuilder or method the results don't include the records where the group is either in the list or is empty.

"TestContact"实体中的groups属性声明如下

The groups attribute in the "TestContact" entity is declared as follows

@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name = "TEST_CONTACT_GROUPS", joinColumns = { @JoinColumn(name = "CONTACT_ID", referencedColumnName = "CONTACT_ID") }, inverseJoinColumns = { @JoinColumn(name = "GROUP_ID", referencedColumnName = "GROUP_ID") })
private List<SystemGroup> groups;

JPA提供程序是EclipseLink 2.5.0，Java EE应用程序服务器是GlassFish 4，数据库是Oracle 11gR2.

The JPA provider is EclipseLink 2.5.0, the Java EE application server is GlassFish 4 and the database is Oracle 11gR2.

任何人都可以指出我要去哪里了吗?

Can anyone please point out where I'm going wrong?

更新

我尝试了@Chris的建议，但是Eclipse在Join<List<SystemGroup>> groupPath = testContact.join("groups", JoinType.LEFT)上返回以下错误

I've tried the suggestion from @Chris but Eclipse is returning the following error on Join<List<SystemGroup>> groupPath = testContact.join("groups", JoinType.LEFT)

Join类型的参数数目不正确；它不可能是 用参数>

Incorrect number of arguments for type Join; it cannot be parameterized with arguments >

在JavaDoc中查看Join时，它说类型参数是...

Looking at the JavaDoc for Join it says the type parameters are...

Z-联接的源类型，X-联接的目标类型

Z - the source type of the join, X - the target type of the join

我尝试了Join<TestContact, SystemGroup> groupPath = testContact.join("groups", JoinType.LEFT);，这随后导致Eclipse在cb.isEmpty上返回以下错误

I've tried Join<TestContact, SystemGroup> groupPath = testContact.join("groups", JoinType.LEFT); which then causes Eclipse to return the following error on cb.isEmpty

边界不匹配:类型的通用方法isEmpty(Expression) CriteriaBuilder不适用于参数 (加入).推断的类型SystemGroup不是 有效替代边界参数>

Bound mismatch: The generic method isEmpty(Expression) of type CriteriaBuilder is not applicable for the arguments (Join). The inferred type SystemGroup is not a valid substitute for the bounded parameter >

推荐答案

testContact.get("groups");子句强制从testContact到组的内部联接，这会过滤掉没有组的testContact.您需要指定一个左外部联接，并在您的isEmpty和in子句中使用它.

The testContact.get("groups"); clause forces an inner join from testContact to groups, which filters out testContacts with no groups. You need to specify a left outer join, and use that in your isEmpty and in clauses.

Root<TestContact> testContact = cq.from(TestContact.class);
cq.select(testContact);

Join<TestContact, SystemGroup> groupPath = testContact.join("groups", JoinType.LEFT);
cq.where(cb.or(cb.isEmpty(testContact.get("groups")), groupPath.in(groups)));

我通常指的是 https://en.wikibooks.org/wiki/Java_Persistence/Criteria#Join 例如

这篇关于使用JPA CriteriaBuilder生成查询，其中属性在列表中或为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！