PreparedStatement 实现分页查询详解

在 JDBC 中使用 PreparedStatement 实现分页查询是高效安全的方式，可以避免 SQL 注入并提升性能。下面我将详细说明实现步骤和原理。

📐 分页查询核心参数

参数名	说明	计算公式
pageNum	当前页码（从1开始）	用户输入
pageSize	每页显示的记录数	用户输入或系统默认值
offset	数据偏移量（跳过的记录数）	`(pageNum - 1) * pageSize`
limit	每页获取的记录数	等于 `pageSize`

📝 分页查询实现步骤

1. 构建分页SQL语句（以MySQL为例）

SELECT * FROM your_table 
ORDER BY sort_column 
LIMIT ? OFFSET ?

LIMIT: 指定每页返回的记录数
OFFSET: 指定跳过的记录数
ORDER BY: 必须指定排序字段，确保分页结果稳定

2. Java 实现代码

public List<YourEntity> getPagedData(int pageNum, int pageSize) {List<YourEntity> resultList = new ArrayList<>();String sql = "SELECT id, name, email FROM users ORDER BY id LIMIT ? OFFSET ?";// 计算偏移量int offset = (pageNum - 1) * pageSize;try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql)) {// 设置参数pstmt.setInt(1, pageSize);   // LIMIT 参数pstmt.setInt(2, offset);     // OFFSET 参数try (ResultSet rs = pstmt.executeQuery()) {while (rs.next()) {YourEntity entity = new YourEntity();entity.setId(rs.getLong("id"));entity.setName(rs.getString("name"));entity.setEmail(rs.getString("email"));resultList.add(entity);}}} catch (SQLException e) {throw new DataAccessException("分页查询失败", e);}return resultList;
}

3. 不同数据库的分页语法差异

数据库	分页语法示例	备注
MySQL	`LIMIT ? OFFSET ?`	最常用
PostgreSQL	`LIMIT ? OFFSET ?`	同 MySQL
Oracle	`OFFSET ? ROWS FETCH NEXT ? ROWS ONLY`	12c 及以上版本
SQL Server	`OFFSET ? ROWS FETCH NEXT ? ROWS ONLY`	2012 及以上版本
SQLite	`LIMIT ? OFFSET ?`	同 MySQL

Oracle 兼容写法 (旧版本):

SELECT * FROM (SELECT t.*, ROWNUM rn FROM (SELECT * FROM your_table ORDER BY sort_column) t WHERE ROWNUM <= ?
) WHERE rn > ?

🔍 分页查询最佳实践

1. 获取总记录数

public int getTotalRecords() {String countSql = "SELECT COUNT(*) FROM your_table";try (Connection conn = dataSource.getConnection();PreparedStatement pstmt = conn.prepareStatement(countSql);ResultSet rs = pstmt.executeQuery()) {if (rs.next()) {return rs.getInt(1);}} catch (SQLException e) {// 异常处理}return 0;
}

2. 计算总页数

int totalRecords = getTotalRecords();
int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

3. 分页参数校验

// 确保页码有效
pageNum = Math.max(1, pageNum); // 限制每页最大记录数
pageSize = Math.min(100, Math.max(1, pageSize));

⚠️ 分页查询注意事项

必须排序：分页查询必须指定 ORDER BY 子句，否则结果顺序不确定
性能优化：
- 在排序字段上创建索引
- 避免 SELECT *，只查询必要字段
- 大数据量表考虑使用基于键的分页（WHERE id > ?）
连接池使用：确保使用数据库连接池（如 HikariCP）
事务管理：在同一个事务中获取数据和总记录数，保证一致性
参数绑定：务必使用 PreparedStatement 防止 SQL 注入

🌟 高级分页技术

1. 键集分页（Keyset Pagination）

适用于超大数据集，性能优于传统分页

SELECT * FROM your_table 
WHERE id > ? 
ORDER BY id 
LIMIT ?

2. 前端分页参数处理

// 前端请求示例
const pageRequest = {page: 2,size: 10,sort: "name,asc|email,desc"
};

3. Spring Data JPA 分页

Pageable pageable = PageRequest.of(pageNum - 1, pageSize, Sort.by("name"));
Page<User> page = userRepository.findAll(pageable);List<User> users = page.getContent();
long totalItems = page.getTotalElements();
int totalPages = page.getTotalPages();