How to use ISNULL in jpa

Question

I have a table test1 having a column amount.I want to to retrieve the summation of all the columns.So I am using SUM function like this

SELECT SUM(cda.amount)FROM test cda

If the table is empty then it gives me null

The equivalent JPA code is as follows

If there is n

public Double sumAmount()
    {
        Query query=entityManagerUtil.getQuery("SELECT SUM(cda.amount)FROM test cda");

        Object result=query.getSingleResult();
        return (Double)result;
    }

Now In my controller I am adding this amount to principal;

Double total=prinicipal+daoImpl.sumAmount();

As sumAmount() returns null so I am getting NULLPOINTEREXCEPTION while adding here prinicipal+daoImpl.sumAmount();

So I am thinking to return 0 if the amount is null so tried ISNULL and IFNULL both like this

SELECT ISNULL(SUM(cda.amount),0) FROM test cda

but here it gives me the following error

No data type for node: org.hibernate.hql.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'ISNULL' {originalText=ISNULL}

So can anybody please tell me how to use properly ISNULL in JPA

Answer 1

The JPQL equivalent to the ISNULL or NVL commands is the COALESCE command. So you can use SELECT COALESCE(SUM(cda.amount),0) FROM test cda.