Hibernate Aggregate Functions

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:

@Entity
public class Student {

    @Id
    @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;

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

        Student jonas = new Student("Jonas", 22);
        session.save(jonas);

        Student sally = new Student("Sally", 20);
        session.save(sally);

        Student simon = new Student("Simon", 25);
        session.save(simon);

        Student raven = new Student("Raven", 21);
        session.save(raven);

        Student sam = new Student("Sam", 23);
        session.save(sam);
    }
}

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:

@Test
public void whenMinAge_ThenReturnValue() {
    int minAge = (int) session.createQuery("SELECT min(age) from Student")
      .getSingleResult();
    assertThat(minAge).isEqualTo(20);
}

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:

@Test
public void whenMaxAge_ThenReturnValue() {
    int maxAge = (int) session.createQuery("SELECT max(age) from Student")
      .getSingleResult();
    assertThat(maxAge).isEqualTo(25);
}

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:

@Test
public void whenSumOfAllAges_ThenReturnValue() {
    Long sumOfAllAges = (Long) session.createQuery("SELECT sum(age) from Student")
      .getSingleResult();
    assertThat(sumOfAllAges).isEqualTo(111);
}

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:

@Test
public void whenAverageAge_ThenReturnValue() {
    Double avgAge = (Double) session.createQuery("SELECT avg(age) from Student")
      .getSingleResult();
    assertThat(avgAge).isEqualTo(22.2);
}

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:

@Test
public void whenCountAll_ThenReturnValue() {
    Long totalStudents = (Long) session.createQuery("SELECT count(*) from Student")
      .getSingleResult();
    assertThat(totalStudents).isEqualTo(5);
}

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.