使用JPA Criteria API的select子句中的子查询 [英] Subquery in select clause with JPA Criteria API

问题描述

我正在尝试像在标题中一样在select子句中插入一个子查询，就像在这个简单的SQL中一样:

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

我已经尝试过了:

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

但我只能得到:

java.lang.IllegalStateException: 子查询不能出现在选择子句中

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

如何获得相似的结果?

How can I get a similar result?

推荐答案

JPA 2.1和Hibernate 5.0支持它.您只需在主查询的multiselect的子查询参数中添加getSelection().

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

看看这个工作示例:

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

这篇关于使用JPA Criteria API的select子句中的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！