How to translate the Mysql query that has a sum to Hibernate?

Question

I need to translate the following query from Mysql to Hibernate:

select sum(TOTAL_MERCADORIA_BRL) from PROCESSO group by PERIODO like 'DEZ-15';

What would be the best way to do this query, but using Hibernate?

Answer 1

It is using HQL if you are only with Hibernate, and JPQL if you are with JPA implementing with any other tool like 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 Procedure is equivalent to class Processo, and its attributes periodo and totalMercadoriaBrl

• Follow the documentation by comparing JPQL with HQL

https://docs.jboss.org/hibernate/orm/4.3/devguide/en-US/html/ch11.html

• Documentation relating to JPQL

https://docs.oracle.com/html/E13946_04/ejb3_langref.html

Answer 2

Hibernate can run Native SQL.

Follow 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 the HQL is the name of the Entity mapped and the bean is the alias I created, but you can put the name you want. The bean.periodo refers to the name of the attribute of the mapped entity, taking into account that the PERIODO is like varchar now if it’s like Date you can do it 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 the type Sessionfactory.