1. Overview

Hibernate aggregate functions calculate the final result using the property values of all objects satisfying the given query criteria.

Hibernate Query Language (HQL) supports various aggregate functions – min(), max(), sum(), avg(), and count() in the SELECT statement. Just like any other SQL keyword, usage of these functions is case-insensitive.

In this quick tutorial, we’ll explore how to use them. Please note that in the examples below we use either the primitive or wrapper types to store the result of aggregate functions. HQL supports both, so it’s a matter of choosing which one to use.

2. Initial Setup

Let’s start by defining a Student entity:

public class Student {

    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private long studentId;

    private String name;

    private int age;

    // constructor, getters and setters

And populating our database with some students:

public class AggregateFunctionsIntegrationTest {

    private static Session session;
    private static Transaction transaction;

    public static final void setup() throws HibernateException, IOException {
        session = HibernateUtil.getSessionFactory()
        transaction = session.beginTransaction();

        Student jonas = new Student("Jonas", 22);

        Student sally = new Student("Sally", 20);

        Student simon = new Student("Simon", 25);

        Student raven = new Student("Raven", 21);

        Student sam = new Student("Sam", 23);

Note that our studentId field has been populated using the SEQUENCE generation strategy.

We can learn more about this in our tutorial on Hibernate Identifier Generation Strategies.

3. min()

Now, suppose we want to find the minimum age among all the students stored in our Student table. We can easily do it by using the min() function:

public void whenMinAge_ThenReturnValue() {
    int minAge = (int) session.createQuery("SELECT min(age) from Student")

The getSingleResult() method returns an Object type. So, we have downcasted the output to an int.

4. max()

Similar to the min() function, we have a max() function:

public void whenMaxAge_ThenReturnValue() {
    int maxAge = (int) session.createQuery("SELECT max(age) from Student")

Here again, the result is downcasted to an int type.

The min() and max() functions’ return type depends on the field in the context. For us, it’s returning an integer, as the Student’s age is an int type attribute.

5. sum()

We can use the sum() function to find the sum of all ages:

public void whenSumOfAllAges_ThenReturnValue() {
    Long sumOfAllAges = (Long) session.createQuery("SELECT sum(age) from Student")

Depending on the field’s data type, the sum() function returns either a Long or a Double.

6. avg()

Similarly, we can use the avg() function to find the average age:

public void whenAverageAge_ThenReturnValue() {
    Double avgAge = (Double) session.createQuery("SELECT avg(age) from Student")

The avg() function always returns a Double value.

7. count()

As in the native SQL, HQL also provides a count() function. Let’s find the number of records in our Student table:

public void whenCountAll_ThenReturnValue() {
    Long totalStudents = (Long) session.createQuery("SELECT count(*) from Student")

The count() function returns a Long type.

We can use any of the available variations of the count() function – count(*), count(…), count(distinct …), or count(all …). Each one of them is semantically equivalent to its native SQL counterpart.

8. Conclusion

In this tutorial, we briefly covered the types of aggregate functions available in Hibernate. Hibernate aggregate functions are similar to those available in plain-old SQL.

As usual, the complete source code is available on GitHub.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

res – Persistence (eBook) (cat=Persistence)
Comments are open for 30 days after publishing a post. For any issues past this date, use the Contact form on the site.