Spring Boot中MyBatis SQL Builder实现Oracle11.2分页（无LIMIT&OFFSET）

问题背景 #

Oracle 11.2不支持LIMIT和OFFSET语法，必须通过ROWNUM嵌套查询实现分页，目标SQL结构如下：

SELECT * FROM   
(SELECT te.*, ROWNUM AS rn FROM TEST_EVENT te ORDER BY te.EVENT_ID)
a WHERE a.rn >= #{resultFrom} AND a.rn <= #{resultTo}

但使用MyBatis SQL Builder编写嵌套查询时出现编译错误，现有代码无法正确生成嵌套SQL结构。

错误代码分析 #

你尝试的SQL Builder写法存在语法逻辑问题：FROM()方法内不能直接嵌套完整的SQL构建代码块，需要先单独构建内层查询语句，再将其作为参数传入外层的FROM()方法中。

正确实现方案 #

方案1：使用MyBatis Dynamic SQL构建嵌套查询 #

利用MyBatis Dynamic SQL的子查询能力，先构建带动态过滤和排序的内层查询，再在外层包装分页逻辑：

public String selectTestEventsFromDate(Long dateFrom, int resultFrom, int resultTo) {
    // 构建内层查询：包含动态条件、排序和ROWNUM赋值
    SelectStatementProvider innerQuery = select(
            column("te.*"),
            column("ROWNUM AS rn")
        )
        .from(table("TEST_EVENT").as("te"))
        .where(column("te.TIMESTMP"), isGreaterThanOrEqualToWhenPresent(dateFrom))
        .orderBy(column("te.EVENT_ID"))
        .build()
        .render(RenderingStrategy.MYBATIS3);

    // 构建外层分页查询：基于内层结果过滤ROWNUM范围
    return select(column("*"))
        .from(sql("(" + innerQuery.getSql() + ") a"))
        .where(column("a.rn"), isGreaterThanOrEqualTo(resultFrom))
        .and(column("a.rn"), isLessThanOrEqualTo(resultTo))
        .build()
        .render(RenderingStrategy.MYBATIS3);
}

方案2：字符串拼接简化实现 #

如果觉得Dynamic SQL过于繁琐，也可以直接拼接SQL字符串，同时保留动态条件逻辑：

public String selectTestEventsFromDate(Long dateFrom, int resultFrom, int resultTo) {
    StringBuilder innerSql = new StringBuilder();
    innerSql.append("SELECT te.*, ROWNUM AS rn FROM TEST_EVENT te");
    
    // 动态添加日期过滤条件
    if (dateFrom != null) {
        innerSql.append(" WHERE te.TIMESTMP >= #{dateFrom}");
    }
    innerSql.append(" ORDER BY te.EVENT_ID");
    
    // 构建外层分页查询
    return new SQL() {{
        SELECT("*");
        FROM("(" + innerSql + ") a");
        WHERE("a.rn >= #{resultFrom} AND a.rn <= #{resultTo}");
    }}.toString();
}

关键注意事项 #

• 内层查询必须先排序再生成ROWNUM，否则分页结果会混乱（Oracle的ROWNUM是在结果集生成后赋值的，未排序直接使用会导致分页数据逻辑错误）。
• 确保MyBatis Dynamic SQL依赖版本与MyBatis版本兼容，你pom.xml中1.3.0版本可正常使用，如需更简洁的API可考虑升级到最新稳定版。
• Mapper接口参数需与SQL中的占位符一一对应，避免参数绑定错误，你的现有Mapper代码无需修改：

@SelectProvider(type = testEventSqlBuilder.class, method = "selectTestEventsFromDate")
ArrayList<TestEventEntity> selectTestEventsProvider(Long dateFrom, int resultFrom, int resultTo);

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