CODEWITHSUNDEEP
javahibernate
XtremeBaumer
XtremeBaumer

Reputation: 6435

Hibernate limit amount of result but check for more

As the title states, I want to only retrieve a maximum of for example 1000 rows, but if the queries result would be 1001, i would like to know in some way. I have seen examples which would check the amount of rows in result with a a second query, but i would like to have it in the query i use to get the 1000 rows. I am using hibernate and criteria to receive my results from my database. Database is MS SQL

Upvotes: 3

Views: 1369

Answers (2)

davidxxx
davidxxx

Reputation: 131346

You want to have two information that results from two distinct queries :

  • select (count) from...

  • select col1, col2, from...

You cannot do it in a single executed Criteria or JPQL query.
But you can do it with a native SQL query (by using a subquery by the way) with a different way according to the DBMS used.

By making it, you would make more complex your code, make it more dependent to a specific DBMS and you would probably not gained really something in terms of performance.
I think that you should use rather a count and a second query to get the rows.

And if later you want to exploit the result of the count to fetch next results, you should favor the use of the pagination mechanisms provided by Hibernate rather doing it in a custom way.

Upvotes: 1

Thierry
Thierry

Reputation: 5440

What you want is not posssible in a generic way.

The 2 usual patterns for pagination are :

  1. use 2 queries : a first one that count, the next one that get a page of result
  2. use only one query, where you fetch one result more than what you show on the page

With the first pattern, your pagination have more functionalities because you can display the total number of pages, and allow the user to jump to the page he wants directly, but you get this possibility at the cost of an additional sql query.

With the second pattern you can just say to the user if there is one more page of data or not. The user can then just jump to the next page, (or any previous page he already saw).

Upvotes: 3

Related Questions