Spring Data JPA aggregate functions

3125 views java
5

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

answered question

Can you post the stacktrace?

@Aris_Kortex added the stack trace (the missing part is Failed to load ApplicationContext; not relevant for the error I suppose)

2 Answers

13

It seems that the EmptyResultDataAccessException exception is thrown when a result from a query was expected to have at least one row (or element) but none was returned.

Related documentation about this can be found here.

I would suggest to run the same query this attempts to run in order to further validate this theory. Now the good question's what to do with this.

You have two options. Either catch the EmptyResultDataAccessException exception at your calling point and handle it directly in there or alternatively you can have an ExceptionHandler which will be tasked with handling such exceptions.

Both ways of handling this, should be OK and you may choose between each depending on your scenario.

posted this
8

i am not complete sure, but i think the problem it is because of the type of return long, maybe you should use the Long wrapper, long does not allow null because it is a primitive, try to change to

@Query("SELECT AVG(u.age) FROM #{#entityName} u")
Long averageAge();

I hope that this can help you with your problem

Best Regards,

posted this

Have an answer?

JD

Please login first before posting an answer.