Query for getting the count of specific columns in HQL

Question

Can anyone please tell me how to get the count for specific columns in HQL. In the below code I want the count of rows having parameters sClass and section. Some data is coming from the front end in sClass and section, on this basis I want the count.

public List<Integer> classSectionSearch(String sClass, String section) {

        Query query = sessionFactory.getCurrentSession().createQuery("select sd.section, sd.sClass, count(*) from student_details sd where sd.sClass=:sClass and sd.section=:section");
        query.setParameter("sClass", sClass);
        query.setParameter("section", section);
        List<Integer> count=query.list();
        return count;
    }

Answer 1

You don't need to have query parameters in the select clause.

You can simply use a utility class, provided below:

Query query = sessionFactory.getCurrentSession().createQuery("select count(*) " +
            "from student_details sd " +
            "where sd.sClass=:sClass and sd.section=:section");
    
query.setParameter("sClass", sClass);
query.setParameter("section", section);

int count = HibernateUtils.count(query);

The utility class:

public final class HibernateUtils {

    private HibernateUtils() {

    }

    public static int count(Query query) {
        Number result = (Number) query.uniqueResult();
        return result == null ? 0 : result.intValue();
    }

    public static int count(Criteria countCriteria) {
        Number count = (Number) countCriteria
                .setProjection(Projections.rowCount())
                .uniqueResult();
        return count.intValue();
    }
}

You can also do count using Criteria (the second utility method).

Implementation Note: Keep in mind that you should specify entity names (not table names) in HQL. So, if you have a table student_details and an Entity StudentDetail (not plural), you have to write:

select count(*) from StudentDetail sd