Spring Data JPA分页异常：Offset生效但Limit未生效的解决方案

使用Spring Data JPA的Criteria API构建分页查询，前端传入offset和limit参数，执行时offset生效，但limit未按原生查询逻辑工作——数据库会查询offset+limit条记录（例如传入offset=1000、limit=1时，实际查询1001条），导致查询效率低下。需要在不使用原生SQL的前提下，让limit像原生查询一样只返回指定数量的记录。

当前使用的Criteria查询代码：

public List<Role> findAllByRoleNameAndUpdatedBy(String roleName, Integer offset, Integer limit) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Role> cq = cb.createQuery(Role.class);
    Root<Role> role = cq.from(Role.class);
    Predicate roleNamePredicate = cb.like(role.get("roleName"), roleName + "%");
    cq.where(roleNamePredicate);
    TypedQuery<Role> query = em.createQuery(cq).setFirstResult(offset) // offset
            .setMaxResults(limit); // limit
    return query.getResultList();
}

控制台生成的SQL（以SQL Server为例）：

select
    top 1001 role0_.id as id1_13_,
    role0_.created_by_id as created_2_13_,
    role0_.created_date as created_3_13_,
    role0_.modified_date as modified4_13_,
    role0_.updated_by_id as updated_5_13_,
    role0_.role_name as role_nam6_13_ 
from
    auth_role role0_ 
where
    role0_.role_name like ?

核心问题是查询未指定稳定的排序条件：
JPA需要明确的排序规则来确定offset对应的起始记录位置。如果没有排序条件，数据库无法保证每次查询的结果顺序一致，因此JPA会先拉取offset+limit条记录到内存，再在内存中截取后面的limit条数据，而非直接在数据库层面做分页限制。

方案1：给Criteria查询添加稳定的排序条件 #

在Criteria查询中添加基于唯一字段（如主键id）的排序，让数据库能准确定位分页位置，从而只查询limit条记录。

修改后的代码：

public List<Role> findAllByRoleNameAndUpdatedBy(String roleName, Integer offset, Integer limit) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Role> cq = cb.createQuery(Role.class);
    Root<Role> role = cq.from(Role.class);
    Predicate roleNamePredicate = cb.like(role.get("roleName"), roleName + "%");
    cq.where(roleNamePredicate);
    // 添加基于主键的稳定排序（可替换为你的唯一排序字段）
    cq.orderBy(cb.asc(role.get("id")));
    TypedQuery<Role> query = em.createQuery(cq).setFirstResult(offset)
            .setMaxResults(limit);
    return query.getResultList();
}

修改后生成的SQL会变成（以SQL Server为例）：

select
    top 1 role0_.id as id1_13_,
    role0_.created_by_id as created_2_13_,
    role0_.created_date as created_3_13_,
    role0_.modified_date as modified4_13_,
    role0_.updated_by_id as updated_5_13_,
    role0_.role_name as role_nam6_13_ 
from
    auth_role role0_ 
where
    role0_.role_name like ?
order by role0_.id asc
offset 1000 rows

此时数据库只会查询1条符合条件的记录，而非1001条。

方案2：使用Spring Data JPA的Pageable接口（更推荐） #

Spring Data JPA提供了Pageable接口来简化分页逻辑，它会自动处理排序和分页的SQL生成，避免手动写Criteria的失误。

1. 在Repository接口中定义方法：

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface RoleRepository extends JpaRepository<Role, Long> {
    Page<Role> findByRoleNameLike(String roleName, Pageable pageable);
}

2. 服务层调用：
   注意Pageable的page参数是从0开始的，需要将前端传入的offset转换为page序号（page = offset / limit）：

import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

public List<Role> findAllByRoleNameAndUpdatedBy(String roleName, Integer offset, Integer limit) {
    // 构建Pageable，指定排序字段（保证分页稳定性）
    Pageable pageable = PageRequest.of(offset / limit, limit, Sort.by("id").ascending());
    Page<Role> rolePage = roleRepository.findByRoleNameLike(roleName + "%", pageable);
    return rolePage.getContent();
}

3. 控制器层可以直接接收offset和limit参数，或者改为接收page和size参数（更符合Spring Data的设计）：

@GetMapping(value = "/list/{roleName}/{offset}/{limit}")
public List<Role> getListDummy(@PathVariable String roleName, @PathVariable Integer offset, @PathVariable Integer limit) {
    return roleServiceDummy.findAllByRoleNameAndUpdatedBy(roleName, offset, limit);
}

这种方式不仅能保证数据库层面只查询limit条记录，还能自动处理分页元数据（如总记录数、总页数），便于前端展示分页信息。

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