如何为与项目实体结构不匹配的DTO实现分页？

当用原生查询映射自定义DTO时，Spring Data JPA的findAll自动分页/排序确实只支持与实体结构一致的返回类型，以下是几个可行的解决办法：

1. 手动拼接分页与排序参数到原生SQL #

直接在原生查询中嵌入分页（LIMIT/OFFSET）和排序（ORDER BY）逻辑，通过@Param传递参数，自己处理Pageable的解析：

@Repository
public interface YourRepository extends JpaRepository<YourEntity, Long> {
    @Query(value = "SELECT col1 AS field1, col2 AS field2 FROM your_table " +
                   "ORDER BY :sortField :sortDirection " +
                   "LIMIT :limit OFFSET :offset",
           nativeQuery = true)
    List<YourDto> findWithPaginationAndSort(
            @Param("sortField") String sortField,
            @Param("sortDirection") String sortDirection,
            @Param("limit") int limit,
            @Param("offset") int offset);
}

调用时从Pageable提取参数：

// 提取排序字段与方向（默认用id升序）
Sort.Order sortOrder = pageable.getSort().stream().findFirst().orElse(new Sort.Order(Sort.Direction.ASC, "id"));
String sortField = sortOrder.getProperty();
String sortDirection = sortOrder.getDirection().name();

// 计算分页参数
int limit = pageable.getPageSize();
int offset = (int) pageable.getOffset();

List<YourDto> dtoList = yourRepository.findWithPaginationAndSort(sortField, sortDirection, limit, offset);
// 包装成Page对象返回
Page<YourDto> page = new PageImpl<>(dtoList, pageable, getTotalCount());

⚠️ 注意：必须对sortField做白名单校验，只允许传入数据库表中存在的列名，避免SQL注入风险。

2. 结合EntityManager与SqlResultSetMapping实现分页 #

通过@SqlResultSetMapping定义DTO的映射规则，再用EntityManager手动构建查询并设置分页、排序，最后包装成Page对象：

第一步：定义映射规则 #

在实体类上添加@SqlResultSetMapping：

@SqlResultSetMapping(
        name = "YourDtoMapping",
        classes = @ConstructorResult(
                targetClass = YourDto.class,
                columns = {
                        @ColumnResult(name = "field1", type = String.class),
                        @ColumnResult(name = "field2", type = Integer.class)
                }
        )
)
@Entity
public class YourEntity {
    // 实体字段定义
}

第二步：实现自定义Repository #

// 自定义Repository接口
public interface YourCustomRepository {
    Page<YourDto> findWithPaginationAndSort(Pageable pageable);
}

// 实现类
@Repository
public class YourCustomRepositoryImpl implements YourCustomRepository {
    private final EntityManager entityManager;

    public YourCustomRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public Page<YourDto> findWithPaginationAndSort(Pageable pageable) {
        // 基础查询SQL
        String sql = "SELECT col1 AS field1, col2 AS field2 FROM your_table";

        // 拼接排序逻辑
        if (pageable.getSort().isSorted()) {
            StringBuilder orderBy = new StringBuilder(" ORDER BY ");
            pageable.getSort().forEach(order -> {
                orderBy.append(order.getProperty())
                        .append(" ")
                        .append(order.getDirection().name())
                        .append(", ");
            });
            sql = sql + orderBy.substring(0, orderBy.length() - 2);
        }

        // 构建查询并映射到DTO
        Query query = entityManager.createNativeQuery(sql, "YourDtoMapping");
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        List<YourDto> content = query.getResultList();

        // 查询总条数（需与主查询逻辑匹配，比如关联表要同步过滤条件）
        Query countQuery = entityManager.createNativeQuery("SELECT COUNT(*) FROM your_table");
        long total = ((Number) countQuery.getSingleResult()).longValue();

        return new PageImpl<>(content, pageable, total);
    }
}

第三步：让主Repository继承自定义接口 #

public interface YourRepository extends JpaRepository<YourEntity, Long>, YourCustomRepository {
}

3. 改用JPQL查询（如果业务允许） #

如果不需要复杂的原生SQL，可以用JPQL直接构造DTO，Spring Data JPA会自动处理分页和排序：

@Repository
public interface YourRepository extends JpaRepository<YourEntity, Long> {
    // 假设YourDto有对应的构造函数：public YourDto(String field1, Integer field2)
    @Query("SELECT new com.yourpackage.YourDto(e.col1, e.col2) FROM YourEntity e")
    Page<YourDto> findAllWithDto(Pageable pageable);
}

这种方式最简洁，JPA会自动将Pageable中的分页、排序参数注入到JPQL中，无需手动处理。

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