Linked Questions

Popular Questions

Spring Data JPA aggregate functions on an empty resultset

Asked by At

I am working on a project involving Spring and JPA/Hibernate. The database driver used in my development environment is H2. My application has a page that displays statistics, one example of such a statistic is the average age of my users. However, when I try to fetch the average age using JPQL, I receive an exception

Result must not be null!

Assume for simplicity reasons that I store age as an integer on every User object (in my application this is of course not the case, but that's not important for my problem).

User model

@Entity
public class User implements Identifiable<Long> {
    private int age;
    // more fields and methods, irrelevant
}

User repository

@Repository
public interface UserRepository extends CrudRepository<User, Long> {
    @Query("SELECT AVG(u.age) FROM #{#entityName} u")
    long averageAge();
}

I cannot seem to figure out why calling UserRepository#averageAge(); is throwing the exception. I have tried replacing the function AVG in the query by COUNT and this behaves as expected. I have also tried to use an SQL query and setting nativeQuery = true in the annotation, yet to no avail. I can ofcourse solve it by fetching all the users and calculate the average age in plain Java, but this wouldn't be very efficient.

Stacktrace:

Caused by: org.springframework.dao.EmptyResultDataAccessException: Result must not be null!
    at org.springframework.data.repository.core.support.MethodInvocationValidator.invoke(MethodInvocationValidator.java:102)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy150.averageAge(Unknown Source)
    at my.test.application.StatisticsRunner.run(StatisticsRunner.java:72)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:809)
    ... 30 more

Solved

The exception was caused by the fact that AVG() returns null when performed on an empty table. I fixed it by modifying the query (inspired by the answer to this question) as follows:

@Query("SELECT coalesce(AVG(u.age), 0) FROM #{#entityName} u")
long averageAge();

Related Questions