能否在原生JDBC查询中使用Spring Data的Pageable？

当然可以实现，你需要手动解析Pageable的分页、排序参数，将其整合到原生JDBC的SQL逻辑中，同时查询总记录数来构建完整的分页结果。以下是具体实现方案：

核心实现步骤 #

• 从Pageable中提取分页参数：页码、每页条数、排序规则
• 改造原SQL，添加对应数据库的分页语法和排序逻辑
• 执行分页查询获取数据，同时执行COUNT查询获取总记录数
• 用查询结果和总记录数构建Page对象（如果需要Spring标准的分页返回类型）

代码示例（以MySQL为例） #

假设你已获取到Pageable参数，改造后的代码如下：

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

// 省略其他依赖导入

String baseQuery = "SELECT id, client_name, content FROM some_thing WHERE client_name = ?";
String countQuery = "SELECT COUNT(*) FROM some_thing WHERE client_name = ?";

Connection conn = null;
PreparedStatement dataStmt = null;
PreparedStatement countStmt = null;
ResultSet dataRs = null;
ResultSet countRs = null;

try {
    DataSource ds = getDataSource();
    conn = ds.getConnection();

    // 1. 拼接排序逻辑
    StringBuilder sqlBuilder = new StringBuilder(baseQuery);
    Sort sort = pageable.getSort();
    if (sort.isSorted()) {
        sqlBuilder.append(" ORDER BY ");
        boolean first = true;
        for (Sort.Order order : sort) {
            if (!first) {
                sqlBuilder.append(", ");
            }
            // 这里必须对字段名做白名单校验，防止SQL注入
            String validColumn = validateSortColumn(order.getProperty());
            sqlBuilder.append(validColumn).append(" ").append(order.getDirection());
            first = false;
        }
    }

    // 2. 添加MySQL分页语法
    int offset = pageable.getPageNumber() * pageable.getPageSize();
    sqlBuilder.append(" LIMIT ? OFFSET ?");

    // 3. 执行分页数据查询
    dataStmt = conn.prepareStatement(sqlBuilder.toString());
    dataStmt.setString(1, clientName);
    dataStmt.setInt(2, pageable.getPageSize());
    dataStmt.setInt(3, offset);
    dataRs = dataStmt.executeQuery();

    // 转换结果集为实体列表
    List<SomeThing> dataList = new ArrayList<>();
    while (dataRs.next()) {
        SomeThing thing = new SomeThing();
        thing.setId(dataRs.getLong("id"));
        thing.setClientName(dataRs.getString("client_name"));
        thing.setContent(dataRs.getString("content"));
        dataList.add(thing);
    }

    // 4. 查询总记录数
    countStmt = conn.prepareStatement(countQuery);
    countStmt.setString(1, clientName);
    countRs = countStmt.executeQuery();
    long total = 0;
    if (countRs.next()) {
        total = countRs.getLong(1);
    }

    // 构建Spring标准Page对象
    Page<SomeThing> pageResult = new PageImpl<>(dataList, pageable, total);

    // 后续业务处理...

} catch (SQLException sqlException) {
    log.error(ExceptionUtils.getStackTrace(sqlException));
} finally {
    // 手动关闭资源（或使用Spring JdbcUtils工具类）
    if (dataRs != null) try { dataRs.close(); } catch (SQLException e) {}
    if (countRs != null) try { countRs.close(); } catch (SQLException e) {}
    if (dataStmt != null) try { dataStmt.close(); } catch (SQLException e) {}
    if (countStmt != null) try { countStmt.close(); } catch (SQLException e) {}
    if (conn != null) try { conn.close(); } catch (SQLException e) {}
}

// 排序字段白名单校验示例方法
private String validateSortColumn(String column) {
    List<String> allowedColumns = Arrays.asList("id", "client_name", "content");
    if (!allowedColumns.contains(column)) {
        throw new IllegalArgumentException("不允许的排序字段：" + column);
    }
    return column;
}

关键注意事项 #

• 数据库分页语法差异：
  • Oracle：需用子查询嵌套，示例：SELECT * FROM (SELECT t.*, ROWNUM rn FROM (原SQL) t WHERE ROWNUM <= ?) WHERE rn > ?
  • SQL Server：使用OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
• SQL注入防护：排序字段必须做白名单校验，禁止直接拼接用户传入的字段名
• 页码索引转换：Spring Pageable默认页码从0开始，若业务使用1起始的页码，需调整偏移量计算：offset = (pageable.getPageNumber() - 1) * pageable.getPageSize()

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