Learn to use the Hibernate aggregate functions that can be used in HQL/SQL SELECT statements to query a calculated value for a field among all entities matching the given criteria.
1. Aggregate Functions
Hibernate Query Language (HQL) supports the following aggregate functions in SELECT statements.
count(), all other functions except numeric values as arguments. The
count() can be used to count any kind of values, including the number of rows in the query result.
- avg(): calculates the average of given numeric arguments.
- count() including count(distinct), count(all), and count(*): count the number of values/rows.
- max(): returns the maximum values among the given numeric arguments.
- min(): returns the minimum values among the given numeric arguments.
- sum(): returns the sum of all the given numeric arguments.
Note that similar to other SQL keywords, these function names are also case-insensitive.
2. Using Aggregate Functions
2.1. Single Aggregate Function
To execute an aggregate function, use it in the HQL query and then use
getSingleResult() method that returns the calculated value by the used aggregate function.
Long emailCount = entityManager .createQuery("select count(distinct e.email) from Employee e", Long.class) .getSingleResult();
2.2. Multiple Aggregate Function
It is also possible to execute more than one aggregate function in a single HQL statement. In this case, the returned value will be an
Object minAndMaxSalaries = entityManager.createQuery( "select " + " min(e.salary), " + " max(e.salary) " + "from Employee e ", Object.class) .getSingleResult();
2.3. Combine with Other Functions
We may use arithmetic operators, concatenation, and other recognized SQL functions in the SELECT clause, along with the listed aggregate functions.
Long maxSalaryWithBonus = entityManager .createQuery("SELECT MAX(e.salary + e.bonus) FROM Employee e WHERE e.bonus > 100000", Long.class) .getSingleResult();
In this tutorial, we learned to use the hibernate aggregate functions in different ways. These functions help in consolidating the returned rows in a single value result.
Happy Learning !!