Hibernate criteria count over with group by

Hibernate ORM
1

First of all, hello to everybody, I’m more than happy to ask my first question here.

I have a spring app with the user entity and the users table. I would like to get a number of all users grouped by certain fields (not per group but in total ). In sql It would be:

select
count(*) OVER () as totalRecords
  from users u
  group by
    u.first_name,
    u.last_name,
    u.age
  order by u.age DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

But I really can’t do that using hibernate criteria. I could do something like:

public Long getTotalCount() {
        ProjectionList groupBy = projectionList();
        groupBy.add(groupProperty("firstName"), "first_name");
        groupBy.add(groupProperty("last_name"), "last_name");
        groupBy.add(groupProperty("age"), "age");
        groupBy.add(Projections.rowCount());

        return (Long) getSession().createCriteria("User")
                .setProjection(groupBy)
                .uniqueResult();
    }

but it’s not what I want. It does counting per each group, I would like to count rows that are the result of the group by clause

2

Hibernate Criteria does not support window functions. Check out Blaze Persistence which adds this to the JPA Criteria.

@beikov can tell you more since he’s the project author.

However, your SQL query is wrong anyway since you are counting without projecting the group, hence yo h won’t be able to know what group a count is associated with.

3

If you want to count the number of groups, you would need a query like this:

select count(distinct u.first_name, u.last_name, u.age)
from users u

Now this can be done with Hibernate directly as well, but the support for tuple count distinct is not emulated yet. If you want support for that for e.g. SQL Server you’d need to use the COUNT_TUPLE function provided by Blaze-Persistence

In JPQL/HQL the use of the function would look like FUNCTION('COUNT_TUPLE', 'DISTINCT', u.firstName, u.lastName, u.age). You can use the JPA Criteria API for that purpose as well if you like, but I don’t think you can formulate this expression with the Hibernate Criteria API.

PostgreSQL is one of the few databases that supports this out of the box, so you wouldn’t need Blaze-Persistence for that purpose.

4

I am using oracle db.
To summarize my options (correct me if I’m wrong) I can:

  1. Make it as a subquery (in native sql only, as hibernate criteria API does not allow to use subselect in the from clause):
select count(*) as totalNumber
from (select distinct u.first_name, u.last_name, u.age from users u)
  1. Using blaze persistence:
CriteriaBuilder<String> cb = cbf.create(em, Long.class)
    .from(User.class, "user")
    .select("FUNCTION('COUNT_TUPLE', 'DISTINCT', u.firstName, u.lastName, u.age)");
  1. Using my first appoach count(*) over() (hibernate with native sql):
select
count(*) OVER () as totalRecords
  from users u
  group by
    u.first_name,
    u.last_name,
    u.age
  order by u.age DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
  1. I am not able to accomplish that using native hibernate criteria API without help from additional libraries, like blaze (considering I am using hibernate criteria and oracle db) - but can be done with JPA Criteria

Am I right ?

5

Hibernate legacy Criteria is also deprecated, so you should use the JPA Criteria API instead.

If you’re doing the projection of the group by columns, you will be able to do it with JPA Criteria API. Otherwise, you will need a native SQL if you want to use derived tables or window functions.

6

I am real newbie when it comes to jpa criteria. Would you be so kind as to show me a simple example I could use in my case ?

7

Check out the User guide. It’s the best way to get to know how to write your query with Criteria API.