Count rows in the result of a query in Hibernate

Question

I'm facing the following problem:

    Criteria criteria = getSession().createCriteria(User.class);

    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.property("id"));
    projectionList.add(Projections.countDistinct("friend_id"));
    projectionList.add(Projections.property("registrationDate"));
    projectionList.add(Projections.property("lastLoginDate"));

    criteria.setProjection(projectionList);

this piece of code returns me a couple of entries with the data I require. However, I need the rowcount for it. Is there a clean way of doing this, rather than just doing a:

return criteria.list().size();

?

Yes, there is http://stackoverflow.com/questions/1372317/how-do-we-count-rows-using-hibernate — luanjot, Jul 17 '14 at 10:14
Sorry, had to change the query to something closer to what I'm using, I did check that answer but it didn't really answer my question. There's a countDistinct within the query and using plain count will give me the wrong number of entries. — kb_z, Jul 17 '14 at 10:16
Good you have found a solution. It's better to post and accept it as your own answer. — MystyxMac, Jul 17 '14 at 20:30

score 0 · Answer 1 · answered Jul 17 '14 at 11:13

0

Criteria criteria = session.createCriteria(Track.class)
                    .setProjection(Projections.rowCount());

            List result = criteria.list();
            if (!result.isEmpty()) {
                Integer rowCount = (Integer) result.get(0);
                System.out.println("Total records: " + rowCount);

answered Jul 17 '14 at 11:13

Kuntal-G

2,970
16
16

This approach overrides the projection list, causing the data row count to be incorrect. Notice there's a 'count distinct' within the projection list which groups users and counts how many different friend_id's they have assigned. – kb_z Jul 17 '14 at 11:30

score 0 · Answer 2 · answered Jul 17 '14 at 12:44

0

What if you add this to the end of the ProjectionList?

Projections.rowCount()

I use the following code to search something and then get the rowcount for it.

Criteria criteria = session.createCriteria(LogEntryOracle.class);
if (search != null && !search.isEmpty()) {
    criteria.add(Restrictions.ilike("xml", search, MatchMode.ANYWHERE));
}

criteria.setProjection(Projections.rowCount());
return (Number) criteria.uniqueResult();

answered Jul 17 '14 at 12:44

MystyxMac

1,532
2
14
28

I've found the solution to my issue, I'll update the post with my solution. I didn't try this solution as I found a simpler approach to it. – kb_z Jul 17 '14 at 15:34

score 0 · Accepted Answer · answered Jul 18 '14 at 10:50

Seems that my problem was caused by the lack of analysis:

Criteria criteria = getSession().createCriteria(User.class);
criteria.setProjection(Projections.countDistinct("id"));

return (Long) criteria.uniqueResult();

since we're already grouping by the user ID then all I had to do is find the amount if unique user IDs matching the search criteria.

Count rows in the result of a query in Hibernate

3 Answers3