JPA2条件中的From claus中的子查询 [英] Subquery in From claus in JPA2 Criteria

问题描述

我有一个帐户可以有多个用户的数据模型：

I have a data model where an account can have multiple users:

class Account {
    Long id;
}
class User {
    Long id;
    @ManyToOne
    Account account;
}

我想执行以下查询，显示以下用户的数量每个帐户：

I'd like to do the following query which displays the number of users of each account:

select Account.id, NumUsers.num from Account, 
       (select Account.id as account_id, count(User.id) as num 
        from User join Account on User.account_id=Account.id 
        group by Account.id) as NumUsers 
where Account.id=NumUsers.account_id;

我知道我可以将此特定查询重写为：

I know I can re-write this specific query as:

select Account.id, count(User.id) from Account join 
       User on User.account_id=Account.id group by Account.id

但是我计划为需要多个分组依据的报表创建更复杂的查询。我在此处中阅读了有关正确的多重分组方法。

But I plan to create more complicated queries for reports that require more than one group by. I read here about the correct approach to multiple group by.

如何使用JPA2 Criteria API创建查询？

How can I create my query using JPA2 Criteria API?

推荐答案

在JPA 2 Criteria API中的FROM子句中使用子查询。它确实具有与JPQL相同的局限性。在JPA 2规范中，关于JPQL中的子查询，说明如下：

You cannot use subquery in FROM clause with JPA 2 Criteria API. It does have same limitations as JPQL does. In JPA 2 specification following is said about subqueries in JPQL:

子查询可以在WHERE或HAVING子句中使用。

Subqueries may be used in the WHERE or HAVING clause.

关于Criteria API中子查询的说法得出了相同的结论：

And what is said about subqueries in Criteria API leads to same conclusion:

在
限制谓词中可以使用相关子查询和不相关子查询。子查询是通过
创建和修改子查询对象来构造的。

Both correlated and non-correlated subqueries can be used in restriction predicates. A subquery is constructed through the creation and modification of a Subquery object.

子查询实例可以作为参数传递给all，any或
CriteriaBuilder接口的一些方法可在条件
表达式中使用。

A Subquery instance can be passed as an argument to the all, any, or some methods of the CriteriaBuilder interface for use in conditional expressions.

可以将子查询实例传递给CriteriaBuilder存在方法
。创建一个条件谓词。

A Subquery instance can be passed to the CriteriaBuilder exists method to create a conditional predicate.

这篇关于JPA2条件中的From claus中的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！