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();