How to perform a SUM in Hibernate

Question

How to perform a SUM in Hibernate

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I'm trying to do a select via HQL but Java can not identify SUM by 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 documentation in Hibernate as follows the link Hibernate says that the function is interpreted by Hibernate and its operation is normal.

But I do not know what might be happening.

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.

sql java query hibernate hql

asked by anonymous 19.02.2016 / 23:02

2 answers

2

Well, let's first address 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 set to receive the query parameters. One 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 package will be the package that will contain its ExemploQuery class.

Still on your question, it is not recommended to name capitalized methods. And, I believe, your finally can not be reached if your method can reach return of it. Maybe store the result of your query in a list and then make em.close , so returning the value stored in your list is the best option.

22.02.2016 / 03:37

Transactional control in context of exception handling using Demoiselle I do not know the feasibility of doing in lambda or linq my expression

score 0 · Accepted Answer

To do a SUM on Hibernate I did it as follows.

queryDAO.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();

    }

}

detailProduct.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];
                }
    ....