Subquery in select clause with JPA API

Question

I am trying, as in the header, to insert a subquery into the select clause, as in this simple SQL:

SELECT id, name, (select count(*) from item) from item 

this is obviously just a mock request to make my point. (The point is to get the last count for each item returned by the request.)

I tried this:

 CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Tuple> c = cb.createTupleQuery(); Root<Item> item= c.from(Item.class); Subquery<Long> scount = c.subquery(Long.class); Root<Item> sarticolo = scount.from(Item.class); scount.select(cb.count(sitem)); c.multiselect(item.get("id"),item.get("nome"), scount); Query q = em.createQuery(c); q.setMaxResults(100); List<Tuple> result = q.getResultList(); for(Tuple t: result){ System.out.println(t.get(0) + ", " + t.get(1) + ", " + t.get(2)); } 

but I only get:

java.lang.IllegalStateException: Subquery cannot occur in select clause

How can I get a similar result?

Answer 1

Supported in JPA 2.1 and Hibernate 5.0. You just need to add getSelection() to the subquery argument in the multiselect main request.

 c.multiselect(item.get("id"),item.get("nome"), scount.getSelection()); 

Take a look at this working example:

 CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<NotificationInfo> cq = builder.createQuery(NotificationInfo.class); //wrapper class Root<Notification> n = cq.from(Notification.class); //root entity //Subquery Subquery<Long> sqSent = cq.subquery(Long.class); Root<NotificationUser> sqSentNU = sqSent.from(NotificationUser.class); sqSent.select(builder.count(sqSentNU)); sqSent.where( builder.equal(sqSentNU.get(NotificationUser_.notification), n), //join subquery with main query builder.isNotNull(sqSentNU.get(NotificationUser_.sendDate)) ); cq.select( builder.construct( NotificationInfo.class, n.get(Notification_.idNotification), n.get(Notification_.creationDate), n.get(Notification_.suspendedDate), n.get(Notification_.type), n.get(Notification_.title), n.get(Notification_.description), sqSent.getSelection() ) ); em.createQuery(cq).getResultList(); 

Answer 2

JPA does not support subqueries in the select clause.

You need to either change your query so that it is not used, but require a subquery in the select clause, execute several queries, or use your own SQL query.

Answer 3

JPA now supports subqueries in the select clause.

EDIT:
JPA 2.1 JPN BNF supports subqueries in the select clause, even if it is not required. As far as I know, Eclipselink supports this and Hibernate too (tested in 5.1).

Answer 4

You need to combine the result of your subquery:

 Expression<ResultType> expression = criterioaBuilder.coalesce(subquery, criteriaBuilder.literal((ResultType) defaultResult); query.select(expression);