Offset Pagination - Trigger COUNT(*) OVER And Return Page<entity> Via Extra Column

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 fetches data as Page<entity> via Spring Boot offset pagination, but, if the fetched data is read-only, then rely on Page<dto> as here.

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 counting) into a List<entity>
• use the fetched List<entity> and Pageable to create a Page<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.

---