0

I want to implement pagination, But I do not want to do a full DBScan every time I execute the same query (this DBcall is done as API).

So far, for pagination I am using TypedQuery in java and setting starting row and pageSize to fetch records.

But the thing is I am confused with how JPA runs queries internally, I think that TypedQuery first executes the query and then sends me a subset of result I want. If so, then is there any way keep track of last processed ResultSet and send it back to response and then use it again do a resultSet.next() next time we hit this API again ?

Or is there any other way to achieve this?

while( resultSet in not null)  
    resultSet = API call to DB with  pageSize
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
abhudya singh
  • 23
  • 4
  • 1
    Rather than using `TypedQuery` You might consider extending `PagingAndSortingRepository` and using `Pageable` and `Page` objects. – Randy Casburn Apr 05 '21 at 22:40
  • Could you please elaborate how to do it or atleast pass any resource? – abhudya singh Apr 06 '21 at 03:34
  • https://www.baeldung.com/spring-data-jpa-pagination-sorting – Randy Casburn Apr 06 '21 at 03:57
  • 1
    The answer to how does JPA or Hibernate execute this query can usually be found by turning on logging to see the generated and executed sql under the covers. On Hibernate this is the showSql option, but I believe there's an equivalent for JPA too – Kevin Hooke Apr 06 '21 at 05:44
  • @abdhuyasingh see my comment under Ken Chan's answer, you might be facing this problem – crizzis Apr 06 '21 at 09:58

1 Answers1

1

When you set the starting row (i.e setFirstResult()) and page size (i.e setMaxResults()) for a query , hibernate will include the offset and limit in the generated SQL. So it already returns you the complete result set and does not need to keep track the last processed row internally.

The hibernate session is basically request scoped. You should close it when a request is completed and open a new one for processing another new request. It also means that all the state in one session will gone after the request is completed and it will not help you to keep track the offset of your last pagination query.

Users of the API have to pass the offset and limit explicitly whenever they call the API. That means it is their responsibility to keep track the last offset and then call the API with a correct offset for fetching the next page.

You can consider to implement a hypermedia-style API or use link header for pagination to alleviate user from maintaining the last offset and calculating the correct offset for the next page. Something likes this.

Ken Chan
  • 84,777
  • 26
  • 143
  • 172
  • 'hibernate will include the offset and limit in the generated SQL' - with one caveat, though. If fetching is specified over a to-many association, the number of result rows does not correspond to the number of returned entities (because of the join), in which case Hibernate is forced to do the paging in memory. [More details here](https://stackoverflow.com/questions/11431670/how-can-i-avoid-the-warning-firstresult-maxresults-specified-with-collection-fe) – crizzis Apr 06 '21 at 09:56