Handling null values from database in spring jpa

Question

This works fine:

@Repository
public interface VoteDao extends CrudRepository <Vote, Long> {

@Query(value = "select sum(points) from votes where siteuserid= ?1", nativeQuery = true)
int countBySiteUser(@Param("user") SiteUser user);
}

Except in case when there are no votes yet that the result is NULL and the problem is that I do not know how to handle that case of checking when it is Null since the query does no return anything when I ask...

    System.out.println("!!!!: PROPOSAL VoteService: " + voteDao.countBySiteUser(user));

Should it print a Null value for that sysout? The DAO is supposed to answer with a NULL value, but it is not. I would be able to handle that NULL if provided, but it does not.

Thanks in advance for your help!

Answer 1

Use COALESCE to handle null as 0, which correspond to what you actually mean.

@Query(
    value = "SELECT COALESCE(SUM(points), 0) FROM votes WHERE siteuserid = ?1",
    nativeQuery = true)
int countBySiteUser(@Param("user") SiteUser user);

... or another solution with a programmatic approach:

// Integer instead of int to add the "null" handling
@Query(
    value = "SELECT SUM(points) FROM votes WHERE siteuserid = ?1",
    nativeQuery = true)
Integer countBySiteUser(@Param("user") SiteUser user);

Usage:

Integer count = voteDao.countBySiteUser(user);

if (count == null) {
    count = 0;
}

System.out.println("!!!!: PROPOSAL VoteService: " + count);

The COALESCE solution seems better to me. But as @EJP said, it will depend on your needs.