Servlet 實現分頁查詢：MySQL LIMIT 邏輯與前端分頁控件完整實戰

前言

分頁查詢是 Web 應用高頻需求。本文基於 Servlet + JDBC + MySQL，完整實現「後端分頁邏輯 + 前端分頁控件」兩大核心模塊，重點解析 MySQL LIMIT 子句分頁原理、邊界條件處理、性能優化要點，提供可直接落地的生產級代碼範本。

一、核心分頁邏輯 Servlet

package com.example.web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.*;

public class PaginationServlet extends HttpServlet {
    // 每頁顯示記錄數
    private static final int PAGE_SIZE = 3;

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) 
            throws ServletException, IOException {
        
        resp.setContentType("text/html;charset=UTF-8");
        PrintWriter out = resp.getWriter();
        
        // 1. 獲取當前頁碼 (默認第 1 頁)
        int pageNow = 1;
        String sPageNow = req.getParameter("pageNow");
        if (sPageNow != null && !sPageNow.trim().isEmpty()) {
            try {
                pageNow = Integer.parseInt(sPageNow);
                if (pageNow < 1) pageNow = 1;
            } catch (NumberFormatException e) {
                pageNow = 1;
            }
        }
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            // 2. 加載驅動 & 獲取連接 (生產環境請使用連接池)
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3307/spdb?useSSL=false&characterEncoding=UTF-8";
            String user = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, user, password);
            
            // 3. 查詢總記錄數
            ps = conn.prepareStatement("SELECT COUNT(*) FROM users");
            rs = ps.executeQuery();
            int rowCount = 0;
            if (rs.next()) {
                rowCount = rs.getInt(1);
            }
            
            // 4. 計算總頁數
            int pageCount = (rowCount + PAGE_SIZE - 1) / PAGE_SIZE; // 向上取整
            if (pageCount == 0) pageCount = 1; // 至少 1 頁
            
            // 5. 邊界修正：頁碼不超過總頁數
            if (pageNow > pageCount) pageNow = pageCount;
            
            // 6. 分頁查詢核心：LIMIT offset, pageSize
            int offset = (pageNow - 1) * PAGE_SIZE;
            String sql = "SELECT id, name, passwd, mail, grade FROM users LIMIT ?, ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, offset);
            ps.setInt(2, PAGE_SIZE);
            rs = ps.executeQuery();
            
            // 7. 輸出數據表格
            out.println("");
            out.println("");
            while (rs.next()) {
                out.println("");
                out.println("");
                out.println("");
                out.println("");
                out.println("");
                out.println("");
                out.println("");
            }
            out.println("
ID
姓名
密碼
郵箱
等級
" + rs.getInt("id") + "
" + rs.getString("name") + "
" + rs.getString("passwd") + "
" + rs.getString("mail") + "
" + rs.getInt("grade") + "
");
            
            // 8. 輸出分頁導航條
            out.println("
");
            
            // 首頁 / 上一頁
            if (pageNow > 1) {
                out.println("首頁 ");
                out.println("上一頁 ");
            }
            
            // 頁碼範圍：顯示當前頁前後各 2 頁，共 5 頁
            int startPage = Math.max(1, pageNow - 2);
            int endPage = Math.min(pageCount, pageNow + 2);
            
            // 若總頁數 ≤ 5，顯示所有頁
            if (pageCount <= 5) {
                startPage = 1;
                endPage = pageCount;
            } else if (pageNow <= 3) { // 前 3 頁
                startPage = 1;
                endPage = 5;
            } else if (pageNow >= pageCount - 2) { // 後 3 頁
                startPage = pageCount - 4;
                endPage = pageCount;
            }
            
            for (int i = startPage; i <= endPage; i++) {
                if (i == pageNow) {
                    out.println("" + i + " ");
                } else {
                    out.println("" + i + " ");
                }
            }
            
            // 下一頁 / 尾頁
            if (pageNow < pageCount) {
                out.println("下一頁 ");
                out.println("尾頁 ");
            }
            
            out.println("共 " + rowCount + " 條 / " + pageCount + " 頁");
            out.println("
");
            
        } catch (Exception e) {
            e.printStackTrace();
            out.println("
系統錯誤: " + e.getMessage() + "
");
        } finally {
            // 9. 資源關閉 (逆序)
            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (ps != null) ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
}

二、MySQL LIMIT 子句深度解析

1. 語法形式

-- 形式一：LIMIT offset, row_count (MySQL 專有)
SELECT * FROM users LIMIT 0, 3;   -- 第 1 頁，每頁 3 條
SELECT * FROM users LIMIT 3, 3;   -- 第 2 頁
SELECT * FROM users LIMIT 6, 3;   -- 第 3 頁

-- 形式二：LIMIT row_count OFFSET offset (SQL:2008 標準)
SELECT * FROM users LIMIT 3 OFFSET 0;
SELECT * FROM users LIMIT 3 OFFSET 3;

-- 方案一：覆蓋索引 + 子查詢 (適合有索引的排序字段)
SELECT * FROM users 
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1000000, 1) 
ORDER BY id LIMIT 10;

-- 方案二：游標分頁 (適合無序/唯一遞增 ID)
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;
-- 前端傳遞 last_seen_id 而非 pageNow

int pageCount = 10; // 總頁數
int pageNow = 4;    // 當前頁

int windowSize = 5; // 顯示頁碼數
int halfWindow = windowSize / 2; // 2

int startPage = Math.max(1, pageNow - halfWindow);
int endPage = Math.min(pageCount, pageNow + halfWindow);

// 邊界修正：確保始終顯示 windowSize 個頁碼 (總頁數足夠時)
if (endPage - startPage + 1 < windowSize) {
    if (startPage == 1) {
        endPage = Math.min(pageCount, startPage + windowSize - 1);
    } else if (endPage == pageCount) {
        startPage = Math.max(1, endPage - windowSize + 1);
    }
}
// 結果：pageNow=4 → start=2, end=6 (顯示 2,3,4,5,6)