Hibernate Criteria paging

Is there a way in JAVA hibernate to execute a criteria query and projections count on that same query in a single trip to the DB?

The reason for wanting this is getting the most efficient way to page results, i was hoping to do the following

start transaction 
   start db call
     do quer开发者_如何学Goy()
     do count()
   end db call
end transaction

what i'm currently doing is

start transaction 
   start db call
     do query()
   end db call
   start db call
     do count()
   end db call
end transaction

I really looked into this about a year ago and the best I came up with was executing both database operations in the same transaction. It is still two separate database hits, but it eliminates somewhat embarrassing headers like records 1-50 from 45.

This is a classic question. The short answer is no, as you need to actually determine the number of records, then you need to retrieve only those records you want for the page. I've done this before by using the same Criteria query, but doing the following:

1. Set up the full Criteria query except for sorting, limit and offset
2. Add a Projections.rowCount() to the query, and execute the query using uniqueResult()
3. Set the Projections to null
4. Add the sorting (addOrder), limit (setMaxResults) and offset (setFirstResult)
5. Run the query again, this time using list to retrieve the resultset

Note that even when working inside a stored proc in Oracle, I've seen experienced DBAs do exactly the same thing.

The question is could you even formulate such a query in SQL? You might be able to come up with some crazy union that gets the count and the first page of results together, but I doubt the performance would be better than two queries and no, there would be no way for hibernate to automatically map this for you.