Spring Data分页异常：页码>1时返回重复数据问题排查

I've implemented a GET endpoint to return paginated Trip data with optional search criteria:

@GetMapping("/trip")
Page<Trip> all(@SearchSpec Specification<Trip> specs, 
               @RequestParam(defaultValue = "0") int page, 
               @RequestParam(defaultValue = "1000") int pageSize) {
    return repository.findAll(Specification.where(specs), 
                              PageRequest.of(page, pageSize, Sort.by("searchKey").ascending()));
}

When calling the endpoint, page=0 and page=1 return the correct paginated data. However, when page≥2, the returned content is exactly the same as page=1. Adjusting pageSize doesn't fix this issue, and removing the Specification and sorting also doesn't resolve the problem.

Alright, let's tackle this pagination issue you're facing—this is a common gotcha with Spring Data JPA, but we can work through it step by step. Since you mentioned removing the Specification and sorting didn't fix the problem, the root cause is likely tied to how Spring Data JPA identifies unique records or how the database handles pagination under the hood. Here are the most probable fixes:

1. Ensure Your Trip Entity Has a Valid Unique Primary Key #

Spring Data JPA relies entirely on the entity's primary key (annotated with @Id) to track which records belong to which page. If your Trip class doesn't have a unique, non-null primary key, the pagination mechanism can't reliably distinguish between rows, leading to duplicate page results.

How to fix:
Double-check your Trip entity class and make sure it has a properly defined primary key. For example:

@Entity
public class Trip {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // Auto-incrementing unique ID
    private Long id;

    // Your other fields (searchKey, etc.)
    // Getters and setters
}

2. Add a Secondary Sort on a Unique Field #

Even if you're sorting by searchKey, if multiple Trip records share the same searchKey value, the database might not return consistent ordering for pages beyond the first. Without a secondary sort on a unique field (like your primary key), the database can return rows in an arbitrary order when using OFFSET (which is what Spring Data uses for pagination).

How to fix:
Modify your PageRequest to include a secondary sort on your primary key to guarantee consistent ordering:

PageRequest.of(page, pageSize, 
               Sort.by("searchKey").ascending()
                   .and(Sort.by("id").ascending())) // Add this line

3. Check for Custom Repository Overrides #

If your TripRepository has a custom implementation of findAll(Specification, Pageable) that overrides the default Spring Data behavior, it might be botching the pagination logic.

How to fix:
Verify your repository interface extends the correct base repositories without custom overrides that break pagination:

public interface TripRepository extends JpaRepository<Trip, Long>, JpaSpecificationExecutor<Trip> {
    // No custom findAll methods that override the default pagination logic
}

4. Database-Specific Pagination Quirks (Try Keyset Pagination) #

Offset-based pagination (which Spring Data uses by default) can have issues with large offsets or non-unique sorting, especially in databases like MySQL. If the above fixes don't work, consider switching to keyset pagination (also known as "seek pagination") which is more reliable for large datasets.

How to implement:
Modify your endpoint to accept the last searchKey and primary key from the previous page, then use those to fetch the next set of results:

@GetMapping("/trip")
Page<Trip> all(@SearchSpec Specification<Trip> specs, 
               @RequestParam(defaultValue = "0") int page, 
               @RequestParam(defaultValue = "1000") int pageSize,
               @RequestParam(required = false) String lastSearchKey,
               @RequestParam(required = false) Long lastId) {

    // Add keyset filtering if we have the last page's values
    if (lastSearchKey != null && lastId != null) {
        specs = specs.and((root, query, cb) -> 
            cb.or(
                cb.greaterThan(root.get("searchKey"), lastSearchKey),
                cb.and(
                    cb.equal(root.get("searchKey"), lastSearchKey),
                    cb.greaterThan(root.get("id"), lastId)
                )
            )
        );
    }

    return repository.findAll(Specification.where(specs), 
                              PageRequest.of(page, pageSize, 
                                             Sort.by("searchKey").ascending()
                                                 .and(Sort.by("id").ascending())));
}

内容的提问来源于stack exchange，提问作者DezeJongen