Default return value for JPA query

Question

I have a JPA query

@Query(value = "SELECT SUM(total_price) FROM ... WHERE ...", nativeQuery = true)

which works as expected when there are matching records. But when there are no matching records, the query returns null.

How can I return zero(0) instead of null when no records are found?

Answer 1

You can change return type to be an Optional;

@Query(value = "SELECT SUM(total_price) FROM ... WHERE ...", nativeQuery = true)
Optional<Integer> getSum(...);

Or you can wrap this getSum() with a default method;

@Query(..)
Integer getSum(...);

default Integer safeGetSum(..) {
    return Optional.ofNullable(getSum(..)).orElse(0);
}

More info on null handling in repositories

---

When the return value is not a list, or some wrapper (plus some others check below), & there are no matching records, the return will be null, so there is no slick way to handle this with some defaultValue=0 through @Query

The absence of a query result is then indicated by returning null. Repository methods returning collections, collection alternatives, wrappers, and streams are guaranteed never to return null but rather the corresponding empty representation.