title: How do I get the number of rows with Hibernate Criteria queries using MySQL 5?
body: <p>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.</p>
  
  <p>My first instinct was to do a <code>.list().size()</code> BEFORE I applied the <code>setMaxResults</code> and <code>setFirstResult</code>.  Which of course is slow and lazy and a total resource hog.</p>
  
  <p>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.</p>
  
  <p>So not being a Hibernate guru, I'm lost on the best way.  We <strong>MIGHT</strong> use DB2 in the future so I would imagine whatever solution I use it would have to work in DB2 and MySQL 5.</p>
  
  <p>Any ideas?  I guess at the minimum I could use a custom HQL to get a count(*).</p>
  
  <p>Thanks</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I just put this in:</p>
  
  <pre><code>ScrollableResults scr = crit.scroll();
  scr.last();
  int rowCount = scr.getRowNumber() + 1;
  </code></pre>
  
  <p><strong>vs</strong></p>
  
  <pre><code>int rowCount = crit.list().size();
  </code></pre>
  
  <p>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.</p>
  
  <p>Any input on if this is still a good idea?</p>