萬盛學電腦網

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

Servlet 分頁查詢完整實戰:Oracle 資料庫、UserData 實體與 DBHelper 封裝

專案架構

com.example
├── model
│   └── UserData.java          # 實體類 (對應 EMP 表)
├── dao
│   └── DBHelper.java          # 資料存取封裝 (分頁查詢、總頁數)
├── servlet
│   └── PaginationServlet.java # 控制器
└── web
    └── list.jsp               # 視圖

1. 實體類

package com.example.model;

import java.math.BigDecimal;
import java.util.Date;

public class UserData {
    private BigDecimal empno;
    private String ename;
    private String job;
    private BigDecimal mgr;
    private Date hireDate;
    private BigDecimal sal;
    private BigDecimal comm;
    private BigDecimal deptno;
    
    // Getter/Setter 省略 (IDE 自動生成)
    // 建議加上 toString() 方便除錯
    @Override
    public String toString() {
        return "UserData{empno=" + empno + ", ename='" + ename + "'}";
    }
}

2. 資料存取層

package com.example.dao;

import com.example.model.UserData;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

public class DBHelper {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String USER = "scott";
    private static final String PWD = "tiger";
    
    static {
        try { Class.forName("oracle.jdbc.driver.OracleDriver"); }
        catch (ClassNotFoundException e) { throw new RuntimeException(e); }
    }
    
    /** 取得當前頁資料 (Oracle ROWNUM 分頁) */
    public List getPageData(int pageNo, int pageSize) {
        List list = new ArrayList<>();
        String sql = """
            SELECT * FROM (
                SELECT t.*, ROWNUM rn FROM (
                    SELECT * FROM emp ORDER BY empno
                ) t WHERE ROWNUM <= ?
            ) WHERE rn > ?
            """;
        // 計算邊界
        int end = pageNo * pageSize;
        int start = (pageNo - 1) * pageSize;
        
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setInt(1, end);
            ps.setInt(2, start);
            
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    UserData u = new UserData();
                    u.setEmpno(rs.getBigDecimal("empno"));
                    u.setEname(rs.getString("ename"));
                    u.setJob(rs.getString("job"));
                    u.setMgr(rs.getBigDecimal("mgr"));
                    u.setHireDate(rs.getDate("hiredate"));
                    u.setSal(rs.getBigDecimal("sal"));
                    u.setComm(rs.getBigDecimal("comm"));
                    u.setDeptno(rs.getBigDecimal("deptno"));
                    list.add(u);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("分頁查詢失敗", e);
        }
        return list;
    }
    
    /** 取得總記錄數 */
    public int getTotalCount() {
        String sql = "SELECT COUNT(*) FROM emp";
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            return rs.next() ? rs.getInt(1) : 0;
        } catch (SQLException e) {
            throw new RuntimeException("統計總數失敗", e);
        }
    }
}

3. 分頁工具類 (計算頁碼、導航)

package com.example.util;

public class PageUtil {
    public static PageInfo build(int pageNo, int pageSize, int totalCount) {
        int totalPages = (int) Math.ceil((double) totalCount / pageSize);
        pageNo = Math.max(1, Math.min(pageNo, totalPages));
        int start = (pageNo - 1) * pageSize + 1;
        int end = Math.min(pageNo * pageSize, totalCount);
        return new PageInfo(pageNo, pageSize, totalCount, totalPages, start, end);
    }
    
    public static class PageInfo {
        public final int pageNo, pageSize, totalCount, totalPages, start, end;
        public PageInfo(int p, int ps, int tc, int tp, int s, int e) {
            this.pageNo=p; this.pageSize=ps; this.totalCount=tc; 
            this.totalPages=tp; this.start=s; this.end=e;
        }
    }
}

4. Servlet 控制器

package com.example.servlet;

import com.example.dao.DBHelper;
import com.example.model.UserData;
import com.example.util.PageUtil;
import jakarta.servlet.*;
import jakarta.servlet.http.*;
import jakarta.servlet.annotation.*;
import java.io.IOException;
import java.util.List;

@WebServlet("/emp/list")
public class PaginationServlet extends HttpServlet {
    private DBHelper dao = new DBHelper();
    private static final int PAGE_SIZE = 5;
    
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) 
            throws ServletException, IOException {
        
        // 1. 解析頁碼參數
        int pageNo = 1;
        try { pageNo = Integer.parseInt(req.getParameter("page")); } 
        catch (NumberFormatException ignored) {}
        
        // 2. 查詢資料
        List list = dao.getPageData(pageNo, PAGE_SIZE);
        int totalCount = dao.getTotalCount();
        
        // 3. 建構分頁資訊
        PageUtil.PageInfo pageInfo = PageUtil.build(pageNo, PAGE_SIZE, totalCount);
        
        // 4. 存入請求轉發給 JSP
        req.setAttribute("empList", list);
        req.setAttribute("pageInfo", pageInfo);
        req.getRequestDispatcher("/web/list.jsp").forward(req, resp);
    }
}

5. JSP 視圖 (使用 JSTL/EL)

<%@ page contentType="text/html;charset=UTF-8" %>
<%@ taglib prefix="c" uri="https://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
    <title>員工分頁列表</title>
    <style>
        table { border-collapse: collapse; width: 100%; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        .pagination a { margin: 0 4px; padding: 4px 8px; border: 1px solid #ccc; text-decoration: none; }
        .pagination .current { background: #007bff; color: white; }
    </style>
</head>
<body>
    <h2>SCOTT.EMP 員工列表 (每頁 ${pageInfo.pageSize} 筆)</h2>
    
    <table>
        <tr><th>EMPNO</th><th>ENAME</th><th>JOB</th><th>MGR</th><th>HIREDATE</th><th>SAL</th><th>COMM</th><th>DEPTNO</th></tr>
        <c:forEach items="${empList}" var="emp">
        <tr>
            <td>${emp.empno}</td>
            <td>${emp.ename}</td>
            <td>${emp.job}</td>
            <td>${emp.mgr}</td>
            <td>${emp.hireDate}</td>
            <td>${emp.sal}</td>
            <td>${emp.comm}</td>
            <td>${emp.deptno}</td>
        </tr>
        </c:forEach>
    </table>
    
    <div class="pagination">
        <c:if test="${pageInfo.pageNo > 1}">
            <a href="list?page=1">首頁</a>
            <a href="list?page=${pageInfo.pageNo-1}">上一頁</a>
        </c:if>
        
        <c:forEach begin="1" end="${pageInfo.totalPages}" varStatus="i">
            <c:choose>
                <c:when test="${i.current == pageInfo.pageNo}">
                    <span class="current">${i.current}</span>
                </c:when>
                <c:otherwise>
                    <a href="list?page=${i.current}">${i.current}</a>
                </c:otherwise>
            </c:choose>
        </c:forEach>
        
        <c:if test="${pageInfo.pageNo < pageInfo.totalPages}">
            <a href="list?page=${pageInfo.pageNo+1}">下一頁</a>
            <a href="list?page=${pageInfo.totalPages}">末頁</a>
        </c:if>
        
        <span>第 ${pageInfo.pageNo} 頁 / 共 ${pageInfo.totalPages} 頁 (${pageInfo.totalCount} 筆)</span>
        
        <form action="list" method="get" style="display:inline;margin-left:10px;">
            跳轉 <input type="number" name="page" min="1" max="${pageInfo.totalPages}" size="3">
            <input type="submit" value="Go">
        </form>
    </div>
</body></html>

Oracle 分頁 SQL 演進

版本語法特點
Oracle 10g-11g雙層 ROWNUM 子查詢相容性好、效能中等
Oracle 12c+OFFSET ... ROWS FETCH NEXT ... ROWS ONLY標準 SQL、可讀性佳、效能優
所有版本游標/分析函數複雜查詢優化

12c+ 標準寫法

SELECT * FROM emp ORDER BY empno
OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
-- 參數: offset=(pageNo-1)*pageSize, limit=pageSize

關鍵最佳實踐

  1. 連線池:生產環境必須使用 DataSource (DBCP/HikariCP/Tomcat Pool),不可每次 DriverManager.getConnection
  2. 參數化查詢:PreparedStatement 防 SQL 注入
  3. 資源關閉:try-with-resources 自動關閉 Connection/Statement/ResultSet
  4. 總數快取:總筆數變化不頻繁時可快取 (Redis/Guava Cache)
  5. 大數據量優化:延遲關聯、覆蓋索引、避免 SELECT *

總結

本文提供從實體類、DAO 封裝、分頁工具、Servlet 控制器到 JSP 視圖的完整分頁實戰代碼。核心在於:**DAO 專注資料存取、工具類封裝分頁算法、Servlet 協調流程、JSP 純渲染**。Oracle 12c+ 建議使用標準 OFFSET/FETCH 語法,舊版本使用雙層 ROWNUM。生產環境務必配置連線池、加入參數驗證、異常處理與日誌記錄。

copyright © 萬盛學電腦網 all rights reserved