How to translate the MySql query that has a sum for Hibernate?

Hibernate can run Native SQL.

Here's an example:

String sql = "select sum(TOTAL_MERCADORIA_BRL) from PROCESSO group by PERIODO like 'DEZ-15'";
SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
BigDecimal result = query.uniqueResult();

Now the same example using HQL (Hibernate Query Language).

String hql= "select sum(bean.totalMercadoria) from Processo bean where bean.periodo = 'DEZ-15' group by bean.periodo";
Query query = sessionFactory.getCurrentSession().createQuery(hql);
BigDecimal result = query.uniqueResult();

Processo in HQL is the name of the Mapped Entity and bean is the alias I created, but you can put whatever name you want. The bean.periodo references the attribute name of the mapped entity, I took into account that PERIODO is of type varchar now if it is of type Date you can do this way

Query q = sessionFactory.getCurrentSession().createQuery("select sum(bean.totalMercadoria) from Processo bean where bean.periodo = :data group by bean");
q.setParameter("data", variavelData, TemporalType.DATE);

The object sessionFactory is of type SessionFactory .

You are using HQL if you are only using Hibernate, and JPQL if you are implementing JPA with any other tool such as Hibernate, Eclipse Link and OpenJPA.

SELECT SUM(p.totalMercadoriaBrl) FROM Processo p WHERE p.periodo LIKE :periodo GROUP BY p.periodo

The reference :periodo is for the parameter that is passing

The process is equivalent to class Processo , and its attributes periodo and totalMercadoriaBrl

• Follows the documentation comparing JPQL with HQL

link

• JPQL Documentation

link