7

Can anybody give me some hints on how to put that kind of subquery in a CriteriaQuery? (I'm using JPA 2.0 - Hibernate 4.x)

SELECT a, b, c FROM tableA WHERE a = (SELECT d FROM tableB WHERE tableB.id = 3) - the second select will always get a single result or null.

artaxerxe
  • 6,281
  • 21
  • 68
  • 106

2 Answers2

14

Try something like the following example to create a subquery:

CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
Root tableA = cq.from(TableA.class);

Subquery<String> sq = cq.subquery(TableB.class);
Root tableB = cq.from(TableB.class);
sq.select(tableB.get("d"));
sq.where(cb.equal(tableB.get("id"), 3));

cq.multiselect(
    cb.get("a"),
    cb.get("b"),
    cb.get("c"));
cq.where(cb.equal(tableA.get("a"), sq));
List<Object[]> = em.createQuery(cq).getResultList();

Note the code has not been tested due to the lack of an IDE nearby.

Menno
  • 12,175
  • 14
  • 56
  • 88
  • I think that I specified that my where clause will always return one result. – artaxerxe May 24 '13 at 11:18
  • Edited the question. If you need some other details, ask me please. Thanks. – artaxerxe May 24 '13 at 11:20
  • What do you expect the query to do in case of a `null`-case? – Menno May 24 '13 at 11:21
  • Return `null` (there is no null value for `a` field). – artaxerxe May 24 '13 at 11:22
  • @artaxerxe Edited accordingly to the given example. – Menno May 24 '13 at 11:33
  • 3
    Don't you have to write `Root tableB = sq.from(TableB.class)` instead of `Root tableB = cq.from(TableB.class)` [line 5, `sq` instead of `cq`] ? – Unda Jun 11 '14 at 15:36
  • That's Great @ Aquillo – Lova Chittumuri May 18 '17 at 08:49
  • Subquery sq = cq.subquery(TableB.class) fails with a type error. "Incompatible types. Found: 'javax.persistence.criteria.Subquery', required: 'javax.persistence.criteria.Subquery'". I think that line should read Subquery sq = cq.subquery(String). Then it all should compile and work (if you also use the correction from Unda above). – jadc Jun 04 '23 at 05:57
0

You can use DetachedCriteria to represend the sub-query. Your code should look something like:

DetachedCriteria subCriteria = DetachedCriteria.forClass(TableB.class);
subCriteria.add(Property.forName("id").eq(3)); //WHERE tableB.id = 3
subCriteria.setProjection(Projections.property("d")); // SELECT d from

DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
criteria.add(Property.forName("a").eq(subCriteria)); //a = (sub-query)
criteria.setProjection(Projections.property("a"); //SELECT a
criteria.setProjection(Projections.property("b"); //SELECT b
criteria.setProjection(Projections.property("c"); //SELECT c
return getHibernateTemplate().findByCriteria(criteria);
Konstantin Yovkov
  • 62,134
  • 8
  • 100
  • 147
  • Is your code *JPA* compliant? What does `getHibernateTemplate()` mean? – artaxerxe May 24 '13 at 11:28
  • Yes, is JPA comliant. getHibernateTemplate() works as a wrapper over hibernate session. This takes care of opening/closing session (all BolierPlate code) as well as it is thread safe. It also exposes methods of session and query criteria like save, update, etc. – Konstantin Yovkov May 24 '13 at 11:30
  • 1
    Ok. If I change the *JPA* implementation I think I won't be able to run that code (as long as it is specific to hibernate). I would like to have a *JPA* based solution. Anyway, your solution responds to my needs I think when you rely on *Hibernate*. Thanks. – artaxerxe May 24 '13 at 11:35