Offset Pagination - Trigger COUNT(*) OVER And Return Page<dto>

Description: Typically, in offset pagination, there is one query needed for fetching the data and one for counting the total number of records. But, we can fetch this information in a single database rountrip via a SELECT COUNT subquery nested in the main SELECT. Even better, for databases vendors that support Window Functions there is a solution relying on COUNT(*) OVER() as in this application that uses this window function in a native query against MySQL 8. So, prefer this one instead of SELECT COUNT subquery. This application return a Page<dto>.

Key points:

• create a Spring projection (DTO) to contains getters only for the columns that should be fetched
• write a repository that extends PagingAndSortingRepository
• fetch data via a native query (that includes counting) into a List<dto>
• use the fetched List<dto> and Pageable to create a Page<dto>

Example:

---

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.

---