JPA subquery generates ID twices in SQL query

Question

I have a problem doing a composite select using JPA CriteriaQuery . I have three tables X->Y->Z , for every X I have one or more Y and for every Y I have one or more Z. I have an X object and I am trying to select all the occurrences of Z for one X .

The code I am using:

  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Z> criteriaQuery = cb
          .createQuery(Z.class);      
  Root<Z> fromZ = criteriaQuery.from(Z.class);

  X xObj = new X(xObjId);

  Subquery<Y> subquery = criteriaQuery.subquery(Y.class);
  Root<Y> fromY = subquery.from(Y.class);
  subquery.select(fromY.<Y> get("Yid")).distinct(true)
          .where(cb.equal(fromY.get("Xid"), xObj));

  criteriaQuery
          .select(fromZ)
          .where(cb.and(
                  cb.in(from.get("Yid")).value(subquery),
                  cb.and(cb.equal(from.get("Zcolumn1"), 0),
                          cb.equal(from.get("Zcolumn2"), 1))))
          .orderBy(cb.asc(from.get("Zcolumn3")));

  TypedQuery<Z> typedQuery = em.createQuery(criteriaQuery);
  List<Z> results = typedQuery.getResultList();

Xid and Yid are the foreign keys in their respective Y and Z beans.

This code generates an error at execution point because I have and invalid SQL :

SELECT t0.Zcolumn1, t0.Zcolumn2, t0.Zcolumn3, FROM Z t0 WHERE (t0.Yid IN ( SELECT t1.Yid.t1.Yid FROM Y t1 WHERE (t1.Xid = ?)) AND ((t0.Zcolumn1 = ?) AND (t0.Zcolumn2 = ?))) ORDER BY t0.Zcolumn3 ASC

I do not understand why the Yid is generated twice by the sub query , and have no idea how to fix it , according to the examples I found and adapted I think this should work. This is my first time working with SubQuery so forgive my for my code style possible stupid errors :)

Thank you.

shouldn't `Subquery subquery = criteriaQuery.subquery(Y.class);` be `Subquery subquery = criteriaQuery.subquery(Yid.class);`? — cosmincalistru, Mar 26 '13 at 17:05
Yid , it's not a class , the notation I used might be a little confusing , Y it's a class and Yid is the foreign key in Z and X , and the primary key in Y. — Nicolae Birlea, Mar 27 '13 at 08:39

perissf · Answer 1 · 2013-03-27T09:15:32.313

I am not sure why you are using subQuery at all.

This much simpler approach will give you all the occurrencies of Z linked to the given X instance. I have slightly changed the field naming: ysCollection and zsCollection are the OneToMany properties in X and Y respectively, like follows:

@Entity
public class X {
    ...
    @OneToMany(mappedBy="xId")
    private List<Y> ysCollection;
    ...
}

If I misunderstood your goal, please let me know:

X xObj;
CriteriaQuery<Z> cq = cb.createQuery(Z.class);
Root<X> root = cq.from(X.class);
Join<X, Y> ys = root.join("ysCollection");
Join<Y, Z> zs= ys.join("zsCollection");
cq.distinct(true);
cq.where(cb.equal(root.get("id"), xObj.getId()));
cq.select(zs);

Or, using MetaModel:

X xObj;
CriteriaQuery<Z> cq = cb.createQuery(Z.class);
Root<X> root = cq.from(X.class);
Join<X, Y> ys = root.join(X_.ysCollection);
Join<Y, Z> zs= ys.join(Y_.zsCollection);
cq.distinct(true);
cq.where(cb.equal(root.get(X_.id), xObj.getId()));
cq.select(zs);

ok , you have to explain to me what are ysCollection and zsCollection. — Nicolae Birlea, Mar 27 '13 at 08:58

JPA subquery generates ID twices in SQL query

1 Answers1