使用 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:select子句中不能出现子查询

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

我怎样才能得到类似的结果?

How can I get a similar result?

推荐答案

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

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屋！