Menu
One of the problem we face now is handling pagination of the big ResultSet we get from the database.
Currently rows that is returns of the SQL stored procedure calls the jdbcTemplate.query range from 100K to 300K and the pagination is done within the extractData method.
The page displayed in the user is just 20 rows.
This is very slow, is there a way to get a page of data from a result of a stored procedure using jdbcTemplate.
804
JdbcTemplate Pagination and sorting examplesSELECT * FROM USER ORDER BY USERNAME ASC LIMIT 10 OFFSET 0; SELECT * FROM USER ORDER BY USERNAME ASC LIMIT 10 OFFSET 0; SELECT * FROM USER ORDER BY USERNAME ASC LIMIT 10 OFFSET 0; LIMIT indicates that the max number of results query returns.
At work we use Hibernate JDBCTemplate because it has more flexibility. It also has better performance than JPA because you are not "loading" a lot of unnecessary data into your app. In the JDBCTemplate case, your SQL skills go a long way in giving you exactly what you need at the right speed.
JdbcTemplate "queryForObject" and "query" is deprecated in Spring.
Hibernate makes a lot of assumptions and forces you to think and code in a certain way. Using JdbcTemplate is easier because it's just a very thin wrapper around JDBC itself. The price here is that you will write thousands of lines of really boring code. Also, you will find that SQL strings are really hard to maintain.
You can have a look at this
A good example to start with.
109
I believe JdbcTemplate doesn't have specific feature for paging. However even if it does it might not help your code runs faster.
Normally the size of a user-facing page is not more than 200 rows, hence querying 100-300K rows seem excessive and waste a lot of memory.
You need to first decide what kind of paging strategy to use. Two common strategies are to query the first N pages, and store it on a temporary cache -- or to query only enough to fill one page size (eg: 200 rows), and query the next 200 rows only if the user request it.
You need to also identify what is the real reason of slowness. Row size is one factor, but not the only one. You have to analyze your schema structure, indexes, query joins, etc.
Keep in mind under normal use case -- although you can present up to 10000 pages to the user, a typical user is unlikely to go through all those pages -- maybe only the first 5-10 pages are important -- hence if you could, limiting the result set to a small number would make more sense
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
