Jdbc Pagination

JDBC Pagination

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rows and start from i-th item clauses directly to the SQL for it to be efficient. Depending on the database this might actually be quite easy (see MySQL's LIMIT -keyword), on other databases such as Oracle it can be a little trickier (involves subquery and using rownum pseudo column).

See this JDBC Pagination Tutorial:
http://java.avdiel.com/Tutorials/JDBCPaging.html

JDBC Pagination Oracle get 1000 at a time execute then get next 1000

Use your application to take note of where you got up to, eg, lets say I'm showing records for today, 20 rows at a time. I might write:

Page 1

select * 
from T 
where date_col = trunc(sysdate) 
order by id desc
fetch first 20 rows only

I fetch ID=100 down to 80...I take note of the 80. My next query will then be

select * 
from T 
where date_col = trunc(sysdate) 
AND ID<80  <<==== additional predicate
order by id desc
fetch first 20 rows only

and my next query would be

select * 
from T 
where date_col = trunc(sysdate) 
AND ID<60
order by id desc
fetch first 20 rows only

and so forth.

Can I do pagination with spring-data-jdbc?

PagingAndSortingRepository is supported since version 2.0 M3

That version wasn't available when this question was asked.

Original answer

Yes, you are right, pagination isn't ready yet.

There is actually a PR that will create infrastructure for that but it won't enable the feature yet. For that you should watch https://jira.spring.io/browse/DATAJDBC-101.

Spring data JDBC query creation with pagination complains IncorrectResultSizeDataAccessException: Incorrect result size

I don't know about the technicality, but this is what I learned from experience.

In your case, if the total number of cities is lesser than the pageable.getPageSize(), then your repository will return a List<>.

But if total number of cities is bigger than the pageable.getPageSize() then your repository will return a Page<>.

Knowing that, this is what I did to work around it.

    Long amount = repository.countByPrefId(prefId);
    if(pagination.getPageSize()>amount ) {
       List<CityEntity> list = repository.findByPrefId(prefId);
    } else {
       Page<CityEntity> pages = repository.findByPrefId(person, PageRequest.of(0, 10));
    }

This also means that in your repository you'll have two differents methods, one with Pageable as a parameter and one with only PrefId as a parameter.

---

Related Topics