JSP高效分頁實戰：基於SQL Server的TOP NOT IN優化方案

分頁是Web應用最基礎也最容易寫出性能問題的功能。本文提供一個針對SQL Server優化的JSP分頁完整實現，採用TOP ... NOT IN子查詢避免全表掃描，並封裝關鍵邏輯供直接複用。

核心SQL優化思路

-- 獲取第cpage頁，每頁ipage條，按au_id降序
SELECT TOP #{ipage} au_id, au_lname, au_fname, address, phone
FROM authors
WHERE au_id NOT IN (
  SELECT TOP #{(cpage-1)*ipage} au_id 
  FROM authors 
  ORDER BY au_id DESC
)
ORDER BY au_id DESC

優勢：無需游標/臨時表，利用索引覆蓋，百萬級數據亦能毫秒級響應。（SQL Server 2012+建議改用OFFSET ... FETCH）

完整JSP實現（fy.jsp）

<%@ page import="java.sql.*,java.util.*" pageEncoding="Big5" %>
<%@ page contentType="text/html;charset=Big5" %>
<%
  // 配置區 —— 僅需修改此處
  final String JDBC_URL = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=DBName";
  final String USER = "user", PWD = "pwd";
  final int PAGE_SIZE = 5;
  
  // 參數解析
  int cpage = 1;
  try { cpage = Integer.parseInt(request.getParameter("pages")); } catch (Exception ignored) {}
  
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null, cntRs = null;
  int totalRows = 0, totalPages = 0;
  
  try {
    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
    conn = DriverManager.getConnection(JDBC_URL, USER, PWD);
    stmt = conn.createStatement();
    
    // 1. 總行數
    cntRs = stmt.executeQuery("SELECT COUNT(*) FROM Authors");
    if (cntRs.next()) totalRows = cntRs.getInt(1);
    totalPages = (int) Math.ceil((double) totalRows / PAGE_SIZE);
    
    // 2. 邊界修正
    if (cpage < 1 || cpage > totalPages) cpage = 1;
    
    // 3. 分頁查詢
    String sql = "SELECT TOP " + PAGE_SIZE + " au_id, au_lname, au_fname, address, phone " +
                 "FROM authors WHERE au_id NOT IN (" +
                 "  SELECT TOP " + (cpage-1)*PAGE_SIZE + " au_id FROM authors ORDER BY au_id DESC" +
                 ") ORDER BY au_id DESC";
    rs = stmt.executeQuery(sql);
%>
  
  <table border="1" cellpadding="2" cellspacing="1" align="center">
    <tr bgcolor="#CCCCCC">
      <th>ID</th><th>姓</th><th>名</th><th>電話</th><th>地址</th>
    </tr>
    <% while (rs.next()) { %>
    <tr>
      <td><%= rs.getString("au_id") %></td>
      <td><%= rs.getString("au_lname") %></td>
      <td><%= rs.getString("au_fname") %></td>
      <td><%= rs.getString("phone") %></td>
      <td><%= rs.getString("address") %></td>
    </tr>
    <% } %>
    <tr bgcolor="#CCCCCC">
      <td colspan="5" align="right">
        共 <%= totalRows %> 條 <%= totalPages %> 頁
        <% if (cpage > 1) { %>
          <a href="fy.jsp?pages=<%= cpage-1 %>">上一頁</a>
        <% } %>
        <% if (cpage < totalPages) { %>
          <a href="fy.jsp?pages=<%= cpage+1 %>">下一頁</a>
        <% } %>
        <% for (int i=1; i<=totalPages; i++) { %>
          <% if (i != cpage) { %>
            <a href="fy.jsp?pages=<%= i %>"><%= i %></a>
          <% } else { %>
            <b><%= i %></b>
          <% } %>
        <% } %>
      </td>
    </tr>
  </table>
<%
  } finally {
    // 資源關閉順序：ResultSet → Statement → Connection
    if (rs != null) try { rs.close(); } catch (Exception ignored) {}
    if (cntRs != null) try { cntRs.close(); } catch (Exception ignored) {}
    if (stmt != null) try { stmt.close(); } catch (Exception ignored) {}
    if (conn != null) try { conn.close(); } catch (Exception ignored) {}
  }
%>

生產環境強化清單

1. 連接池：替換DriverManager為DBCP/HikariCP，配置在context.xml的DataSource
2. PreparedStatement：防注入，預編譯提升性能
3. 索引優化：au_id必須建聚簇索引
4. 大數據量優化：NOT IN子查詢在千萬級可能退化，改用ROW_NUMBER() OVER()或OFFSET/FETCH
5. 前端增強：AJAX無刷新分頁、頁碼輸入框、每頁條數選擇器

結語

分頁看似簡單，實則考驗SQL優化、資源管理、邊界處理等綜合功力。掌握TOP NOT IN模式，是SQL Server開發者邁向高性能分頁的必修課。