Subquery in select clause with JPA Criteria API

Question

I'm trying, as in title, to insert a subquery in select clause like in this simple SQL:

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

this is obviously only a mock query just to make my point. (The point would be to get the last invoice for each item returned by the query.)

I've 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?

score 30 · Accepted Answer · answered Sep 15 '16 at 15:37

It is supported in JPA 2.1 and Hibernate 5.0. You just had to add getSelection() to the subquery argument in the multiselect of the main query.

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

I believe you have the right answer, but could you write out how NotfiticationInfo and NotificationUser relate and what the SQL statement this accomplishes would be? It doesn't look like: `SELECT id, creationDate, suspendedDate, type, title, description, (select count(case when i don't follow) from notification) from notification` — dlamblin, May 26 '20 at 07:19

score 7 · Answer 2 · answered Jan 12 '11 at 13:53

7

JPA does not support sub-queries in the select clause.

You need to either change your query so as not to use require the sub-query in the select clause, execute multiple queries, or use a native SQL query.

answered Jan 12 '11 at 13:53

James

17,965
11
91
146

score 4 · Answer 3 · answered May 20 '17 at 11:39

4

You need to coalesce your subquery result:

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

answered May 20 '17 at 11:39

cghislai

1,751
15
29

This is a very useful trick. I didn't find it anywhere else. Is this also mentioned in any documentation or tutorial? – hvb May 22 '18 at 12:28

Ondrej Bozek · Answer 4 · 2016-07-12T15:26:25.747

3

JPA now supports sub-queries in the select clause.

EDIT:
JPA 2.1 JPQL BNF supports sub-queries in select clause even if it's not required. As far as I know Eclipselink Supports this and Hibernate too (tested in 5.1).

edited Jul 12 '16 at 15:26

answered Jan 11 '16 at 16:17

Ondrej Bozek

10,987
7
54
70

1

Exactly what version of JPA Specification does the word "now" in your answer mean? – Lukas Risko Feb 23 '16 at 15:49
1

"now" meant exactly the latest version of JPA which was (is) 2.1 – Ondrej Bozek Sep 14 '16 at 15:45

Subquery in select clause with JPA Criteria API

4 Answers4

Linked