完整 JSP 分頁程式碼
<%@ page contentType="text/html; charset=gb2312" language="java" %>
<%@ page import="java.util.*, java.io.*, java.sql.*" %>
<html>
<head>
<title>教師信息查詢</title>
<link href="default.css" rel="stylesheet" type="text/css">
<style>
.title { font-family: "華文行楷"; color: #FFFFFF; font-size: 24px; }
.header { background: #666666; color: #000; }
.row { background: #999999; }
</style>
</head>
<body bgcolor="#ffffff">
<% session.getAttribute("a_name"); %>
<h1 align="center" class="title">教師信息查詢</h1>
<hr />
<jsp:useBean id="teacherBean" scope="page" class="computer.sqlbean" />
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr class="header" align="center">
<td colspan="5"><div align="right"><a href="index.html">返回首頁</a></div></td>
</tr>
<tr class="row" align="center">
<td>教號</td><td>姓名</td><td>系別</td><td>專業</td><td>操作選項</td>
</tr>
<%
request.setCharacterEncoding("gb2312");
int pageSize = 10; // 每頁筆數
int totalRecords = teacherBean.getCounter("SELECT COUNT(*) FROM dysf_teacher");
int totalPages = (int) Math.ceil((double) totalRecords / pageSize);
int currentPage = 1;
String pageParam = request.getParameter("pages");
if (pageParam != null) {
currentPage = Integer.parseInt(pageParam);
}
if (currentPage < 1) currentPage = 1;
if (currentPage > totalPages) currentPage = totalPages;
// 這裡需配合 sqlbean 提供分頁查詢方法
// ResultSet rs = teacherBean.queryByPage(sql, currentPage, pageSize);
%>
<!-- 資料列輸出迴圈 -->
<%
// while (rs.next()) { ... }
%>
<tr><td colspan="5" align="center">
<!-- 分頁導航列 -->
<a href="?pages=1">首頁</a> |
<a href="?pages=<%=currentPage-1%>">上一頁</a> |
第 <%=currentPage%> / <%=totalPages%> 頁 |
<a href="?pages=<%=currentPage+1%>">下一頁</a> |
<a href="?pages=<%=totalPages%>">尾頁</a>
</td></tr>
</table>
</body></html>
分頁核心邏輯解析
| 步驟 | 說明 |
| 1. 取得總筆數 | SELECT COUNT(*) FROM table |
| 2. 計算總頁數 | totalPages = (totalRecords + pageSize - 1) / pageSize |
| 3. 解析請求頁碼 | request.getParameter("pages"),預設第 1 頁 |
| 4. 邊界檢查 | 頁碼 < 1 設為 1,> 總頁數設為總頁數 |
| 5. 執行分頁查詢 | 資料庫方言不同,MySQL 用 LIMIT offset, size,SQL Server 用 TOP + 子查詢 |
| 6. 渲染導航列 | 首頁、上一頁、頁碼、下一頁、尾頁 |
資料庫分頁寫法對照
-- MySQL
SELECT * FROM teacher LIMIT ?, ?; -- offset, pageSize
-- Oracle 12c+
SELECT * FROM teacher OFFSET ? ROWS FETCH NEXT ? ROWS ONLY;
-- SQL Server 2012+
SELECT * FROM teacher ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY;
-- 通用子查詢法(舊版相容)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM teacher
) t WHERE rn BETWEEN ? AND ?;
效能優化建議
- 為分頁欄位建立索引
- 避免
OFFSET 過大導致掃描效能下降(游標分頁/Keyset Pagination)
- 總筆數快取或非同步計算
- 使用連線池減少連線開銷