JPQL“不属于"使用条件API查询 [英] JPQL "NOT MEMBER OF" query using criteria API

问题描述

给出以下带有JPA注释的实体类:

Given the following JPA annotated entity classes:

@Entity
@Table("foo")
public class Foo {
    @Id private int id;
    @Column(name="name") private String name;
    @ManyToMany
    @JoinTable(name = "foo_tags",
           joinColumns = {@JoinColumn(name = "foo")},
           inverseJoinColumns = {@JoinColumn(name = "tag")})
    private Collection<Tag> tags;
    ...
}

@Entity
@Table(name = "tag")
public class Tag {
    @Id private String tag;
    ...
}

我正在尝试制定查询以获取所有缺少给定标签的Foo实例.以下JPQL查询可以解决问题

I'm trying to formulate a query to get all Foo instances that lack a given tag. The following JPQL query does the trick

SELECT f FROM Foo f WHERE :tag NOT MEMBER OF f.tags

但是，我在将其转换为条件查询时遇到了麻烦.翻译对我来说似乎很明显:

However, I'm having trouble translating this into a criteria query. The translation seems obvious (to me):

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Foo> query = cb.createQuery(Foo.class);
Root<Foo> from = query.from(Foo.class);
query.where(cb.isNotMember(cb.parameter(Tag.class, "tag"), from.get(Foo_.tags)));
TypedQuery<Foo> tq = em.createQuery(query);
return tq.setParameter("tag", sometag).getResultList();

在这些情况下，生成的SQL明显不同.第一个查询生成以下内容:

The generated SQL differs significantly in these cases though. The first query generates the following:

SELECT t0.id, t0.name FROM foo t0
WHERE NOT EXISTS (
    SELECT DISTINCT t2.TAG FROM tag t2, foo_tags t1
    WHERE (((t1.foo = t0.id) AND (t2.TAG = t1.tag)) AND ('blue' = t2.TAG)))

条件查询生成以下内容:

while the criteria query generates this:

SELECT t1.id, t1.name FROM tag t0, foo_tags t2, Foo t1
WHERE (NOT ((t0.TAG = 'blue')) AND ((t2.foo = t1.id) AND (t0.TAG = t2.tag)))

我仅使用eclipselink实现进行了测试，因此可能存在问题，但我想首先要在这里问是否有人发现了明显的错误.

I've only tested this using the eclipselink implementation, so possibly there's a problem there, but figured I'd ask here first if anyone spots an obvious mistake.

推荐答案

SQL应该是相同的，尽管两者看起来都可以工作(减去空的情况).

The SQL should be the same, although both seem like they would work (minus the empty case).

请在EclipseLink中记录有关标准问题的错误.

Please log a bug for the criteria issue in EclipseLink.

您应该能够使用联接或子选择，而不是语法的特殊成员.

You should be able to use a join or a sub select instead of the special member of syntax.

SELECT f FROM Foo f left join f.tags t WHERE not(t == :tag)

这篇关于JPQL“不属于"使用条件API查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！