CODEWITHSUNDEEP
jpaspring-data-jpa
Ken
Ken

Reputation: 4927

Default return value for JPA query

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?

Upvotes: 7

Views: 4436

Answers (2)

Ken
Ken

Reputation: 4927

A native SQL option is COALESCE which returns the first non-null expression in the arg list

SELECT COALESCE(SUM(total_price),0) FROM ...

Upvotes: 5

buræquete
buræquete

Reputation: 14698

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.

Upvotes: 11

Related Questions