Query Group JPA Criteria Criteria Use Identifier Only

Question

This is an example of an object:

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 using Join as follwing:

 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 generates an Sql command, as shown below:

 select accType.id, accType.name, sum(acc.remaining) from account acc join accType on acc.accounttype_id = accType.id group by accType.id 

The above code works in PosgreSQL, but cannot work in Oracle, because in it select accType.name, which does not appear in the group by clause.

update :
I think my question is incomprehensible to you. My question is not about the behavior of PostgreSQL or Oracle in group by . My question is: I use typeJoin in group by (this means that I expect hibernate to use the entire AccountType field in group by ), but why does sleep mode just use the authentication field on group by ? if I use only the identifier field in group by , then I can use the following statement:

 criteriaQuery.groupBy(typeJoin.get(AccountType_.id)) ; 

Answer 1

JPA / Hibernate does not automatically include all entity properties in a group by clause, so you must manually specify them:

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

Answer 2

When using GROUP BY, Oracle requires that each column in the selection list be in GROUP BY.
PostgreSQL is the same, except when grouping by primary key, then it allows you to select any column.

From Oracle docs

In a query containing a GROUP BY clause, the list items can be aggregated functions, GROUP BY clauses, constants, or expressions with one of them.

From PostgreSQL Docs

When GROUP BY is present or any aggregate functions are present, it is not valid for SELECT list expressions to refer to ungrouped columns, except for aggregate functions and for a non-group column it functionally depends on grouped columns, because otherwise it can be more than one possible return value for a non-group column. A functional dependency exists if grouped columns (or a subset of them) are the primary key of a table containing an ungrouped column.