Some help with hibernate Criteria query

Question

Can somebody please help me complete / clean up this query. I am trying to first get the number of rows in the table, then I need to actually fetch a set of rows. I am not sure how I can use the same criteria instance to perform both queries.

To fetch the count I have something like this:

Criteria criteria = session.createCriteria(MyTable.class);
criteria.setProjection(Projections.rowCount());
Integer count = (Integer) criteria.uniqueResult();
int numRows = count.intValue();

And to fetch the rows (I need only a subset for pagination purposes):

Criteria criteria = session.createCriteria(MyTable.class);
criteria.setFirstResult(offset);
criteria.setMaxResults(limit);
criteria.addOrder(Order.desc(orderBy.toString()));
List<MyType> myType = criteria.list();

Do I first need to Null out the projection or something like that so that I can use the criteria to fetch rows (after performing the count)?

I would love some help cleaning this up so that I can efficiently perform both queries and end up with the total number of rows and a list of results. Thanks!!

Answer 1

Have you seen this question?

Answer 2

You are asking about re-using the same criteria for the count and the projection, correct? I couldn't find a way to do it so I've been putting the criteria and restrictions building in it's own method and building it up twice...once for count and once for the page of data. If there is a better way, I'm all ears...

Answer 3

You need to clear the count projection and set a result transformer to retrieve the MyTable objects. Try this for the second query:

criteria.setProjection(null)

criteria.setResultTransformer(Criteria.ROOT_ENTITY);