Java 带有 JPA Criteria API 的“NOT IN `subquery`”语句

"NOT IN `subquery`" statement with JPA Criteria API

提问by Unda

I'm using JPA in my Java EE application and the Criteria API to query my database (PostgreSQL). I implemented a tree as a Closure Table ans I'm trying to get the root nodes. Here's my schema (useless fields omitted):

我在我的 Java EE 应用程序和 Criteria API 中使用 JPA 来查询我的数据库 (PostgreSQL)。我实现了一个树作为一个闭包表，我正在尝试获取根节点。这是我的架构（省略了无用的字段）：

NeedsTreev2     :
id              | primary key

NeedNode        :
id              | primary key
needstree_id    | foreign key references needstreev2(id)

NeedLink        :
ancestor_id     | foreign key references neednode(id)
descendant_id   | foreign key references neednode(id)
needstree_id    | foreign key references needstreev2(id)

Here's my JPA mapping

这是我的 JPA 映射

public class NeedsTreev2 {
    @Id
    private Long id;
}

public class NeedNode {
    @Id
    private Long id;
}

public class NeedLink {
    @ManyToOne
    private NeedNode ancestor;
    @ManyToOne
    private NeedNode descendant;
    @ManyToOne
    private NeedsTreev2;
}

The root nodes of a tree are those which are never used as descendants, so here's the SQL query which returns the root nodes of a specified tree :

树的根节点是那些永远不会用作后代的节点，因此这里是返回指定树的根节点的 SQL 查询：

SELECT nNode.* FROM neednode nNode
               INNER JOIN needstreev2 nTree
                       ON nNode.needstree_id = nTree.id

               WHERE nTree.id = ?
                 AND nNode.id NOT IN
                (SELECT nLink.descendant_id FROM needlink nLink
                                            WHERE nLink.ancestor_id != nLink.descendant_id)
               ;

Then I tried to translate it with Criteria :

然后我尝试用 Criteria 翻译它：

public List<NeedNode> getRootsByTree(NeedsTreev2 tree) {
        List<NeedNode> ret;

        CriteriaBuilder cb = this.getEntityManager().getCriteriaBuilder();
        CriteriaQuery<NeedNode> cq = cb.createQuery(NeedNode.class);

        Root<NeedNode> nNode = cq.from(NeedNode.class);

        /* Here we define the subquery */
        Subquery<NeedNode> sq = cq.subquery(NeedNode.class);
        Root<NeedLink> nLink = sq.from(NeedLink.class);
        sq.where(cb.notEqual(nLink.get(NeedLink_.ancestor), nLink.get(NeedLink_.descendant)));
        sq.select(nLink.get(NeedLink_.descendant));
        /* End of subquery */

        Predicate[] p = {
            cb.equal(nNode.get(NeedNode_.needsTree), tree),
            cb.not(cb.in(nNode).value(sq)) /* This is where the problem occurs */
        };

        cq.where(cb.and(p));

        TypedQuery<NeedNode> query = this.getEntityManager().createQuery(cq);
        ret = query.getResultList();

        return (ret);
    }

This code seems logical to me but it throws an exception :

这段代码对我来说似乎合乎逻辑，但它引发了一个异常：

org.eclipse.persistence.exceptions.QueryException
Exception Description: Illegal use of getField() [NEEDNODE.ID] in expression.
Query: ReadAllQuery(referenceClass=NeedNode )

I also tried to replace cb.not(cb.in(nNode).value(sq))by cb.not(nNode.in(sq))but it throws the same exception.

我也尝试替换为cb.not(cb.in(nNode).value(sq))，cb.not(nNode.in(sq))但它引发了相同的异常。

I probably missed something but I can't find it. Thanks for the help.

我可能错过了一些东西，但我找不到。谢谢您的帮助。

采纳答案by Unda

This is how I fixed the problem : I mainly selected the NeedNode.idin the subquery instead of the full object. This way, the INstatement works.

这就是我解决问题的方法：我主要NeedNode.id在子查询中选择了而不是完整的对象。这样，IN语句就起作用了。

public List<NeedNode> getRootsByTree(NeedsTreev2 tree) {
    List<NeedNode> ret;

    CriteriaBuilder cb = this.getEntityManager().getCriteriaBuilder();
    CriteriaQuery<NeedNode> cq = cb.createQuery(NeedNode.class);

    Root<NeedNode> nNode = cq.from(NeedNode.class);

    Subquery<Long> sq = cq.subquery(Long.class);
    Root<NeedLink> nLink = sq.from(NeedLink.class);
    Join<NeedLink, NeedNode> d = nLink.join(NeedLink_.descendant, JoinType.INNER);
    sq.where(cb.notEqual(nLink.get(NeedLink_.ancestor), nLink.get(NeedLink_.descendant)));
    sq.select(d.get(NeedNode_.id));
    sq.distinct(true);

    Predicate[] p = {
        cb.equal(nNode.get(NeedNode_.needsTree), tree),
        nNode.get(NeedNode_.id).in(sq).not()
    };

    cq.where(cb.and(p));

    TypedQuery<NeedNode> query = this.getEntityManager().createQuery(cq);
    ret = query.getResultList();

    return (ret);
}

So the code needs a Joinvariable and the subquery to return Longinstead of NeedNode. It works that way though I don't understand why it doesn't work as it's written in the question. IMO, making the subquery to select ids makes the Criteria query loose a little bit of its "Type-safe" feature.

所以代码需要一个Join变量和子查询来返回Long而不是NeedNode. 虽然我不明白为什么它不能像问题中所写的那样工作，但它是这样工作的。IMO，使子查询选择 id 使得 Criteria 查询失去了它的“类型安全”功能。