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();
💡 總結
使用 PreparedStatement 實現分頁查詢的關鍵點：
- 正確計算
OFFSET和LIMIT值 - 根據數據庫類型使用正確的分頁語法
- 必須指定
ORDER BY子句 - 結合總記錄數計算實現完整的分頁功能
- 使用參數綁定防止 SQL 注入
在實際項目中，推薦使用成熟的 ORM 框架（如 MyBatis、Hibernate）的分頁功能，它們已經處理了各種數據庫的兼容性問題，并提供了更簡潔的 API。
)
持續更新中.........)
