JPA: TypedQuery sometimes returns null instead of NoResultException

Question

I usually work with NoResultException to return an "empty" object, for example. an empty error list or the new BigInteger ("0") if I don't get results from TypedQuery. Now it turned out that this sometimes does not work. Suddenly getSingleResult () returns null instead of calling NoResultException, and I don't understand why. Check out this example:

public BigInteger pointsSumByAccountId(long accountId) { try { TypedQuery<BigInteger> pointsQuery = entityManager.createNamedQuery(Points.SumByAccountId, BigInteger.class); pointsQuery.setParameter(Points.AccountIdParameter, accountId); return pointsQuery.getSingleResult(); } catch (NoResultException e) { return new BigInteger("0"); } } 

An important part of the facility ...

 @NamedQueries({@NamedQuery(name = "Points.sumByAccountId", query = "select sum(p.value) from Points p where p.validFrom <= current_timestamp() and p.validThru >= current_timestamp() and p.account.id = :accountId")}) public class Points { private static final long serialVersionUID = -15545239875670390L; public static final String SumByAccountId = Points.class.getSimpleName() + ".sumByAccountId"; public static final String AccountIdParameter = "accountId"; . . . 

If I use accountId, which does not cause any results, I get null instead of NoResultException. Any idea why this is? Even the Javadoc TypedQuery says that it should return a NoResultException:

 /** * Execute a SELECT query that returns a single result. * * @return the result * * @throws NoResultException if there is no result * @throws NonUniqueResultException if more than one result * @throws IllegalStateException if called for a Java * Persistence query language UPDATE or DELETE statement * @throws QueryTimeoutException if the query execution exceeds * the query timeout value set and only the statement is * rolled back * @throws TransactionRequiredException if a lock mode has * been set and there is no transaction * @throws PessimisticLockException if pessimistic locking * fails and the transaction is rolled back * @throws LockTimeoutException if pessimistic locking * fails and only the statement is rolled back * @throws PersistenceException if the query execution exceeds * the query timeout value set and the transaction * is rolled back */ X getSingleResult(); 

Answer 1

This seems like the right behavior for me.

NoResultException is NoResultException when no rows are returned, but sum returns exactly one row with a null value in your case. From the JPA 2.0 specification:

If SUM, AVG, MAX or MIN is used, and there are no values ​​that can be associated with the aggregate function, the result of the total NULL function is applied.

If you want to get 0 instead of null , use coalesce :

 select coalesce(sum(p.value), 0) ...