JPA Criteria query group by uses only the id

Question

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 :

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:

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.

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 typeJoin in group by clause(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 by then I can use the following statement :

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

score 16 · Answer 1 · answered Jan 02 '15 at 15:31

16

JPA/Hibernate doesn't automatically include all entity properties in a group by clause, so you have to 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();

answered Jan 02 '15 at 15:31

Vlad Mihalcea

142,745
71
566
911

this is not good idea. In this solution any time AccountType changed i most change all of my queries. of course i can handle it by reflection but i think must be there a better solution for it. are you sure hibernate cann't include all entitiy properties? – Rasoul Taheri Jan 02 '15 at 15:52
You can simply specify the accountType.id and use a 2nd level cache to load it afterwards. – Vlad Mihalcea Jan 02 '15 at 16:07
You could of course also use the JPA Metamodel to retrieve all attributes of the entity type and put them into the group by which would be generic. – Christian Beikov Oct 21 '16 at 06:35
@VladMihalcea is that stil the case ? I'm facing this problem, I wonder if there is a workaround now ? – remi bourgarel Mar 04 '20 at 09:24
Most likely this is still the case since this is not really an issue. If anyone wants to add an improvement via a `QueryHint`, then the query could possible to the GROUP BY all columns. – Vlad Mihalcea Mar 04 '20 at 09:30
The solution works greate, but now `createQuery()` method is parametrized. If someone facing with: "Raw use of parameterized class 'CriteriaQuery'" warning the solution is to use POJO to wrap the query result: `CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(AccountAggregationDTO.class);`. The AccountAggregationDTO class must include constructor with three parameters: Long, String, Double, to avoid `Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class`. – Falcon Oct 21 '21 at 10:30

score 6 · Answer 2 · answered Jan 05 '15 at 21:33

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.

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.

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.

JPA Criteria query group by uses only the id

2 Answers2