How do I get the number of rows with Hibernate Criteria queries using MySQL 5?

Question

We've been using HQL mostly. But we have this complex search form that contains many joins so I thought I would try my luck at using Criteria's (never have before). I like the syntax much better and it fits the complex form we have.

My first instinct was to do a .list().size() BEFORE I applied the setMaxResults and setFirstResult. Which of course is slow and lazy and a total resource hog.

After a little Googling I found an example that uses ScrollableResults. But the post said that MySQL doesn't support cursors. This was a post from 2004. Here in 2012, we use MySQL 5 with InnoDB tables. So I think we support cursors. Then I found to use projections.

So not being a Hibernate guru, I'm lost on the best way. We MIGHT use DB2 in the future so I would imagine whatever solution I use it would have to work in DB2 and MySQL 5.

Any ideas? I guess at the minimum I could use a custom HQL to get a count(*).

Thanks

UPDATE

I just put this in:

ScrollableResults scr = crit.scroll();
scr.last();
int rowCount = scr.getRowNumber() + 1;

vs

int rowCount = crit.list().size();

Both before I put my limit/start values. It ran MUCH faster. So I'm assuming the cursor is working for out particular DB and results. I even put some joins in there and it seems to still be much faster.

Any input on if this is still a good idea?

score 2 · Answer 1 · answered Nov 09 '12 at 20:03

2

You could use Criteria's setProjection(Projections.property("id")).

answered Nov 09 '12 at 20:03

AmirMV

215
1
11

score 1 · Answer 2 · answered Nov 09 '12 at 16:19

1

I think getting the count through a separate query is the only option, if you want to apply pagination by using setFirstResult and setMaxResults.

answered Nov 09 '12 at 16:19

Yogendra Singh

33,927
6
63
73

What if the DB2 option was off the table? Would the answer change then? Thanks – cbmeeks Nov 09 '12 at 16:23
My answer is based on the hibernate not on the database. Please note: Hibernate supports Lazy collections with batch size, which will serve your need little differently i.e. define batch size as your page size and entire collection as once. It will just lazily initialize them and when you iterate, it will load the results in batch. – Yogendra Singh Nov 09 '12 at 16:32

score 0 · Answer 3 · answered Apr 11 '14 at 06:19

For sure crit.list().size() will load the full set of rows as entities in Hibernate session.

The method ScrollableResults.last() depends on JDBC driver implementation and it may be really slow too because the full ResultSet may be loaded completely, even if Hibernate entities are not created yet.

The best option is to use crit.setProjection(Projections.rowCount()).uniqueResult()

score 0 · Answer 4 · edited Oct 01 '14 at 08:46

0

And you can include hashcode() in your code, that is,

criteria.setProjection(Projections.rowCount()).uniqueResult().hashCode()

edited Oct 01 '14 at 08:46

Rajesh

10,318
16
44
64

answered Oct 01 '14 at 08:26

Raja Chandrasekar

1
1

How do I get the number of rows with Hibernate Criteria queries using MySQL 5?

4 Answers4