Select query with SUM java

Ready, in the end the solution was to cast it as an object and pass it to a rest method that will show it as a json.

public Object findBySum(Integer iduser) {
    Query q = getEntityManager().createNativeQuery("select SUM(a.cantidadhoras) FROM asistenciacapacitaciones a JOIN empleados e on e.iduser = a.iduser WHERE e.iduser =   " + iduser)
            .setParameter("iduser", iduser);
    Object res = (Object) q.getSingleResult();
    return res;

}

The json that returns has this look

{"type":"decimal","value":5}

Already in the front was only work with that json object, which is already easy to do.

Thanks for responding equally.

EclipseLink is case sensitive in less recent versions by default unless the eclipselink.jpa.uppercase-column-names persistent property is set to true.

This may be the problem with native queries if the database returns the name of the column in upper case, for example: "ID" when it is defined as a lowercase "id".

Try to change the column definitions in the annotations to match those used by your database or add the property with a value of true, check that your columns have the same name as in the database.

Also so that you use the .setParameter("iduser", iduser); if you are concatenating it in case it should be like this:

public Object findBySum(Integer iduser){
    Query q =  getEntityManager().createNativeQuery("select SUM(a.cantidadhoras) FROM Asistenciacapacitaciones a JOIN Empleados e on e.iduser = a.iduser WHERE e.iduser =   "+ iduser, Asistenciacapacitaciones.class);
            List count = (List) q.getResultList();
    return ((BigInteger)count.get(0)).longValue();

}

or

public Object findBySum(Integer iduser){
    Query q =  getEntityManager().createNativeQuery("select SUM(a.cantidadhoras) FROM Asistenciacapacitaciones a JOIN Empleados e on e.iduser = a.iduser WHERE e.iduser =   :iduser", Asistenciacapacitaciones.class)
            .setParameter("iduser", iduser);
            List count = (List) q.getResultList();
    return ((BigInteger)count.get(0)).longValue();

}

Also when The return type is used when you want the JPA provider to build an entity from the results, but in this case you want the raw data.

public Object findBySum(Integer iduser){
        Query q =  getEntityManager().createNativeQuery("select SUM(a.cantidadhoras) FROM Asistenciacapacitaciones a JOIN Empleados e on e.iduser = a.iduser WHERE e.iduser =   :iduser")
                .setParameter("iduser", iduser);
                List count = (List) q.getResultList();
        return ((BigInteger)count.get(0)).longValue();

    }

Or another way it could be:

BigDecimal sum =(BigDecimal)em.createNativeQuery ("select SUM(a.cantidadhoras) FROM Asistenciacapacitaciones a JOIN Empleados e on e.iduser = a.iduser WHERE e.iduser =  ?")
.setParameter(1, iduser)
.getSingleResult();