Subquery in the where section with the Query criterion

Question

Can someone give me some tips on how to put such a subquery in CriteriaQuery ? (I am 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 choice will always have one result or zero.

Answer 1

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(); 

Please note that the code has not been tested due to the absence near the IDE.

Answer 2

You can use DetachedCriteria to represent a subquery. Your code should look something like this:

 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);