Hibernate limits the number of results, but checks more
As the title states, I want to get a maximum of 1000 rows, but if the query result is 1001, I would like to find out somehow. I've seen examples that would check the number of rows in the result with a second query, but I would like to have it in the query I'm using to get 1000 rows. I am using hibernate and criteria to get my results from my database. Database - MS SQL
source to share
What you want is not generally possible.
2 common pagination templates:
- use 2 queries, the first one that counts, the next one that gets the result page
- use only one query where you get one result more than you show on the page
With the first template, your pagination has more functionality because you can display the total number of pages and allow the user to navigate to the page they want directly, but you get that ability at the cost of an additional sql query.
With the second template, you can simply tell the user if there is another page of data or not. The user can then simply navigate to the next page (or any previous page they have already seen).
source to share
You want to have two information that comes from two different requests:
-
select (quantity) from ...
-
select col1, col2, from ...
You cannot do this in one of the Criteria or JPQL met.
But you can do it with your own SQL query (using a subquery by the way) with a different way according to the DBMS you are using.
By doing this, you would complicate your code by making it more database-specific, and you probably wouldn't get something in terms of performance.
I think you should rather use count and second query to get rows.
And if you later want to use the counting result to get the following results, you must approve the use of the paging mechanisms provided by Hibernate instead of doing it your own way.
source to share