Handling null values from database in spring jpa

hibernate, java, jpa, spring

Solution

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.

Problem

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!

Original source