Problem with SUM in NamedQuery

0

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  ")

In it I use the SUM aggregation function. I query it 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 that's fine however the list comes in a different format than expected. The return of this list is Array of Arrays and within these Arrays each index corresponds to an item of the object.

Example:

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

Expected would be this:

Array [ Object, Object, Object, Object]

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

    
asked by anonymous 10.03.2016 / 18:44

1 answer

1

You can create a constructor in 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.

Already 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
    
10.03.2016 / 20:31