Tuning JHipster Pagination: From Pageable to SQL LIMIT/OFFSET

The moment the generated grid stops scaling

You scaffold a JHipster entity, open the list view, and everything feels solved. The table paginates, the column headers sort, and the REST endpoint accepts page, size, and sort query parameters without you writing a line of SQL. Then production data arrives. A request for page 400 hangs, sorting by a business field takes seconds, and you are left wondering whether the generated pagination is a feature or a trap.

The useful takeaway: JHipster's pagination is not magic—it is Spring Data JPA translating a Pageable object into SQL LIMIT and OFFSET clauses. Once you see that translation, you can predict where it will break and decide whether to tune it or replace it.

What JHipster actually generates

This guide assumes a JHipster 7 or later application using Spring Data JPA; the core repository and controller contracts have been stable across these versions, but always confirm against your generated code. When you run the entity generator, JHipster creates a repository interface that extends JpaRepository. In the Spring Data hierarchy, JpaRepository extends PagingAndSortingRepository, which is where the findAll(Pageable) method comes from. The generated service injects that repository and passes the Pageable straight through. The generated REST resource exposes a method like getAllEntities(Pageable pageable), and Spring's PageableHandlerMethodArgumentResolver binds the page, size, and sort query parameters into that object automatically.

This means the pagination contract is defined by Spring Data, not by JHipster-specific code. The same rules apply whether you generated the app yesterday or upgraded an existing blueprint.

A worked example: from endpoint to SQL

Assume you generated an entity called Invoice. The repository is unremarkable:

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {
}

The service delegates to invoiceRepository.findAll(pageable), and the controller maps a GET request. To verify the behavior, run an authenticated request from your terminal (JHipster secures endpoints by default, so substitute a valid token):

curl -H "Authorization: Bearer YOUR_TOKEN" \
  "http://localhost:8080/api/invoices?page=0&size=20&sort=id,desc"

Three checks tell you the stack is working as designed:

1. Repository inheritance: Confirm the generated interface extends JpaRepository (and therefore PagingAndSortingRepository). If it does, findAll(Pageable) is available.
2. Response shape: The JSON body should be a Spring Data Page serialization. Look for metadata fields such as totalElements, totalPages, and a content array containing the current slice. If those fields are missing, a custom mapper or DTO projection may be intercepting the response.
3. SQL logs: Enable Hibernate SQL logging in your application.yml (or via the logging.level.org.hibernate.SQL property) and re-run the request. You should see a SELECT statement ending in LIMIT and OFFSET clauses (for PostgreSQL, MySQL, or H2). If the log shows a full table fetch without limits, the Pageable was likely ignored or overridden somewhere in the service layer.

One Spring Boot convention trips up many developers: page is one-indexed by default. page=0 is not the first page; page=1 is. If your frontend sends zero-based indexes, either adjust the client or set spring.data.web.pageable.one-indexed-parameters=false.

Where the default pagination becomes a liability

The generated approach is efficient for shallow browsing and toxic for deep scrolling. Two failure modes matter most.

Deep pagination. A request for page=5000&size=50 asks the database for OFFSET 250000. Most relational databases still scan and discard those 250,000 rows before returning the 50 you want. The API response time grows linearly with the offset. If your UI allows users to jump to the last page of a large table, the default Pageable approach will eventually time out. Keyset (seek) pagination—filtering by the last seen ID or timestamp instead of using an offset—avoids the scan, but it is not what JHipster scaffolds by default.

Unindexed sorting. The sort parameter is passed directly to the ORDER BY clause. Sorting by id is usually cheap because primary keys are indexed. Sorting by a descriptive field like customerName without a supporting database index forces a full table scan and an in-memory sort. The generated code has no way to know which columns your users filter and sort by most.

Practical tuning before you ship

You do not need to rewrite the persistence layer on day one, but you should make three deliberate decisions.

• Cap the page size. Spring Boot lets you set spring.data.web.pageable.max-page-size and spring.data.web.pageable.default-page-size. A generous default (say, 50) balances payload size against round trips, but a hard maximum prevents a client from requesting 10,000 rows and exhausting heap memory.
• Index your sort columns. Look at the columns your generated UI actually sorts by. Add database indexes for those columns, or remove the sort affordance from the frontend if the data volume makes it impractical.
• Decide on deep access. If users legitimately need to reach row 100,000, plan for keyset pagination or a search-specific endpoint rather than relying on OFFSET. This is a schema and API design decision, not a configuration toggle.

Verify, then trust

Before you declare the pagination "done," run the verification loop: inspect the repository inheritance, fire a request with explicit page, size, and sort parameters, and read the SQL logs to confirm LIMIT/OFFSET is present. Then test the worst case your UI allows—usually a high page number or a sort on a non-primary-key column. If the SQL log shows a scan you did not expect, you have found the problem before your users did.