CODEWITHSUNDEEP
javamysqlhibernaterowcounthibernate3
cbmeeks
cbmeeks

Reputation: 11420

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

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?

Upvotes: 2

Views: 3946

Answers (4)

Raja Chandrasekar
Raja Chandrasekar

Reputation: 1

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

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

Upvotes: 0

Yves Martin
Yves Martin

Reputation: 10361

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()

Upvotes: 0

AmirMV
AmirMV

Reputation: 215

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

Upvotes: 2

Yogendra Singh
Yogendra Singh

Reputation: 34367

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

Upvotes: 1

Related Questions