Hibernate count rows with some criterias

Question

Suppose I have a table Person and i want to count all those people whose "birthDate" is not null and they are a student. Assuming i have two columns :

birthDate Date (can be null)
isStudent boolean (default: false)

How can i do this using hibernate.. ?

What have you tried? This is a basic query. Have you read the Hibernate documentation? http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html — JB Nizet, Nov 29 '11 at 12:18

score 48 · Accepted Answer · edited Nov 08 '16 at 09:59

48

Criteria crit = session.createCriteria(Person.class);
crit.add( Restrictions.isNotNull("birthDate"));
crit.add( Restrictions.eq("isStudent", true));
crit.setProjection(Projections.rowCount());
Integer count = (Integer)crit.uniqueResult();

edited Nov 08 '16 at 09:59

Khader M A

5,423
3
19
19

answered Jun 19 '13 at 05:28

Lawrence

1,035
13
8

What if he also wanted to see the count of isDropout in the same query ? – Stephane Mar 16 '16 at 12:16
2

If you want two independent counts you'll have to use either a union or a sub-query and thus some aliasing in hibernate. Because you need to group on specific criteria – Lawrence Jun 23 '17 at 14:57

NimChimpsky · Answer 2 · 2011-11-29T12:37:22.997

18

Criteria crit = session.createCriteria(Person.class);
crit.add( Restrictions.isNotNull("birthDate"));
crit.add( Restrictions.eq("isStudent", true));
List<Person> students = crit.list();
Integer count = students.size();

or if just want a single count value, and no list returned :

Criteria crit = session.createCriteria(Person.class);
crit.setProjection(Projections.rowCount());
crit.add( Restrictions.isNotNull("birthDate"));
crit.add( Restrictions.eq("isStudent", true));
return (Long) crit.uniqueResult();

edited Nov 29 '11 at 12:37

answered Nov 29 '11 at 12:19

NimChimpsky

46,453
60
198
311

2

You're fetching the whole list just to know its size? Why use Criteria when a basic HQL query is sufficient? – JB Nizet Nov 29 '11 at 12:24
@JB Nizet because i would probably be using the list as well for other logic – NimChimpsky Nov 29 '11 at 12:26
5

I prefer criteria over concatenating strings, kinda defeats the purpose of hibernate if you write out sql as a string, imho. – NimChimpsky Nov 29 '11 at 12:38
The criteria is ok, but there's no reason to fetch all the results for a single count operation: think about when you have a query with 1,000+ results, it will take so much time for Hibernate to fill the list to only be interested in the number of results – McSonk Jul 11 '17 at 14:26

score 4 · Answer 3 · answered Nov 29 '11 at 12:26

4

Number count = (Number) session.createQuery(
    "select count(p.id) from Person p"
    + " where p.birthDate is not null and p.isStudent = true").uniqueResult();

answered Nov 29 '11 at 12:26

JB Nizet

678,734
91
1,224
1,255

score 1 · Answer 4 · answered May 10 '14 at 17:16

1

int result= (int)((long)session.createQuery("select count(p) from User p where p.mobileNumber = :pMobileNumber")
                 .setParameter("pMobileNumber", mobileNumber).uniqueResult());

answered May 10 '14 at 17:16

Jugal Panchal

1,448
16
20

score 0 · Answer 5 · edited Oct 08 '12 at 11:00

Use it. It's working

public class IncIncidentListGridModel
{

    private String historyStatus = null;

    public String getHistoryStatus()
    {
        return historyStatus;
    }

    public void setHistoryStatus(String historyStatus)
    {
        this.historyStatus = historyStatus;
    }

    public void run()
    {
        IncIncidentListGridModel resultCount =
            (IncIncidentListGridModel) ((SQLQuery) hibersession
                .createSQLQuery("select count(ch.incident_capa_history_id) as historyStatus  from incident_capa_history ch, incident_capa ic where ic.incident_capa_id = ch.FK_incident_capa_id and ic.incident_capa_id='011'"))
                .addScalar("historyStatus", Hibernate.STRING)
                .setResultTransformer(
                    Transformers.aliasToBean(IncIncidentListGridModel.class))
                .uniqueResult();

        System.out.println("count result" + resultCount.getHistoryStatus());
    }
}

Please format the answer properly, not just parts of the answer. — konqi, Oct 08 '12 at 10:54
"Use it. It's working" is not a helpful comment. Why would someone assume that an answer posted would not work? — Stealth Rabbi, Oct 28 '15 at 14:00

score 0 · Answer 6 · answered Mar 28 '20 at 07:42

Criteria crit = session.createCriteria(Person.class);
crit.setProjection(Projections.rowCount());
crit.add( Restrictions.isNotNull("birthDate"));
crit.add( Restrictions.eq("isStudent", true));
return (Long) crit.uniqueResult();

The (Long)crit.uniqueResult() will cause NonUniqueResultException. I check the crit.list(). It is an ArrayList having 2 items. The first item of the arraylist contains the count. The second item always contains a 0. Is it the expected result of rowCount?

Thanks

Hibernate count rows with some criterias

6 Answers6

Linked