Hibernate Criteria Limit mechanism?

Question

Hibernate Criteria support provides a setMaxResults() method to limit the results returned from the db.

I can't find any answer to this in their documentation - how is this implemented? Is it querying for the entire result set and then returning only the request number? Or is it truly limiting the query on the database end (think LIMIT keyword as in mySql).

This is important because if a query could potentially return many many results, I really need to know if the setMaxResults() will still query for all the rows in the database (which would be bad).

Also - if its truly limiting the number of rows on the database end, how is it achieving this cross-db (since I don't think every rdbms supports a LIMIT functionality like mySql does).

Solution 1

Hibernate asks the database to limit the results returned by the query. It does this via the dialect, which uses whatever database-specific mechanism there is to do this (so for SQL Server it will do somthing like "select top n * from table", Oracle will do "select * from table where rownum < n", MySQL will do "select * from table limit n" etc). Then it just returns what the database returns.

Solution 2

The class org.hibernate.dialect.Dialect contains a method called supportsLimit(). If dialect subclasses override this method, they can implement row limit handling in a fashion native to their database flavor. You can see where this code is called from in the class org.hibernate.loader.Loader which has a method titled prepareQueryStatement, just search for the word limit.

However, if the dialect does not support this feature, there is a hard check in place against the ResultSet iterator that ensures Java object (entity) results will stop being constructed when the limit is reached. This code is also located in Loader as well.

Solution 3

I use both Hibernate and Hibernate Search and without looking at the underlying implementation I can tell you that they definitely do not return all results. I have implemented the same query returning all results and then changed it to set the first result and max results (to implement pagination) and the performance gains were massive.

They likely use dialect specific SQL for this, e.g. LIMIT in MySQL, ROWNUM in Oracle. Your entity manager is aware of the dialect that you are using so this is simple.

Lastly if you really want to check what SQL Hibernate is producing for this query, just set the "show_sql" property to true when you create your entity manager / factory and it spits out all the SQL it is running to the console.

Solution 4

HQL does not suppport a limitation inside a query like in SQL, only the setMaxResults() which you also found.

To find out if it transform the setMaxResults() into a LIMIT query, you can turn on your SQL logging.

Solution 5

I know Question is bit old. But yes setMaxResults() is truly limiting the number of rows on the database end.

If you really look into your Hibernate SQL output, you can find the following SQL statement has been appended to your query.

limit ?

View more solutions

Comments

[john]

Intersting. Then why, do you think, did they not provide a way to limit using HQL if they are able to transform to the specific dialect given a criteria setMaxResults ?

[Ritesh Mengji]

have a look in stackoverflow.com/questions/1239723/…

[brent777]

this may provide some clarity: stackoverflow.com/questions/1239723/…. My understanding is that it boils down to keeping the HQL part database agnostic, the dialect specific features are then done through additional method calls like setMaxResults

[brent777]

sorry @Ritesh, I see we posted the same link, you obviously type faster than I do :)