採用 「總記錄數 → 總頁數 → 當前頁 → 起始行 → 查詢當頁數據」 流程,利用 SQL Server TOP 與 NOT IN 子查詢實現物理分頁。
<%@ page import="java.sql.*,java.util.*" pageEncoding="Big5" %>
<%!
// 頁面層級變量(多執行緒不安全,僅示範用)
String au_id, au_lname, au_fname, phone, address;
%>
<html><body>
<table width="760" align="center"><tr><td>
<table width="740" align="center">
<tr bgcolor="#CCCCCC" align="center">
<td><b>標題</b></td><td><b>作者</b></td>
<td><b>類型</b></td><td><b>點擊</b></td>
<td><b>加入時間</b></td>
</tr>
<%
int pageSize = 5; // 每頁筆數
int currentPage = 1; // 當前頁
int totalRecords = 0; // 總記錄數
int totalPages = 0; // 總頁數
// 1. 取得請求頁碼
String pageParam = request.getParameter("pages");
if (pageParam != null) {
try { currentPage = Integer.parseInt(pageParam); }
catch (NumberFormatException e) { currentPage = 1; }
}
// 2. 資料庫連線
Connection conn = null;
Statement stmt = null;
ResultSet rs = null, cntRs = null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=DBName";
conn = DriverManager.getConnection(url, "user", "pwd");
stmt = conn.createStatement();
// 3. 查總筆數
cntRs = stmt.executeQuery("SELECT COUNT(*) FROM Authors");
if (cntRs.next()) totalRecords = cntRs.getInt(1);
totalPages = (int) Math.ceil((double) totalRecords / pageSize);
// 4. 修正當前頁
if (currentPage < 1) currentPage = 1;
if (currentPage > totalPages) currentPage = totalPages;
// 5. 物理分頁查詢:跳過前 (currentPage-1)*pageSize 筆
String sql = "SELECT TOP " + pageSize +
" au_id, au_lname, au_fname, address, phone " +
"FROM authors " +
"WHERE au_id NOT IN (SELECT TOP " + ((currentPage-1)*pageSize) + " au_id FROM authors ORDER BY au_id DESC) " +
"ORDER BY au_id DESC";
rs = stmt.executeQuery(sql);
// 6. 輸出數據行
while (rs.next()) {
au_id = rs.getString("au_id");
au_lname = rs.getString("au_lname");
au_fname = rs.getString("au_fname");
phone = rs.getString("phone");
address = rs.getString("address");
%>
<tr align="center">
<td><%= au_id %></td>
<td><%= au_lname %></td>
<td><%= au_fname %></td>
<td><%= phone %></td>
<td><%= address %></td>
</tr>
<%
}
} catch (Exception e) { e.printStackTrace(); }
finally {
try { if (rs != null) rs.close(); } catch (Exception e) {}
try { if (cntRs != null) cntRs.close(); } catch (Exception e) {}
try { if (stmt != null) stmt.close(); } catch (Exception e) {}
try { if (conn != null) conn.close(); } catch (Exception e) {}
}
%>
<tr bgcolor="#CCCCCC">
<td colspan="5" align="right">
共 <%= totalRecords %> 條記錄 共 <%= totalPages %> 頁
<% if (currentPage > 1) { %>
<a href="fy.jsp?pages=<%= currentPage-1 %>"><img src="prev.gif" border="0"></a>
<% } %>
<% if (currentPage < totalPages) { %>
<a href="fy.jsp?pages=<%= currentPage+1 %>"><img src="next.gif" border="0"></a>
<% } %> /
<% for (int i = 1; i <= totalPages; i++) {
if (i != currentPage) { %>
<a href="fy.jsp?pages=<%= i %>"><%= i %></a>
<% } } %>
</td>
</tr>
</table>
</td></tr></table>
</body></html>
SELECT TOP pageSize *
FROM table
WHERE pk NOT IN (SELECT TOP (page-1)*pageSize pk FROM table ORDER BY pk DESC)
ORDER BY pk DESC
優點:語法簡單、無需游標。缺點:大數據量時 NOT IN 子查詢效能差,建議改用 ROW_NUMBER() (SQL Server 2005+) 或 Keyset 分頁。
try-with-resources (JDK 7+) 自動關閉連線%! 宣告)在多執行緒下不安全,應改為局部變量頁碼過多時採用「首頁、上一頁、頁碼區塊、下一頁、末頁」模式,避免輸出過多連結。