JavaBean 實現 MySQL 分頁顯示：PageQuery 封裝與 JSP 整合實戰

基於 LIMIT 的 MySQL 分頁 JavaBean：PageQuery 封裝、dbClass 整合、JSP 兩行代碼調用

本文提供一個可復用的分頁組件 PageQuery，封裝 LIMIT offset, size 邏輯、總頁數計算、導航欄生成，配合簡易 dbClass 實現 JSP 頁面極簡調用。適用於 Servlet/JSP 時代專案維護，現代專案建議用 MyBatis Plus / PageHelper。

架構圖

JSP 頁面
    ↓ 調用
PageQuery (分頁邏輯)
    ↓ 委託
dbClass (JDBC 連接/查詢)
    ↓
MySQL

核心類：PageQuery.java

package dbclass;

import java.sql.*;
import javax.servlet.http.*;

public class PageQuery {
    private int offset = 0;      // 記錄偏移量
    private int total = 0;       // 總記錄數
    private int maxLine = 10;    // 每頁條數
    private ResultSet rs;        // 結果集
    private int tPages = 0;      // 總頁數
    private int cPages = 0;      // 當前頁
    private String query;        // 原始 SQL
    private String queryPart;    // FROM 後部分
    private String filePath;     // 請求 URI
    private dbClass db = new dbClass();

    public ResultSet myQuery(String query, HttpServletRequest req) throws SQLException {
        // 1. 提取 FROM 後片段用於 COUNT(*)
        int fromIdx = query.indexOf(" FROM ");
        this.queryPart = query.substring(fromIdx).trim();
        this.query = query;

        // 2. 獲取 offset 參數
        String os = req.getParameter("offset");
        this.offset = (os == null) ? 0 : Integer.parseInt(os);

        // 3. 當前請求路徑（生成分頁鏈接）
        this.filePath = req.getRequestURI();

        // 4. 查總數
        String countSql = "SELECT COUNT(*) AS total " + this.queryPart;
        ResultSet cntRs = db.executeQuery(countSql);
        if (cntRs.next()) this.total = cntRs.getInt("total");

        // 5. 計算頁碼
        this.tPages = (int) Math.ceil((double) this.total / this.maxLine);
        this.cPages = (int) Math.floor((double) this.offset / this.maxLine + 1);

        // 6. 執行分頁查詢
        if (this.total > 0) {
            String pageSql = query + " LIMIT " + this.offset + " , " + this.maxLine;
            this.rs = db.executeQuery(pageSql);
        }
        return this.rs;
    }

    // 導航欄 HTML 生成
    public String pageLegend() {
        String str = "";
        int first = 0;
        int next = this.offset + this.maxLine;
        int prev = this.offset - this.maxLine;
        int last = (this.tPages - 1) * this.maxLine;

        if (this.offset >= this.maxLine)
            str += " <a href='" + filePath + "?offset=" + first + "'>首頁</a> ";
        else str += " 首頁 ";

        if (prev >= 0)
            str += " <a href='" + filePath + "?offset=" + prev + "'>前頁</a> ";
        else str += " 前頁 ";

        if (next < this.total)
            str += " <a href='" + filePath + "?offset=" + next + "'>後頁</a> ";
        else str += " 後頁 ";

        if (this.tPages != 0 && this.cPages < this.tPages)
            str += " <a href='" + filePath + "?offset=" + last + "'>尾頁</a>";
        else str += " 尾頁 ";

        str += " 頁次：" + this.cPages + "/" + this.tPages + "頁 ";
        str += this.maxLine + "條/頁 共" + this.total + "條";
        return str;
    }

    public int getTotalPages() { return tPages; }
    public int getCurrentPage() { return cPages; }
}

依賴類：dbClass.java

package dbclass;
import java.sql.*;

public class dbClass {
    String dbName = "Kernel", login = "root", password = "MySQL";
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/" + dbName + "?user=" + login + "&password=" + password;
    Connection con = null; Statement stmt = null; ResultSet rs = null;

    public dbClass() {
        try { Class.forName(driver); }
        catch (ClassNotFoundException e) { System.out.println(e.getMessage()); }
        try { con = DriverManager.getConnection(url); }
        catch (SQLException e) { System.out.println(e.getMessage()); }
    }

    public ResultSet executeQuery(String sql) throws SQLException {
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        return rs;
    }

    public boolean executeUpdate(String sql) {
        try { stmt = con.createStatement(); stmt.executeUpdate(sql); return true; }
        catch (SQLException e) { System.out.println(e.getMessage()); return false; }
    }
}

JSP 調用範例：example.jsp

<%@ page import="java.sql.*, dbclass.*" %>
<jsp:useBean id="pq" scope="page" class="dbclass.PageQuery" />
<html><body>
<table>
<%
    String query = "SELECT * FROM systempass";  // 注意 FROM 大寫
    ResultSet rs = pq.myQuery(query, request);
    out.println("<tr><td colspan=2>" + pq.pageLegend() + "</td></tr>");
    out.println("<tr><td colspan=2><hr></td></tr>");
    while (rs.next()) {
%>
    <tr><td><%=rs.getString(9)%></td><td><%=rs.getString(10)%></td></tr>
<% } %>
</table></body></html>

關鍵設計點

1. SQL 解析：提取 FROM 後片段構建 COUNT(*)，要求 FROM 大寫
2. 無狀態設計：每次請求重新計算，適合 Web 多用戶併發
3. 導航欄純字串：前端樣式可自行 CSS 美化
4. 異常透傳：SQLException 交由 JSP errorPage 處理

遷移建議

• Spring Boot + MyBatis Plus：IPage page = new Page(offset/maxLine+1, maxLine); mapper.selectPage(page, wrapper);
• Spring Data JPA：Pageable pageable = PageRequest.of(page, size); repo.findAll(pageable);
• 通用攔截器：PageInterceptor 自動攔截 LIMIT 改寫