LIMIT offset, row_count 分頁邏輯。Connection 或 DataSource。pq.myQuery(sql, request) + pq.pageLegend()。dbclass/
├── DBConnection.java # 連接池/單例連接 (自行實現或用 DBCP/Hikari)
├── PageQuery.java # 核心分頁邏輯
└── example.jsp # 演示頁面package dbclass;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class PageQuery {
// 配置常量
private static final int DEFAULT_PAGE_SIZE = 10;
private static final String PAGE_PARAM = "page"; // 頁碼參數名
private static final String SIZE_PARAM = "pageSize"; // 每頁條數參數名
private Connection conn; // 外部注入
private int pageSize = DEFAULT_PAGE_SIZE;
private int currentPage = 1;
private long totalRows = 0;
private int totalPages = 0;
private String sql; // 基礎查詢 SQL (不含 LIMIT)
private HttpServletRequest request;
/** 注入連接 (或 DataSource 獲取) */
public void setConnection(Connection conn) { this.conn = conn; }
/** 設置頁面大小 */
public void setPageSize(int pageSize) { this.pageSize = pageSize > 0 ? pageSize : DEFAULT_PAGE_SIZE; }
/**
* 執行分頁查詢
* @param sql 基礎 SELECT 語句 (不含 LIMIT)
* @param request HttpServletRequest (提取 page/pageSize 參數)
* @return ResultSet (已定位到當前頁數據)
*/
public ResultSet myQuery(String sql, HttpServletRequest request) throws SQLException {
this.sql = sql;
this.request = request;
// 1. 解析請求參數
parseRequestParams();
// 2. 計算總行數 (優化：若傳入 COUNT SQL 可避免全表掃描)
calculateTotalRows();
// 3. 計算總頁數、修正當前頁
computePagination();
// 4. 構建帶 LIMIT 的最終 SQL
String limitSql = buildLimitSql();
// 5. 執行查詢並返回 ResultSet
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(limitSql);
return rs;
}
private void parseRequestParams() {
try {
String pageStr = request.getParameter(PAGE_PARAM);
if (pageStr != null) currentPage = Integer.parseInt(pageStr);
} catch (NumberFormatException ignored) { currentPage = 1; }
try {
String sizeStr = request.getParameter(SIZE_PARAM);
if (sizeStr != null) pageSize = Integer.parseInt(sizeStr);
} catch (NumberFormatException ignored) { /* keep default */ }
if (currentPage < 1) currentPage = 1;
}
private void calculateTotalRows() throws SQLException {
// 簡易版：SELECT COUNT(*) FROM (原 SQL) 作為派生表
// 生產建議：傳入專用 COUNT SQL 或緩存總數
String countSql = "SELECT COUNT(*) FROM (" + sql + ") AS t_count";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(countSql);
if (rs.next()) totalRows = rs.getLong(1);
rs.close();
stmt.close();
}
private void computePagination() {
totalPages = (int) Math.ceil((double) totalRows / pageSize);
if (currentPage > totalPages) currentPage = totalPages > 0 ? totalPages : 1;
}
private String buildLimitSql() {
long offset = (long) (currentPage - 1) * pageSize;
// MySQL LIMIT offset, row_count
return sql + " LIMIT " + offset + ", " + pageSize;
}
/**
* 生成分頁導航 HTML 片段
* @return 如 "首頁 上頁 1 2 [3] 4 5 下頁 末頁 共 5 頁 50 條"
*/
public String pageLegend() {
if (totalPages <= 1) return "";
StringBuilder sb = new StringBuilder();
String baseUrl = request.getRequestURI() + "?" + getOtherParams();
// 首頁、上頁
if (currentPage > 1) {
sb.append("首頁 ");
sb.append("上頁 ");
}
// 頁碼範圍：顯示當前頁前後各 2 頁
int start = Math.max(1, currentPage - 2);
int end = Math.min(totalPages, currentPage + 2);
if (start > 1) { sb.append("1 ... "); }
for (int i = start; i <= end; i++) {
if (i == currentPage) {
sb.append("[").append(i).append("] ");
} else {
sb.append("").append(i).append(" ");
}
}
if (end < totalPages) { sb.append("... ").append(totalPages).append(" "); }
// 下頁、末頁
if (currentPage < totalPages) {
sb.append("下頁 ");
sb.append("末頁 ");
}
sb.append("共 ").append(totalPages).append(" 頁 ").append(totalRows).append(" 條");
return sb.toString();
}
/** 保留除 page/pageSize 外的其他查詢參數 */
private String getOtherParams() {
StringBuilder sb = new StringBuilder();
java.util.Enumeration names = request.getParameterNames();
while (names.hasMoreElements()) {
String name = names.nextElement();
if (PAGE_PARAM.equals(name) || SIZE_PARAM.equals(name)) continue;
String[] values = request.getParameterValues(name);
for (String v : values) {
if (sb.length() > 0) sb.append("&");
try {
sb.append(name).append("=").append(java.net.URLEncoder.encode(v, "UTF-8"));
} catch (Exception ignored) {}
}
}
return sb.toString();
}
// Getters for JSP EL
public int getCurrentPage() { return currentPage; }
public int getTotalPages() { return totalPages; }
public long getTotalRows() { return totalRows; }
public int getPageSize() { return pageSize; }
} <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, javax.sql.*, dbclass.*" %>
用戶列表分頁
用戶列表 (共 <%= pq.getTotalRows() %> 條，第 <%= pq.getCurrentPage() %>/<%= pq.getTotalPages() %> 頁)
<%= pq.pageLegend() %>
ID 用戶名 郵箱 創建時間
<%= rs.getInt("id") %>
<%= rs.getString("username") %>
<%= rs.getString("email") %>
<%= rs.getTimestamp("create_time") %>
<%
}
rs.close();
rs.getStatement().close();
conn.close();
%>
| 問題 | 解決方案 |
|---|---|
| COUNT(*) 全表掃描慢 | 1. 傳入專用 countSql 參數2. 緩存總數 (Redis, 過期 5-10 分鐘) 3. 觸發器維護計數表 4. 估算行數 EXPLAIN SELECT ... 的 rows |
| 深分頁 LIMIT 100000, 10 | 1. 游標分頁 WHERE id > last_id ORDER BY id LIMIT 102. 覆蓋索引 + 延遲關聯 3. 禁止超大頁碼跳轉 |
| 連接泄漏 | JSP 用 try-with-resources (JSP 2.0+ / Servlet 3.0+) 或 Filter 統一關閉 |
| SQL 注入 | 基礎 SQL 固定，參數用 PreparedStatement；PageQuery 可改造接受 PreparedStatement 設置參數後再追加 LIMIT |
| 並發下總數不一致 | 接受「最終一致性」；關鍵業務用 SELECT ... FOR UPDATE 鎖表計數 (極罕見) |
com.github.pagehelper
pagehelper-spring-boot-starter
1.4.6
// Mapper
List selectUsers(@Param("name") String name, RowBounds rowBounds);
// Service
PageHelper.startPage(pageNum, pageSize);
List users = userMapper.selectUsers(name);
PageInfo pageInfo = new PageInfo<>(users); // 含總數、導航頁碼
Page page = userRepository.findByNameContaining(name, PageRequest.of(pageNum-1, pageSize, Sort.by("id").descending())); PageQuery 展示了「基礎 SQL + LIMIT + 總數計算 + 導航渲染」的分頁核心模式。雖手寫組件利於理解原理，但生產環境強烈建議使用 MyBatis PageHelper 或 Spring Data Pageable，獲得成熟的緩存、游標分頁、參數綁定、類型安全等企業級能力。核心原理通吃：MySQL LIMIT、PostgreSQL LIMIT/OFFSET、Oracle ROWNUM/ROW_NUMBER、SQL Server OFFSET/FETCH。
