萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> JSP 程式設計

JSP 分頁顯示完整實戰:MySQL 資料庫、核心算法與前端導航組件

分頁核心變量

變量含義計算公式
pageSize每頁顯示筆數固定值 (如 10、20)
recordCount總記錄數SELECT COUNT(*) FROM table
pageCount總頁數(recordCount + pageSize - 1) / pageSize
showPage當前頁碼請求參數 page,預設 1
startIndex當前頁首筆在總集合中的索引 (0-based)(showPage - 1) * pageSize

MySQL 分頁查詢 (LIMIT 子句)

-- 第 1 頁 (0-9)
SELECT * FROM messageinfo LIMIT 0, 10;

-- 第 n 頁
SELECT * FROM messageinfo LIMIT ?, ?;
-- 參數1: (pageNo-1)*pageSize, 參數2: pageSize

完整 JSP 實作 (含資料庫操作、分頁導航、跳轉表單)

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%
    // ===== 配置 =====
    final int PAGE_SIZE = 10;
    String dbUrl = "jdbc:mysql://localhost:3306/message_board?useSSL=false&serverTimezone=UTC";
    String dbUser = "root", dbPwd = "123456";
    
    // ===== 解析頁碼 =====
    int showPage = 1;
    try { showPage = Integer.parseInt(request.getParameter("page")); } catch (Exception ignored) {}
    if (showPage < 1) showPage = 1;
    
    // ===== 資料庫操作 =====
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    int recordCount = 0;
    List> messages = new ArrayList<>();
    
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
        
        // 1. 查總筆數
        ps = conn.prepareStatement("SELECT COUNT(*) FROM messageinfo");
        rs = ps.executeQuery();
        if (rs.next()) recordCount = rs.getInt(1);
        rs.close(); ps.close();
        
        // 2. 計算總頁數
        int pageCount = (recordCount + PAGE_SIZE - 1) / PAGE_SIZE;
        if (showPage > pageCount) showPage = pageCount;
        if (showPage < 1) showPage = 1;
        
        // 3. 分頁查詢
        int offset = (showPage - 1) * PAGE_SIZE;
        ps = conn.prepareStatement(
            "SELECT UserName, datetime, content FROM messageinfo ORDER BY datetime DESC LIMIT ?, ?"
        );
        ps.setInt(1, offset);
        ps.setInt(2, PAGE_SIZE);
        rs = ps.executeQuery();
        
        while (rs.next()) {
            Map row = new HashMap<>();
            row.put("userName", rs.getString("UserName"));
            row.put("datetime", rs.getString("datetime"));
            row.put("content", rs.getString("content"));
            messages.add(row);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try { if (rs != null) rs.close(); } catch (Exception ignored) {}
        try { if (ps != null) ps.close(); } catch (Exception ignored) {}
        try { if (conn != null) conn.close(); } catch (Exception ignored) {}
    }
    
    // 存入 pageContext 供 EL 使用 (或直接在頁面用 scriptlet 變數)
    pageContext.setAttribute("messages", messages);
    pageContext.setAttribute("showPage", showPage);
    pageContext.setAttribute("pageCount", pageCount);
    pageContext.setAttribute("recordCount", recordCount);
%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>留言板 - 第 <%=showPage%> 頁</title>
    <style>
        .msg { border-bottom: 1px solid #eee; padding: 15px 0; }
        .msg-header { display: flex; justify-content: space-between; color: #666; font-size: 14px; margin-bottom: 8px; }
        .msg-content { white-space: pre-wrap; word-break: break-all; }
        .pagination { margin: 20px 0; text-align: center; }
        .pagination a, .pagination span { display: inline-block; margin: 0 3px; padding: 6px 12px; border: 1px solid #ddd; text-decoration: none; color: #333; border-radius: 3px; }
        .pagination .current { background: #007bff; color: white; border-color: #007bff; }
        .pagination .disabled { color: #999; pointer-events: none; }
        .jump-form { display: inline-block; margin-left: 15px; }
    </style>
</head>
<body>
    <div style="max-width:800px;margin:0 auto;padding:20px;">
        <h1>留言板</h1>
        <p>共 <strong><%=recordCount%></strong> 條留言,第 <strong><%=showPage%></strong> / <strong><%=pageCount%></strong> 頁</p>
        
        <div id="messages">
            <% for (Map msg : messages) { %>
            <div class="msg">
                <div class="msg-header">
                    <span><%=msg.get("userName")%></span>
                    <span><%=msg.get("datetime")%></span>
                </div>
                <div class="msg-content"><%=msg.get("content")%></div>
            </div>
            <% } %>
        </div>
        
        <div class="pagination">
            <%-- 首頁/上一頁 --%>
            <a href="list.jsp?page=1" class="<%=showPage==1?'disabled':''%>">首頁</a>
            <a href="list.jsp?page=<%=showPage-1%>" class="<%=showPage==1?'disabled':''%>">上一頁</a>
            
            <%-- 頁碼列表 (最多顯示 10 個) --%>
            <%
                int startPage = Math.max(1, showPage - 5);
                int endPage = Math.min(pageCount, startPage + 9);
                if (endPage - startPage < 9) startPage = Math.max(1, endPage - 9);
                for (int i = startPage; i <= endPage; i++) {
            %>
                <a href="list.jsp?page=<%=i%>" class="<%=i==showPage?'current':''%>"><%=i%></a>
            <% } %>
            
            <%-- 下一頁/末頁 --%>
            <a href="list.jsp?page=<%=showPage+1%>" class="<%=showPage==pageCount?'disabled':''%>">下一頁</a>
            <a href="list.jsp?page=<%=pageCount%>" class="<%=showPage==pageCount?'disabled':''%>">末頁</a>
            
            <%-- 跳轉表單 --%>
            <form class="jump-form" action="list.jsp" method="get">
                <input type="number" name="page" min="1" max="<%=pageCount%>" value="<%=showPage%>" size="3">
                <input type="submit" value="跳轉">
            </form>
        </div>
    </div>
</body></html>

重構建議:分離關注點 (MVC)

Servlet 控制器

@WebServlet("/messages")
public class MessageListServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) 
            throws ServletException, IOException {
        int page = Math.max(1, Integer.parseInt(req.getParameter("page") == null ? "1" : req.getParameter("page")));
        int pageSize = 10;
        
        // 呼叫 Service/DAO 獲取分頁資料
        PageResult result = messageService.findPage(page, pageSize);
        
        req.setAttribute("pageResult", result);
        req.getRequestDispatcher("/WEB-INF/view/message/list.jsp").forward(req, resp);
    }
}

分頁結果封裝類

public class PageResult {
    private List data;
    private int pageNo, pageSize, totalCount, totalPages;
    // getter/setter + 建構子
    
    public int getStartPage() { return Math.max(1, pageNo - 5); }
    public int getEndPage() { return Math.min(totalPages, getStartPage() + 9); }
}

JSP 視圖 (純 EL/JSTL)

<c:forEach items="${pageResult.data}" var="msg">
    <div class="msg">
        <div class="msg-header"><span>${msg.userName}</span><span>${msg.datetime}</span></div>
        <div class="msg-content">${msg.content}</div>
    </div>
</c:forEach>

<c:forEach begin="${pageResult.startPage}" end="${pageResult.endPage}" var="i">
    <a href="messages?page=${i}" class="${i == pageResult.pageNo ? 'current' : ''}">${i}</a>
</c:forEach>

大數據量優化策略

策略適用場景原理
延遲關聯千萬級資料、深分頁先查 ID (覆蓋索引),再回表查詳情
游標分頁無限滾動、時間線WHERE id > lastId ORDER BY id LIMIT 20
覆蓋索引查詢欄位少索引包含所有查詢欄位,避免回表
總數快取總筆數變化慢Redis 快取 COUNT(*),定期更新
預估總數極大表、近似值可接受EXPLAIN SELECT COUNT(*) 或資訊表統計

總結

JSP 分頁核心在於 **LIMIT offset, size** 配合 **總筆數查詢** 計算導航。本文提供從單一 JSP 完整實作到 MVC 分層重構的完整代碼。關鍵最佳實踐:使用 PreparedStatement 防注入、try-with-resources 管理資源、頁碼邊界檢查、導航組件智能截斷。大數據量場景需採用延遲關聯或游標分頁優化效能。

copyright © 萬盛學電腦網 all rights reserved