萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 腳本程式專題 >> JavaScript

JSP 分頁查詢實作:教師資訊管理系統範例

完整 JSP 分頁程式碼

<%@ page contentType="text/html; charset=gb2312" language="java" %>
<%@ page import="java.util.*, java.io.*, java.sql.*" %>
<html>
<head>
    <title>教師信息查詢</title>
    <link href="default.css" rel="stylesheet" type="text/css">
    <style>
        .title { font-family: "華文行楷"; color: #FFFFFF; font-size: 24px; }
        .header { background: #666666; color: #000; }
        .row { background: #999999; }
    </style>
</head>
<body bgcolor="#ffffff">
    <% session.getAttribute("a_name"); %>
    <h1 align="center" class="title">教師信息查詢</h1>
    <hr />
    <jsp:useBean id="teacherBean" scope="page" class="computer.sqlbean" />
    <table width="100%" border="0" cellpadding="0" cellspacing="0">
        <tr class="header" align="center">
            <td colspan="5"><div align="right"><a href="index.html">返回首頁</a></div></td>
        </tr>
        <tr class="row" align="center">
            <td>教號</td><td>姓名</td><td>系別</td><td>專業</td><td>操作選項</td>
        </tr>
    <%
        request.setCharacterEncoding("gb2312");
        int pageSize = 10;                 // 每頁筆數
        int totalRecords = teacherBean.getCounter("SELECT COUNT(*) FROM dysf_teacher");
        int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

        int currentPage = 1;
        String pageParam = request.getParameter("pages");
        if (pageParam != null) {
            currentPage = Integer.parseInt(pageParam);
        }
        if (currentPage < 1) currentPage = 1;
        if (currentPage > totalPages) currentPage = totalPages;

        // 這裡需配合 sqlbean 提供分頁查詢方法
        // ResultSet rs = teacherBean.queryByPage(sql, currentPage, pageSize);
    %>
    <!-- 資料列輸出迴圈 -->
    <%
        // while (rs.next()) { ... }
    %>
    <tr><td colspan="5" align="center">
        <!-- 分頁導航列 -->
        <a href="?pages=1">首頁</a> |
        <a href="?pages=<%=currentPage-1%>">上一頁</a> |
        第 <%=currentPage%> / <%=totalPages%> 頁 |
        <a href="?pages=<%=currentPage+1%>">下一頁</a> |
        <a href="?pages=<%=totalPages%>">尾頁</a>
    </td></tr>
    </table>
</body></html>

分頁核心邏輯解析

步驟說明
1. 取得總筆數SELECT COUNT(*) FROM table
2. 計算總頁數totalPages = (totalRecords + pageSize - 1) / pageSize
3. 解析請求頁碼request.getParameter("pages"),預設第 1 頁
4. 邊界檢查頁碼 < 1 設為 1,> 總頁數設為總頁數
5. 執行分頁查詢資料庫方言不同,MySQL 用 LIMIT offset, size,SQL Server 用 TOP + 子查詢
6. 渲染導航列首頁、上一頁、頁碼、下一頁、尾頁

資料庫分頁寫法對照

-- MySQL
SELECT * FROM teacher LIMIT ?, ?;  -- offset, pageSize

-- Oracle 12c+
SELECT * FROM teacher OFFSET ? ROWS FETCH NEXT ? ROWS ONLY;

-- SQL Server 2012+
SELECT * FROM teacher ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY;

-- 通用子查詢法(舊版相容)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM teacher
) t WHERE rn BETWEEN ? AND ?;

效能優化建議

  • 為分頁欄位建立索引
  • 避免 OFFSET 過大導致掃描效能下降(游標分頁/Keyset Pagination)
  • 總筆數快取或非同步計算
  • 使用連線池減少連線開銷
copyright © 萬盛學電腦網 all rights reserved