Implementing and Verifying Hibernate Pagination in a Towergit‑Managed Java Project

Problem & Takeaway

When adding pagination to a Spring Data JPA application, developers often forget to test that the generated SQL actually limits results and that the Git history remains clean. This guide shows how to implement Hibernate pagination, verify it, and use Towergit to manage the changes safely.

Desired Outcome

• Pagination logic is correctly implemented using Pageable or setFirstResult / setMaxResults.
• SQL queries contain the appropriate LIMIT / OFFSET clauses.
• Repository history reflects the pagination feature without noisy commits.
• Recovery path is available if the pagination breaks or introduces performance issues.

Prerequisites

1. Java 17‑plus project with Spring Boot 3.x and Spring Data JPA.
2. Hibernate 6.x as the JPA provider.
3. Towergit installed and configured to use the project’s Git repository.
4. Access to a database (PostgreSQL, MySQL, etc.) with a table large enough to test pagination.
5. Basic knowledge of Git commands and Towergit UI.

Procedure

1. Create the Pagination Service

Define a repository method that returns a Page<Entity> using Spring Data’s Pageable:

public interface UserRepository extends JpaRepository<User, Long> {
    Page<User> findByLastName(String lastName, Pageable pageable);
}

Alternatively, use Hibernate’s CriteriaQuery with setFirstResult and setMaxResults for fine‑grained control.

2. Add a Controller Endpoint

@GetMapping("/users")
public ResponseEntity<Page<UserDto>> listUsers(
        @RequestParam String lastName,
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "10") int size) {
    Pageable pageable = PageRequest.of(page, size, Sort.by("id").ascending());
    Page<User> users = userRepository.findByLastName(lastName, pageable);
    return ResponseEntity.ok(users.map(UserDto::from));
}

3. Verify SQL Generation

Run the application in debug mode and inspect the generated SQL. For PostgreSQL, you should see something like:

SELECT * FROM users WHERE last_name = ? ORDER BY id ASC LIMIT 10 OFFSET 20

Use a logger or database profiler to confirm the LIMIT and OFFSET are applied.

4. Test Pagination Logic

• Seed the database with 50+ users sharing the same last name.
• Call /users?lastName=Smith&page=1&size=10 and verify the response contains users 11‑20.
• Increment page and ensure continuity without gaps or duplicates.

5. Commit Changes with Towergit

1. Open Towergit and ensure you’re on the feature/pagination branch.
2. Review the diff: src/main/java/.../UserRepository.java, UserController.java, and any DTO changes.
3. Write a concise commit message, e.g., "Add pagination for user lookup by last name".
4. Stage the files, commit, and push to the remote.

Use Towergit’s Commit & Push button to avoid accidental pushes of incomplete changes.

6. Recovery Options

• If pagination breaks after a push, use Towergit’s Undo Commit to revert the last commit locally.
• To discard local changes entirely, Reset Branch to Remote from Towergit’s branch menu.
• For more granular rollback, use git revert <commit‑hash> from the Towergit terminal.

Expected Checks & Validation

Check	How to Verify	Risk
SQL LIMIT/OFFSET applied	Database profiler or debug log shows LIMIT and OFFSET	Missing limits can cause memory exhaustion.
No N+1 selects	Profile queries; ensure JOIN FETCH or eager fetch only on needed associations.	Performance degradation.
Consistent ordering	Verify that ORDER BY id is present.	Duplicate or missing records across pages.
Clean Git history	Check Towergit commit list; only one commit for pagination changes.	Messy history makes rollback harder.

Limitations & When to Switch to Keyset Pagination

Offset‑based pagination scales poorly when the offset grows large because the database scans rows before the offset. If your application needs to serve pages beyond the first few dozen, consider keyset (seek) pagination:

// Example using the last seen ID
Pageable pageable = PageRequest.of(0, size, Sort.by("id").descending());
List<User> users = userRepository.findByIdGreaterThan(lastSeenId, pageable);

Keyset pagination requires a unique, monotonically increasing key (often the primary key). It offers constant‑time performance but changes the API slightly.

Practical Checklists

1. Run mvn test to ensure unit tests cover pagination.
2. Execute docker exec -it db psql -c "EXPLAIN SELECT * FROM users ..." to view execution plans.
3. Use Towergit’s Compare Branches to confirm no unintended changes were merged.

Following this guide ensures your pagination is efficient, your Git history remains tidy, and you can recover quickly if something goes wrong.