Count with HQL JPA

Question

Problem

How do I receive the amount of record from a database table?

public Integer quantidadeRegistros() {
    try {
        Query query = em.createQuery("SELECT COUNT(*) FROM Categoria cat");
        return (Integer) query.getSingleResult();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

Error

Caused by: java.lang.RuntimeException: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer
    at erp.dao.CategoriaDao.quantidadeRegistros(CategoriaDao.java:100)
    at erp.dao.CategoriaDao$$FastClassBySpringCGLIB$$8d3c7f86.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at erp.dao.CategoriaDao$$EnhancerBySpringCGLIB$$252f3a07.quantidadeRegistros(<generated>)
    at erp.service.CategoriaService.quantidadeRegistros(CategoriaService.java:95)

Answer 1

Change the * for cat in your query:

public Integer quantidadeRegistros() {
    try {
        Query query = em.createQuery("SELECT COUNT(cat) FROM Categoria cat");
        return (Integer) query.getSingleResult();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

The rule for jpql queries is:

SELECT COUNT(entidade) FROM Entidade entidade

This way as you are using should be a native Nativequery with native SQL:

em.createNativeQuery("SELECT COUNT(*) FROM Categoria").getSingleResult();

---

Updating:

Your problem is on the cast too, change Integer for Long:

public Long quantidadeRegistros() {
    try {
        Query query = em.createQuery("SELECT COUNT(cat) FROM Categoria cat");
        return (Long) query.getSingleResult();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

Or use direct return to Integer, doing the cast of Long for Integer:

public Integer quantidadeRegistros() {
    try {
        Query query = em.createQuery("SELECT COUNT(cat) FROM Categoria cat");
        return query.getSingleResult() != null ? 
               Integer.parseInt(query.getSingleResult().toString()) : 0;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

If I wanted to cast Long for int in his ManagedBean use:

Integer quantidadeRegistros = valorLong != null ? valorLong.intValue() : null;

Reference:

• https://www.guj.com.br/t/jpa-como-usar-count/289701