Problem with SUM in Namedquery

Question

I have the following NamedQuery:

@NamedQuery(name = "Controle.listarTotais", query = "SELECT controle.prestador, controle.tipoPrestador, SUM(controle.valorLote), SUM(controle.valorPago), SUM(controle.valorGlosa) FROM Controle controle WHERE controle.competencia.codigo = :competencia GROUP BY controle.prestador ORDER BY controle.prestador  ")

I use the function of aggregation SUM. I consult in this way:

public List<Controle> listarTotais(String competencia) {
        List<Controle> lista = new ArrayList<>();
        Session sessao = HibernateUtil.getSessionFactory().openSession();
        Transaction transacao = null;
        try {
            transacao = sessao.beginTransaction();
            Query consulta = sessao.getNamedQuery("Controle.listarTotais");
            consulta.setString("competencia", competencia);
            lista = consulta.list();
            transacao.commit();
        } catch (RuntimeException ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            sessao.close();
        }
        return lista;
    }

This query returns a list of Controle, so far so good but the list comes in a different format than expected. The return of this list is a Array of Arraysand within these Arrays each index corresponds to an object item.

Example:

Array [ Array[5], Array[5], Array[5], Array[5] ] 

The Expected would be this:

Array [ Object, Object, Object, Object]

When I withdraw the SUM of NamedQuery works as expected. What would be the possible problem and how I can solve it?

Answer 1

You can create a builder on Controle or in a new class that receives the values you are returning.

Ex.:

new Controle(Prestador pre, TipoPrestador tp, Float valorLote, Float valorPago, ... // resto dos atributos.

Ai in namedQuery using select new:

SELECT new Controle ( controle.prestador, controle.tipoPrestador, SUM(controle.valorLote), SUM(controle.valorPago), SUM(controle.valorGlosa) ) FROM Controle controle WHERE controle.competencia.codigo = :competencia GROUP BY controle.prestador ORDER BY controle.prestador