com.example
├── model
│ └── UserData.java # 實體類 (對應 EMP 表)
├── dao
│ └── DBHelper.java # 資料存取封裝 (分頁查詢、總頁數)
├── servlet
│ └── PaginationServlet.java # 控制器
└── web
└── list.jsp # 視圖
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 + "'}";
}
}
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);
}
}
}
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;
}
}
}
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);
}
}
<%@ 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 10g-11g | 雙層 ROWNUM 子查詢 | 相容性好、效能中等 |
| Oracle 12c+ | OFFSET ... ROWS FETCH NEXT ... ROWS ONLY | 標準 SQL、可讀性佳、效能優 |
| 所有版本 | 游標/分析函數 | 複雜查詢優化 |
SELECT * FROM emp ORDER BY empno
OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
-- 參數: offset=(pageNo-1)*pageSize, limit=pageSize
DataSource (DBCP/HikariCP/Tomcat Pool),不可每次 DriverManager.getConnectionPreparedStatement 防 SQL 注入SELECT *本文提供從實體類、DAO 封裝、分頁工具、Servlet 控制器到 JSP 視圖的完整分頁實戰代碼。核心在於:**DAO 專注資料存取、工具類封裝分頁算法、Servlet 協調流程、JSP 純渲染**。Oracle 12c+ 建議使用標準 OFFSET/FETCH 語法,舊版本使用雙層 ROWNUM。生產環境務必配置連線池、加入參數驗證、異常處理與日誌記錄。