Java JPA Criteria 查询组仅使用 id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27725770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA Criteria query group by uses only the id
提问by Rasoul Taheri
This is a sample entity:
这是一个示例实体：
public class Account{
@Id
Long id
Double remaining;
@ManyToOne
AccountType type
}
public class AccountType{
@Id
Long id;
String name;
}
Now i create a criteria query with Join as follwing :
现在我创建了一个带有 Join 的条件查询，如下所示：
CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createquery();
Root<Account> accountRoot = criteriaQuery.from(Account.class);
Join<Account, AccountType> typeJoin = accountRoot.join(Account_.type);
criteriaQuery.multiSelect(
typeJoin,
criteriaBuilder.sum(accountRoot.get(Account_.remaining))
);
criteriaQuery.groupBy(typeJoin);
Query query = getEntityManager().createQuery(criteriaQuery);
query.getResultList();
The above code generate Sql command like following:
上面的代码生成Sql命令如下：
select accType.id, accType.name, sum(acc.remaining)
from account acc join accType on acc.accounttype_id = accType.id
group by accType.id
Above code work in PosgreSQL but can't run in Oracle, because in it select accType.name that doesn't appear in the group by clause.
上面的代码在 PosgreSQL 中工作但不能在 Oracle 中运行，因为在其中选择了 accType.name 没有出现在 group by 子句中。
update:
I think my question isn't clear for you. My question isn't about PostgreSQL or Oracle behavior in group by. My question is this :
I use typeJoinin group byclause(this means I expect hibernate use all field of AccountType in group by), but why hibernate just use identity field on group by? if I will use just identity field in group bythen I can use the following statement :
更新：
我想我的问题对你来说不清楚。我的问题不是关于group by. 我的问题是：
我使用typeJoiningroup by子句（这意味着我希望 hibernate 使用 AccountType in 的所有字段group by），但是为什么 hibernate 只使用 identity 字段 on group by？如果我只使用身份字段，group by那么我可以使用以下语句：
criteriaQuery.groupBy(typeJoin.get(AccountType_.id)) ;
回答by Vlad Mihalcea
JPA/Hibernate doesn't automatically include all entity properties in a group by clause, so you have to manually specify them:
JPA/Hibernate 不会自动在 group by 子句中包含所有实体属性，因此您必须手动指定它们：
CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root<Account> accountRoot = criteriaQuery.from(Account.class);
Join<Account, AccountType> typeJoin = accountRoot.join(Account_.type);
criteriaQuery.multiSelect(
typeJoin.get("id"),
typeJoin.get("name"),
criteriaBuilder.sum(accountRoot.get(Account_.remaining))
);
criteriaQuery.groupBy(typeJoin.get("id"), typeJoin.get("name"));
Query query = getEntityManager().createQuery(criteriaQuery);
query.getResultList();
回答by outdev
If using GROUP BY, Oracle requires every column in select list to be in the GROUP BY.
PostgreSQL is the same, except when grouping by the primary key, then it allows you to select any column.
如果使用 GROUP BY，Oracle 要求选择列表中的每一列都在 GROUP BY 中。
PostgreSQL 也是一样，除了按主键分组时，它允许您选择任何列。
From Oracle docs
In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these.
在包含 GROUP BY 子句的查询中，选择列表的元素可以是聚合函数、GROUP BY 表达式、常量或涉及其中之一的表达式。
From PostgreSQL docs
When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.
当存在 GROUP BY 或存在任何聚合函数时，SELECT 列表表达式引用未分组的列是无效的，除非在聚合函数内或当未分组的列在功能上依赖于分组的列时，否则会有更多为未分组的列返回一个可能的值。如果分组列（或其子集）是包含未分组列的表的主键，则存在函数依赖。
