Hibernate version: 3.0.4

What is the currently accepted best practice when it comes to pagination?

My setup includes Hibernate on a remove server handling requests for
retrieving datasets, which are larger than is practical to return to clients.
Clients therefore are specifying whatever query criteria they need and
request the resulting data set to be of a particular size ( a page) and
starting at a particular page number. This is all covered in the docs.

However, the clients are required to show the total size of the data set
matching the criteria. How can I do this without running the original
query again, using a count(*) projection? Is there an interface I can use
to achieve this? I saw the Projections.rowCount() projection but as best
as I can ascertain, this would still require me to run the query again.

So .... how would you do it in SQL?

I am not sure it is a good idea to generate dynamic queries this way, but it must work with "native SQL":

"SELECT COUNT(*) FROM ( SELECT ... )" or "WITH SELECT ... " a.k.a "inline view"

Gavin,




I'm not suggesting the approach described (using the rowCount projection)
is not the right way to do this. I just wanted to understand the implication
and make sure I use the correct tools.

Also, this applies to Criteria queries, but how can one achieve this
using a predefined query stored as part of the Hibernate mapping docs?
Should I create a dynamic query as baliukas suggests above?

There is no way to tune dynamic queries constructed from user query criteria (this stuff can be very slow to execute) and it is not secure (SQL injection), not all of databases support inline views too. I use lucene for this stuff and it works great if stale data is tolerated.
It is out of topic, but it must be usefull to think about related problems before to use dynamic queries.

I was a bit unclear as to how to achieve this. What I meant was that
instead of having two queries in the mapping document like so:


I would have only the original query and then create a secondary query
like so:


Or are you suggesting that even this usage is expensive? Otherwise I will
have to ensure that I have two versions of each query I am specifying
ahead of time and ensure that they are both in sync. This is error prone
and could create hard to find errors.

Actually I think this is very similar to the problem I'm having right now.

I'll describe what I'm doing and maybe I'm just being stupid here, but ya'll can tell me anyways.

So, currently, I'm constructing a query and I only want 100 rows back.

I construct my query (FROM foo.bar as bar where bar.valueone = "somevalue" ...) as a String and then issue the following commands:



Now what I additionally have to provide to the user is a number representing the total number of objects that match the query (as in, if I hadn't set maxResults, what would I get back?).

To improve network transfer, it would be nice if I could just get the count back (possibly I would be retrieving a million items across the network instead of just 100).

Should I just give up and retrieve everything and crop afterwards?

Nevermind, I figured it out. I had to close the session and open a new one (for some reason if I didn't do that, it tried to update the table like I had changed the values, even though I hadn't changed the values?).

Here's my code in case anyone else is a dunce like me:

There is nothing wrong in this query, I just misunderstand use case.

It would be so much nicer if the query string can be similarily retrieved from a Criteria object, so that one can:

If you are using Criteria queries, you can just use a Projection, as in

Cool! Or simply,

Very nice :)

Sadly it seems the Criteria object failed on complicated object reference. For example, the following HQL works if I use Query constructed from:


Note the alias somehow must be specified in this case to make it work. (The alias is not necessary if it is a simple property reference.) However, it doesn't work

1) if the alias is taken out (using Query); or
2) if I use Criteria object, regardless of whether I specify an alias or not when I create the Critiera from the session.

It always failed with an error saying it cannot resolve the property "instruction.customerProductAgreement.compositeKey.customerId". I am using Hibernate 3.0.5.

Is this a known bug, or am I mistaken ?

I think if it is a property, that syntax will work. If it is an association to another mapped class, you need to use criteria chaining, as in

Try that and see if it works.

-Lee

Not consistent with the hql fragment used in Query, but the criteria chaining does work. Thanks!