Java + Oracle 分頁查詢完整實戰：從原理到代碼落地

分頁核心原理

數據庫層分頁是性能最優方案。Oracle 無 LIMIT，採用三層嵌套 ROWNUM 實現：

SELECT * FROM (
    SELECT ROWNUM rn, t.* FROM (
        SELECT * FROM user_info WHERE ... ORDER BY id
    ) t WHERE ROWNUM <= :endRow
) WHERE rn >= :startRow

• 內層：排序 + 篩選
• 中層：打上 ROWNUM 並截取前 endRow 條
• 外層：過濾 rn >= startRow 獲得當前頁數據

通用分頁模型

public class PageModel<E> {
    private List<E> list;      // 當前頁數據
    private int pageNo;         // 當前頁碼
    private int pageSize;       // 每頁大小
    private int totalNum;       // 總記錄數
    private int totalPage;      // 總頁數

    // getter/setter 省略
    // 輔助方法：首頁、末頁、上一頁、下一頁、是否可用
    public void setTotalNum(int totalNum) {
        this.totalNum = totalNum;
        this.totalPage = (totalNum % pageSize == 0) ? 
            totalNum / pageSize : totalNum / pageSize + 1;
    }
}

分層架構實現

1. Servlet 層（接收參數、返回 XML/JSON）

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) {
    int pageNo = Integer.parseInt(req.getParameter("pageNo"));
    UserInfo query = new UserInfo();
    query.setSex(req.getParameter("gender"));
    query.setHome(req.getParameter("newlocation"));
    // ... 其他條件

    UserInfoManage service = new UserInfoManage();
    PageModel<UserInfo> page = service.userBasicSearch(query, pageNo, pageSize);

    // 構建 XML 響應（或用 Jackson 序列化 JSON）
    StringBuilder xml = new StringBuilder("<?xml ...?><root>");
    for (UserInfo u : page.getList()) {
        xml.append("<data><id>").append(u.getId()).append("</id>...")
           .append("<truename>").append(u.getTruename()).append("</truename>...")
           .append("</data>");
    }
    xml.append("<pagination><total>").append(page.getTotalPage())
       .append("</total><pageno>").append(page.getPageNo()).append("</pageno></pagination></root>");
    writeResponse(resp, xml.toString());
}

2. Service 層（事務控制、調用 DAO）

public PageModel<UserInfo> userBasicSearch(UserInfo u, int pageNo, int pageSize) {
    Connection conn = null;
    PageModel<UserInfo> page = new PageModel<>();
    try {
        conn = DBUtil.getConnection();
        DBUtil.setAutoCommit(conn, false);
        page.setList(dao.getUserList(u, pageNo, pageSize));
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setTotalNum(dao.getTotalNum(u));
        DBUtil.commit(conn);
    } catch (Exception e) {
        DBUtil.rollBack(conn);
        throw new RuntimeException(e);
    } finally {
        DBUtil.closeConnection();
    }
    return page;
}

3. DAO 層（分頁 SQL、參數綁定）

public List<UserInfo> getUserList(UserInfo u, int pageNo, int pageSize) {
    String sql = "SELECT * FROM (" +
                 "  SELECT ROWNUM rn, t.* FROM (" +
                 "    SELECT * FROM user_info WHERE sex = ? AND home LIKE ? AND colleage LIKE ? AND comingyear LIKE ? ORDER BY id" +
                 "  ) t WHERE ROWNUM <= ?" +
                 ") WHERE rn >= ?";
    try (Connection conn = DBUtil.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, u.getSex());
        ps.setString(2, u.getHome() + "%");
        ps.setString(3, u.getColleage() + "%");
        ps.setString(4, u.getCy() + "%");
        ps.setInt(5, pageNo * pageSize);       // endRow
        ps.setInt(6, (pageNo - 1) * pageSize + 1); // startRow
        try (ResultSet rs = ps.executeQuery()) {
            List<UserInfo> list = new ArrayList<>();
            while (rs.next()) {
                UserInfo user = new UserInfo();
                user.setId(rs.getInt("id"));
                user.setTruename(rs.getString("truename"));
                user.setSex(rs.getString("sex"));
                user.setHome(rs.getString("home"));
                list.add(user);
            }
            return list;
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

public int getTotalNum(UserInfo u) {
    String sql = "SELECT COUNT(*) FROM user_info WHERE sex = ? AND home LIKE ? AND colleage LIKE ? AND comingyear LIKE ?";
    // ... 類似執行，返回 count
}

4. DBUtil（連接管理、ThreadLocal 綁定事務）

public class DBUtil {
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();
    public static Connection getConnection() {
        Connection conn = tl.get();
        if (conn == null) {
            conn = DriverManager.getConnection(url, user, pwd);
            tl.set(conn);
        }
        return conn;
    }
    public static void closeConnection() {
        Connection conn = tl.get();
        if (conn != null) {
            try { conn.close(); } catch (SQLException ignored) {}
            tl.remove();
        }
    }
    // setAutoCommit, commit, rollBack, closePreparedStatement, closeResultSet ...
}

ThreadLocal 作用：同一線程內多次 getConnection() 返回同一連接，保證事務一致性。

優化建議

1. 使用連接池：替換 DriverManager 為 HikariCP/Druid。
2. 參數化 LIKE：DAO 中 ps.setString(2, u.getHome() + "%") 防注入。
3. 總數查詢優化：大表可用 COUNT(1)、索引覆蓋、或緩存總數。
4. 游標分頁：超大偏移量（如第 10 萬頁）改用 WHERE id > lastId ORDER BY id LIMIT pageSize。
5. ORM 框架：生產建議用 MyBatis/PageHelper，自動攔截分頁 SQL。

總結

Oracle 分頁雖繁瑣，但掌握「三層 ROWNUM」模式並封裝通用 PageModel、DBUtil，即可構建可復用的分頁組件。現代項目建議引入成熟分頁插件，專注業務邏輯。