Offset Pagination - Trigger SELECT COUNT Subquery And Return List<entity> Via Extra Column
Description: This application fetches data as List<entity> via Spring Boot offset pagination. The SELECT COUNT triggered for counting the total number of records is a subquery of the main SELECT. Therefore, there will be a single database roundtrip instead of two (typically, one query is needed for fetching the data and one for counting the total number of records).
Key points:
- write a repository that extends
PagingAndSortingRepository - in the
entity, add an extra column for representing the total number of records and annotate it as@Column(insertable = false, updatable = false) - fetch data via a native query (that includes
SELECT COUNTsubquery) into aList<entity>
| If you need a deep dive into the performance recipes exposed in this repository then I am sure that you will love my book "Spring Boot Persistence Best Practices" | If you need a hand of tips and illustrations of 100+ Java persistence performance issues then "Java Persistence Performance Illustrated Guide" is for you. |
|
|
|
