JSP + MySQL 留言板實戰（二）：分頁顯示與資料庫讀取優化

分頁查詢核心邏輯

留言板列表頁 (connectmysql.jsp) 實作伺服器端分頁，每頁 15 筆，支援雙層導航（頁碼 + 每 10 頁一組的大翻頁）。

參數處理與預設值

<%
String pageParam = request.getParameter("page");
int currentPage = 0;
try {
    currentPage = Integer.parseInt(pageParam);
} catch (NumberFormatException | NullPointerException e) {
    currentPage = 0;  // 第 1 頁 (0-based)
}
%>

資料庫連線與總筆數查詢

<%
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    conn = DriverManager.getConnection("jdbc:mysql://localhost/p", "test", "");
    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

    // 取得總筆數
    rs = stmt.executeQuery("SELECT COUNT(*) FROM comment");
    rs.next();
    int totalRecords = rs.getInt(1);
    rs.close();

    // 計算分頁參數
    int pageSize = 15;
    int totalPages = (totalRecords + pageSize - 1) / pageSize;  // 無條件進位
    if (currentPage < 0) currentPage = 0;
    if (currentPage >= totalPages) currentPage = totalPages - 1;

    int offset = currentPage * pageSize;

    // 分頁查詢 (MySQL LIMIT 語法)
    String query = "SELECT * FROM comment ORDER BY userid DESC LIMIT " + offset + ", " + pageSize;
    rs = stmt.executeQuery(query);
%>

資料顯示迴圈

<p align="center">總共有 <%=totalRecords%> 條留言</p>
<% while (rs.next()) { %>
    <table width="95%" border="1" cellspacing="1" cellpadding="1" bgcolor="#CCCCFF" align="center">
        <tr>
            <td width="25%">暱稱：<%=rs.getString("username")%></td>
            <td width="25%">性別：<%=rs.getString("sex")%></td>
            <td colspan="2" width="50%">地址：<%=rs.getString("address")%></td>
        </tr>
        <tr>
            <td width="25%">電話：<%=rs.getString("telnumber")%></td>
            <td width="25%">郵編：<%=rs.getString("post")%></td>
            <td width="25%">OICQ：<%=rs.getString("oicq")%></td>
            <td width="25%">ICQ：<%=rs.getString("icq")%></td>
        </tr>
        <tr>
            <td colspan="2" width="50%">
                Email：<a href="mailto:<%=rs.getString("email")%>"><%=rs.getString("email")%></a>
            </td>
            <td colspan="2" width="50%">
                網址：<a href="<%=rs.getString("url")%>" target="_blank"><%=rs.getString("urltitle")%></a>
            </td>
        </tr>
        <tr>
            <td colspan="4">
                <font style="color:green; line-height:150%">
                    留言：<%=rs.getString("comment")%><br>
                    - <%=rs.getString("time")%> (來自 <%=rs.getString("ip")%>)
                </font>
            </td>
        </tr>
    </table>
    <hr align="center" noshade size="2" width="95%">
<% } %>

分頁導航生成邏輯

<%
int blockSize = 10;  // 每組顯示 10 個頁碼
int currentBlock = currentPage / blockSize;
int startPage = currentBlock * blockSize;
int endPage = Math.min(startPage + blockSize, totalPages);

out.print("[共有 " + totalPages + " 頁] ");

// 上一組
if (currentBlock > 0) {
    int prevBlockLastPage = startPage - 1;
    out.print("| <a href='connectmysql.jsp?page=" + prevBlockLastPage + "'>前10頁</a> ");
}

// 頁碼
for (int i = startPage; i < endPage; i++) {
    if (i == currentPage) {
        out.print("| 第 " + (i+1) + " 頁 ");
    } else {
        out.print("| <a href='connectmysql.jsp?page=" + i + "'>第 " + (i+1) + " 頁</a> ");
    }
}

// 下一組
if (endPage < totalPages) {
    out.print("| <a href='connectmysql.jsp?page=" + endPage + "'>後10頁</a> ");
}
out.print("|");
%>

資源釋放

<%
} catch (SQLException e) {
    out.println("<p style='color:red;'>資料庫錯誤: " + e.getMessage() + "</p>");
} finally {
    try { if (rs != null) rs.close(); } catch (Exception ignore) {}
    try { if (stmt != null) stmt.close(); } catch (Exception ignore) {}
    try { if (conn != null) conn.close(); } catch (Exception ignore) {}
}
%>
<p>程式結束</p>

效能與架構問題分析

現代化重構建議

1. DAO 層封裝

public interface CommentDao {
    List findByPage(int offset, int limit);
    long countAll();
}

@Repository
public class JdbcCommentDao implements CommentDao {
    private final JdbcTemplate jdbcTemplate;

    public List findByPage(int offset, int limit) {
        String sql = "SELECT * FROM comment ORDER BY userid DESC LIMIT ?, ?";
        return jdbcTemplate.query(sql, new CommentRowMapper(), offset, limit);
    }

    public long countAll() {
        return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM comment", Long.class);
    }
}

2. Service 層分頁邏輯

 getPage(int pageNumber) {  // 1-based
        int offset = (pageNumber - 1) * PAGE_SIZE;
        List content = dao.findByPage(offset, PAGE_SIZE);
        long total = dao.countAll();
        return new PageResult<>(content, pageNumber, PAGE_SIZE, total);
    }
}

3. Controller 處理請求

 result = commentService.getPage(page);
        model.addAttribute("page", result);
        return "comment/list";  // Thymeleaf 模板
    }
}

4. Thymeleaf 模板片段

<div th:each="c : ${page.content}" class="comment-card">
    <h4><span th:text="${c.username}"></span> <small th:text="${c.time}"></small></h4>
    <p th:text="${c.comment}"></p>
</div>

<nav aria-label="分頁導航">
    <ul class="pagination">
        <li th:if="${page.hasPrevious}" class="page-item">
            <a class="page-link" th:href="@{/comments(page=${page.previousPageNumber})}">上一頁</a>
        </li>
        <li th:each="p : ${page.pageNumbers}" th:classappend="${p == page.currentPage} ? 'active' : ''">
            <a class="page-link" th:href="@{/comments(page=${p})}" th:text="${p}"></a>
        </li>
        <li th:if="${page.hasNext}" class="page-item">
            <a class="page-link" th:href="@{/comments(page=${page.nextPageNumber})}">下一頁</a>
        </li>
    </ul>
</nav>

結語

分頁顯示是 Web 應用最基礎也最關鍵的功能之一。從早期 JSP Scriptlet 手寫 LIMIT 分頁，到現代框架的 Pageable/Page 抽象，核心挑戰始終是：大資料量下的深頁查詢效能、一致性視圖、使用者體驗。掌握基礎原理，才能在選型時做出正確決策。