Hibernate 分頁查詢底層原理解析：Dialect 適配、ScrollableResultSet 與三層降級策略

分頁 API 使用

Query q = session.createQuery("from Cat");
q.setFirstResult(20000);  // 從第 20001 條開始
q.setMaxResults(100);     // 取 100 條
List list = q.list();

Loader 核心邏輯 (Hibernate 2.0.3)

if (useLimit) {
    sql = dialect.getLimitString(sql);  // 方言專用分頁 SQL
}
PreparedStatement st = session.getBatcher().prepareQueryStatement(sql, scrollable);

數據庫方言分頁實現對比

MySQL - LIMIT 子句

public String getLimitString(String sql) {
    return new StringBuffer(sql).append(" limit ?, ?").toString();
}
// 生成: SELECT * FROM cat LIMIT 20000, 100

Oracle 9i+ - 三層嵌套 ROWNUM

public String getLimitString(String sql) {
    StringBuffer pagingSelect = new StringBuffer(100);
    pagingSelect.append("select * from (select row_.*, rownum rownum_ from (");
    pagingSelect.append(sql);
    pagingSelect.append(") row_ where rownum <= ?) where rownum_ > ?");
    return pagingSelect.toString();
}
// 生成: 
// SELECT * FROM (
//   SELECT row_.*, ROWNUM rownum_ FROM (
//     SELECT * FROM cat
//   ) row_ WHERE ROWNUM <= 20100
// ) WHERE rownum_ > 20000

原因：單層 ROWNUM 不支持 ORDER BY，三層嵌套是 Oracle 最快分頁方案。

其他支持方言

• PostgreSQL: LIMIT ? OFFSET ?
• HSQLDB: LIMIT ? OFFSET ?
• Interbase: ROWS ? TO ?
• SQL Server 2012+: OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

三層降級策略

1. 方言專用 SQL：優先使用 Dialect.supportsLimit()=true 的數據庫原生分頁
2. JDBC 2.0 ScrollableResultSet：

   if (session.getFactory().useScrollableResultSets()) {
       rs.absolute(firstRow);  // 直接定位
   } else {
       for (int m=0; m

3. 內存分頁：全量查詢後內存截取（極端情況）

# hibernate.cfg.xml
<property name="hibernate.jdbc.use_scrollable_resultset">true</property>