Paging with jdbcTemplate
One of the problems we are facing now is handling the paging of the large ResultSet that we get from the database.
Currently, rows returning a SQL stored procedure call the range jdbcTemplate.query
100K to 300K, and paging is done within the method extractData
.
The page displayed to the user is only 20 lines long.
This is very slow, there is a way to get the data page from the result of the stored procedure using jdbcTemplate
.
source to share
I believe the JdbcTemplate has no special paging functionality. However, even if it does, it may not help your code run faster.
Typically, the page size facing the user is no more than 200 lines, so querying 100-300K lines seems overwhelming and memory-consuming.
You need to first decide which paging strategy to use. Two general strategies are to request the first N pages and store them in a temporary cache - or to request only enough to fill one page size (e.g. 200 lines) and only ask for the next 200 lines if the user asks for it.
You also need to determine what the real reason for the slowness is. Line size is one factor, but not the only one. You have to analyze the schema structure, indexes, query joins, etc.
Keep in mind in normal use case - although you can present the user with up to 10,000 pages, a typical user is unlikely to go through all of these pages - perhaps only the first 5-10 pages are important - hence if you could, limit the result set to a small number would make more sense
source to share
Late but good to know the new version of SpringBoot.
Take a look at PageImpl
:
/**
* Basic {@code Page} implementation.
*
* @param <T> the type of which the page consists.
* @author Oliver Gierke
*/
public class PageImpl<T> extends Chunk<T> implements Page<T>
Constructor:
/**
* Constructor of {@code PageImpl}.
*
* @param content the content of this page, must not be {@literal null}.
* @param pageable the paging information can be {@literal null}.
* @param total the total amount of items available. The total might be adapted considering the length of the content
* given, if it is going to be the content of the last page. This is in place to mitigate inconsistencies
*/
public PageImpl(List<T> content, Pageable pageable, long total)
Hope it helps!
source to share