使用Hibernate批量查询Oracle数据时遇ORA-01002错误求助

问题场景 #

使用Hibernate 5.4.24.Final搭配Oracle 19c数据库，在分页拉取百万级记录（每次批量20000条）时，拉取到6万条后触发以下错误：

18:54:10,224 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-4) SQL Error: 1002, SQLState: 24000
18:54:10,224 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-4) ORA-01002: fetch out of sequence

分页查询代码如下：

List<Object[]> objectInMemList = new ArrayList<>();
final BigDecimal count = (BigDecimal) entityManager.createNativeQuery("SELECT COUNT (RP.id) AS count FROM TABLE1 RP INNER JOIN TABLE2 CS ON RP.ID = ?1 AND RP.CUSTOMER_ID = CS.ID ORDER BY RP.CREATION_DATE").setParameter(1, 123456).getSingleResult();

int numberOfRequests = count.intValue() < 20000 ? 1 : (int)Math.ceil(count.intValue() / 20000);

for(int req = 0; req < numberOfRequests; req++) {
    final Query query = entityManager.createNativeQuery("SELECT RP.ID AS id, RP.field1, CS.field2 AS field2, CS.field3 AS field3, RP.field2 AS rpfield2 FROM TABLE1 RP INNER JOIN TABLE2 CS ON RP.Id = ?1 AND RP.CUSTOMER_ID = CS.ID")
.setFirstResult(req*20000).setMaxResults(20000).setHint(QueryHints.READ_ONLY, true).setParameter(1, 123456);
    final List<Object[]> resultList = query.getResultList();
    objectInMemList.addAll(resultList);
}

注：采用分页是为了避免百万级记录查询超时，网上查到的错误相关信息均指向INSERT/UPDATE或存储过程，但本次为单线程SELECT查询，对错误原因存疑。

错误原因分析 #

ORA-01002本质是游标获取顺序异常，在该分页场景下触发的核心原因：

1. SQL逻辑不一致：count查询添加了ORDER BY RP.CREATION_DATE，但分页查询未添加，导致两次查询的结果集排序规则不同，分页偏移量匹配的记录出现偏差，触发游标异常；
2. Oracle高偏移量分页缺陷：Hibernate对Oracle的setFirstResult实现依赖嵌套ROWNUM查询，当偏移量过大（如6万）时，游标处理容易出现顺序混乱；
3. 资源未及时释放：高频率分页查询下，游标资源未及时释放堆积，也可能引发该异常。

解决步骤 #

1. 统一SQL排序规则 #

给分页查询添加与count查询一致的ORDER BY子句，确保两次查询的结果集排序逻辑完全匹配：

final Query query = entityManager.createNativeQuery("SELECT RP.ID AS id, RP.field1, CS.field2 AS field2, CS.field3 AS field3, RP.field2 AS rpfield2 FROM TABLE1 RP INNER JOIN TABLE2 CS ON RP.Id = ?1 AND RP.CUSTOMER_ID = CS.ID ORDER BY RP.CREATION_DATE")
.setFirstResult(req*20000).setMaxResults(20000).setHint(QueryHints.READ_ONLY, true).setParameter(1, 123456);

2. 替换高偏移量分页为键集分页 #

Oracle对高偏移量分页的支持较差，建议改用键集分页，通过上一页最后一条记录的唯一排序键（如CREATION_DATE+ID）来定位下一页起始位置，彻底避免偏移量问题：

List<Object[]> objectInMemList = new ArrayList<>();
Date lastCreationDate = null;
Long lastId = null;
int batchSize = 20000;

while (true) {
    StringBuilder sql = new StringBuilder("SELECT RP.ID AS id, RP.field1, CS.field2 AS field2, CS.field3 AS field3, RP.field2 AS rpfield2, RP.CREATION_DATE " +
            "FROM TABLE1 RP INNER JOIN TABLE2 CS ON RP.Id = ?1 AND RP.CUSTOMER_ID = CS.ID " +
            "WHERE 1=1 ");
    if (lastCreationDate != null && lastId != null) {
        sql.append("AND (RP.CREATION_DATE > ?2 OR (RP.CREATION_DATE = ?2 AND RP.ID > ?3)) ");
    }
    sql.append("ORDER BY RP.CREATION_DATE, RP.ID ").append("FETCH FIRST ").append(batchSize).append(" ROWS ONLY");

    Query query = entityManager.createNativeQuery(sql.toString())
            .setHint(QueryHints.READ_ONLY, true)
            .setParameter(1, 123456);
    if (lastCreationDate != null && lastId != null) {
        query.setParameter(2, lastCreationDate)
             .setParameter(3, lastId);
    }

    List<Object[]> resultList = query.getResultList();
    if (resultList.isEmpty()) {
        break;
    }
    objectInMemList.addAll(resultList);

    // 更新最后一条记录的排序键
    Object[] lastRow = resultList.get(resultList.size() - 1);
    lastId = (Long) lastRow[0];
    lastCreationDate = (Date) lastRow[5];
}

3. 显式释放游标资源（可选） #

每次分页查询后，调用flush()和clear()释放EntityManager的游标资源，避免资源堆积：

final List<Object[]> resultList = query.getResultList();
objectInMemList.addAll(resultList);
entityManager.flush();
entityManager.clear();

4. 配置正确的Oracle方言 #

确保Hibernate使用兼容Oracle 19c的方言，优化分页语法支持：

hibernate.dialect=org.hibernate.dialect.Oracle12cDialect

内容的提问来源于stack exchange，提问作者Dev