"NOT IN `subquery`" statement with JPA Criteria API
This is how I fixed the problem : I mainly selected the NeedNode.id
in the subquery instead of the full object. This way, the IN
statement works.
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 Join
variable and the subquery to return Long
instead 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.
Comments
-
Unda almost 2 years
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):
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
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 :
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 :
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))
bycb.not(nNode.in(sq))
but it throws the same exception.I probably missed something but I can't find it. Thanks for the help.
-
Gabriel Patricio Bonilla over 5 yearsReally useful the lines:
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));