How to perform a SUM in Hibernate

Question

I am trying to select via HQL but Java cannot identify the SUM generating the following error:

java.lang.NullPointerException
at org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions$SumFunction.determineJdbcTypeCode(StandardAnsiSqlAggregationFunctions.java:213)
at org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions$SumFunction.getReturnType(StandardAnsiSqlAggregationFunctions.java:171)
at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:432)
at org.hibernate.hql.internal.ast.tree.AggregateNode.getDataType(AggregateNode.java:85)
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:172)
at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:924)
at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:692)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:665)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:301)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:249)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:278)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)

But in the Hibernate documentation as follows Hibernate says the function is interpreted by Hibernate and its functioning is normal.

But I don’t know what could be going on.

My code to perform the search is as follows:.

public List RotornoProduto() {

    EntityManager em = getEntityManager();

    try {

        Query q = em.createQuery("select codigo, produto, sum(quantidade) as quantidade, sum(valorTotal) as valorTotal from itens group by codigo");


        return q.getResultList();

    } finally {
        em.close();
    }

}

For information purposes I use Mysql database and the application is Java Desktop.

Answer 1

Well, let’s first go to the possible cause of your problem. I believe your problem lies in the set of attributes you are receiving in your query. You need a class with a constructor defined to receive the query parameters. A way would be:

class ExemploQuery{
    private long codigo;
    private String produto;
    private int quantidade;
    private BigDecimal valorTotal;

    public ExemploQuery(long codigo, String produto, int quantidade, BigDecimal valorTotal) {
        this.codigo = codigo;
        this.produto = produto;
        ...
    }
    //GETTERS AND SETTERS
}

And your query would look like this:

Query q = em.createQuery("select NEW package.ExemploQuery(codigo, produto, sum(quantidade), sum(valorTotal)) from itens group by codigo");

Where the package will be the package that will contain its class ExemploQuery.

Still on your question, it is not indicated to name methods initiated by capital letters. And, I believe, that your finally cannot be achieved if its method achieves the return of the same. Maybe store the result of your query in a list and then perform the em.close, to then return the value stored in your list is the best option.

Answer 2

To accomplish a SUM in the HibernateI did it this way.

query java.

public List retornoMovQtdVendido(int id) {

    EntityManager em = getEntityManager();

    try {

        Query q = em.createNativeQuery("select codigo, nome, sum(quantidade) as quantidade, sum(valor) as valor from itens where codigo =" + id + " group by codigo");

        return q.getResultList();

    } finally {

        em.close();

    }

}

detailsProduct.java

...
 List<produto> RetornaProduto = prod.retornoMovQtdVendido(1);
for (int i = 0; i < RetornaProduto .size(); i++) {

                Object[] item = RetornaProduto.toArray();
                for (Object obj : item) {
                    Object[] x = (Object[]) obj;
                    codigo = (int) x[0];
                    nome = (String) x[1];
                    quantidade = (BigDecimal) x[2];
                    valor = (BigDecimal) x[3];
                }
    ....