Oracle与PostgreSQL中查询行数限制及JPA原生SQL分页适配问题

刚好踩过这个 exact 坑！因为JPA Criteria API确实不支持UNION这类集合操作，被迫切换到原生SQL是无奈但必要的选择，但跨数据库分页其实不用手动写两套SQL——我整理了两种实用方案，按优先级推荐：

方案1：直接用JPA原生Query的分页API（最省心，推荐） #

你原来用Criteria API写的setFirstResult()/setMaxResults()，原生SQL的TypedQuery/Query也支持！ 只要你的JPA实现（比如Hibernate）配置了正确的数据库方言，它会自动帮你把这两个参数转换成对应数据库的分页语法，完全不用自己处理差异。

代码示例 #

// 你的UNION原生SQL（注意：如果要排序，一定要把排序放在外层子查询里，避免只排序单个UNION分支）
String unionSql = """
    SELECT id, name FROM table_a WHERE status = 1
    UNION
    SELECT id, name FROM table_b WHERE status = 1
""";

// 创建原生TypedQuery（如果用DTO，确保DTO有对应的构造函数或者用@SqlResultSetMapping映射）
TypedQuery<MyDto> query = entityManager.createNativeQuery(unionSql, MyDto.class);

// 直接复用你原来的分页代码！
int startRow = gridParams.getStartRow();
int pageSize = gridParams.getEndRow() - Math.max(0, startRow);
query.setFirstResult(startRow);
query.setMaxResults(pageSize);

List<MyDto> result = query.getResultList();

原理说明 #

只要你在配置文件里指定了正确的方言：

• Oracle 12c：org.hibernate.dialect.Oracle12cDialect
• PostgreSQL：org.hibernate.dialect.PostgreSQLDialect

JPA实现会自动把你的查询转换成对应数据库的分页语法：

• Oracle 12c版本：自动套上OFFSET ... FETCH NEXT ... ROWS ONLY
• PostgreSQL版本：自动追加LIMIT ... OFFSET ...

完全不用你手动拼接SQL，零额外工作量！

方案2：手动根据数据库方言拼接分页SQL（适合特殊场景） #

如果因为某些原因（比如用了小众JPA实现、或者有极复杂的SQL逻辑）不能用方案1，可以手动判断当前数据库类型，拼接对应的分页语法。

代码示例 #

String unionSql = """
    SELECT id, name FROM table_a WHERE status = 1
    UNION
    SELECT id, name FROM table_b WHERE status = 1
""";
int startRow = gridParams.getStartRow();
int pageSize = gridParams.getEndRow() - Math.max(0, startRow);
String finalSql;

// 获取当前数据库方言（以Hibernate为例）
Session session = entityManager.unwrap(Session.class);
Dialect dialect = session.getSessionFactory().getJdbcServices().getDialect();

// 根据方言拼接分页SQL
if (dialect instanceof Oracle12cDialect) {
    // Oracle 12c用ANSI标准的OFFSET/FETCH语法（比老的ROWNUM更简洁）
    finalSql = String.format("SELECT * FROM (%s) t ORDER BY name ASC OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", unionSql, startRow, pageSize);
} else if (dialect instanceof PostgreSQLDialect) {
    // PostgreSQL用LIMIT/OFFSET，注意顺序：先LIMIT再OFFSET
    finalSql = String.format("%s ORDER BY name ASC LIMIT %d OFFSET %d", unionSql, pageSize, startRow);
} else {
    throw new UnsupportedOperationException("当前数据库方言未支持");
}

// 执行查询
TypedQuery<MyDto> query = entityManager.createNativeQuery(finalSql, MyDto.class);
List<MyDto> result = query.getResultList();

注意点 #

• 排序逻辑一定要放在分页之前，不然会导致分页结果混乱
• Oracle 12c之前的版本用ROWNUM，但你用的是12c，优先用OFFSET/FETCH（符合ANSI标准，和PostgreSQL的语法更接近）
• 避免直接用数据库名称判断（比如通过URL识别），用方言判断更可靠

内容的提问来源于stack exchange，提问作者Renaud is Not Bill Gates