如何在JPA中使用ISNULL

问题描述

我有一个表test1的列数，我想检索所有列的总和，所以我正在使用像这样的SUM函数

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

如果表为空，则为空

等效的JPA代码如下

如果有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();

由于sumAmount()返回null，所以在此处添加prinicipal+daoImpl.sumAmount();

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

所以我想如果数量为null则返回0，所以尝试ISNULL和IFNULL都这样

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}

所以任何人都可以告诉我如何在JPA中正确使用ISNULL

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

推荐答案

与 ISNUL 或 NVL 命令等效的是 coalesce 命令.因此您可以使用SELECT coalesce(SUM(cda.amount),0) FROM test cda

The equivalent to the ISNUL or NVL command in is the coalesce command.so you can use SELECT coalesce(SUM(cda.amount),0) FROM test cda

这篇关于如何在JPA中使用ISNULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！