Hibernate query to get Total number of entries in a column with a specific value

Question

I am pretty new to hibernate. So this could be a simple question for you all. I have a Table called BuildHistory. In that i have a column with name Status. I want to get total count of entries in that table whose status value is SUCCESS.

UPDATE

This is my method which i use for getting the count.

public Object countStatus(String sql){
    Session session = HibernateServeletContextListner.sessionFactory_Dummy.openSession();
    Transaction tx = null;

    try {
        tx = session.beginTransaction();
        Query query = session.createQuery("select sum(case when bh.status = :"+sql+" then 1 else 0 end) from BuildHistory bh");
        tx.commit();
        //  return query.uniqueResult();
    } catch (HibernateException e) {
        if (tx!=null) tx.rollback();
        e.printStackTrace(); 
    } finally {
        session.close(); 
    }

    return 0;
}

Answer 1

You should be able to use conditional aggregation with HQL. Something like the following should work:

select sum(case when bh.status = :success then 1 else 0 end)
from BuildHistory bh

You might use the following Java code:

String hql = "select sum(case when bh.status = :status then 1 else 0 end) ";
       hql += "from BuildHistory bh";
Query query = session.createQuery(hql);
query.setParameter("status", "success");
int sum = (Integer)query.uniqueResult();

One important point here is that we are binding the :status parameter here dynamically using a prepared statement. In your original code, you were attempting to concatenate the query together, which is prone to error, and SQL injection.