MyBatis拦截器实现分页SQL自动转换

1. 对应简历段落

这篇文章对应简历中“基于 MyBatis 插件机制实现统一分页，减少业务 XML 重复分页 SQL，兼容 Oracle/MySQL 方言”的项目经历。简历可以写成：

设计并落地 MyBatis 分页拦截器，基于 MappedStatement 和 BoundSql 自动识别分页参数，按数据库方言改写分页 SQL 和 count SQL，统一处理排序白名单、参数绑定、插件顺序和慢 SQL 记录，降低遗留系统分页 XML 维护成本。

面试官会继续追问：为什么不用每个 XML 自己写分页？插件拦截 Executor.query 还是 StatementHandler.prepare？如何生成 count SQL？复杂 SQL、order by、group by、union 怎么处理？如何避免 SQL 注入？如何保证数据权限插件和分页插件顺序正确？分页参数从哪里来？如果查询很慢，是 count 慢还是数据页慢？

这类项目的重点不是“我会写拦截器”，而是你能不能把 SQL 改写这件事做成工程上可控的能力。分页插件一旦写错，影响的是所有列表查询；如果没有方言、白名单、测试和降级策略，很容易把一个便利功能变成生产风险。

2. 业务背景

保险运营系统中列表查询非常多，例如客户列表、保单列表、订单列表、回访工单、渠道人员、产品配置、佣金结算、批处理记录、异常流水等。早期 MyBatis XML 通常每个查询自己写分页：Oracle 使用双层 ROWNUM，MySQL 使用 limit offset,size，有些还要额外写一条 count SQL。随着业务迭代，问题越来越明显。

第一，重复代码多。每个列表都要写分页包装和 count，开发效率低，且容易出现排序和条件不一致。第二，数据库方言耦合。老系统如果从 Oracle 扩展到 MySQL，或者测试环境和生产环境数据库不同，XML 中硬编码方言会带来迁移成本。第三，权限条件难统一。机构、渠道、数据范围等条件如果散落在每个 XML 中，既容易漏，也难以审计。第四，分页性能参差不齐。有些查询先查全量再内存分页，有些 count 带复杂排序，有些深分页没有优化。第五，接口风格不统一。不同模块分页参数命名、页码从 0 还是 1 开始、最大页大小限制都不一致。

因此项目中可以通过 MyBatis 拦截器统一分页行为：业务 Mapper 只写基础查询 SQL，调用层传入 PageRequest，插件自动生成分页 SQL 和 count SQL，返回 PageResult 或把总数写回分页对象。这样不仅减少 XML 重复，也能把最大页大小、排序白名单、SQL 日志、方言适配和异常保护集中治理。

3. 核心原理

MyBatis 分页插件的核心是拦截 SQL 执行前的某个点，拿到原始 SQL、参数对象和分页请求，然后改写 SQL。

常见拦截点有两个。拦截 Executor.query 的好处是能拿到 MappedStatement、参数对象、RowBounds 和 ResultHandler，上下文完整，适合同时执行 count 查询和分页查询。拦截 StatementHandler.prepare 的好处是离 JDBC 更近，改写最终 SQL 比较直接，但如果要执行 count 查询，需要额外拿到 Executor 或连接，处理更绕。很多成熟分页插件选择 Executor 层或 StatementHandler 层都可以，关键是团队要清楚边界。

分页 SQL 改写依赖 BoundSql。MappedStatement#getBoundSql(parameter) 会根据动态 SQL 和入参生成最终 SQL、参数映射和附加参数。插件不能只看 XML 原文，因为 <if>、<foreach>、<where> 已经在生成 BoundSql 时被处理。改写 SQL 后，还要保证原参数映射仍然有效。如果新增了分页参数占位符，例如 MySQL 的 limit ?, ?，就需要扩展 parameterMappings 或采用直接拼接经过校验的数字。

count SQL 的生成更敏感。简单做法是 select count(1) from (原SQL) tmp_count。这种方式兼容性较好，但复杂 SQL 下性能可能不佳，且原 SQL 中的 order by 对 count 无意义。优化做法是解析 SQL AST，移除最外层 order by，再包 count；或者对简单单表查询生成更轻的 count。遗留系统中建议先采用保守可靠的子查询 count，再针对热点 SQL 做手工 count 或白名单优化。

方言决定分页语法。MySQL 常用 limit offset, size；Oracle 11g 常用 ROWNUM 双层包装；Oracle 12c 可以使用 offset ... rows fetch next ... rows only。分页插件要根据数据库类型选择方言，数据库类型可以来自配置、DataSource 元数据或项目环境变量。不要在每次查询时都获取数据库元数据，避免额外开销。

排序必须白名单。分页接口通常允许前端传排序字段，如果直接拼接 ${sort}，就有 SQL 注入风险。正确做法是业务层传入逻辑字段，如 policyNo、createdTime，插件或查询组件映射到安全列名，如 policy_no、created_time，排序方向只允许 asc 和 desc。

4. 项目落地

落地时先定义统一分页模型。PageRequest 包含页码、页大小、是否查询总数、排序字段、排序方向；PageResult 包含数据列表、总数、页码、页大小、是否还有下一页。页码建议统一从 1 开始，插件内部转换为 offset，并限制最大页大小，例如后台管理默认 20，最大 500，导出接口不要走分页插件。

第二步是约定分页参数传递方式。可以把 PageRequest 作为 Mapper 参数的一部分，也可以通过 ThreadLocal 在 Service 层开启分页。ThreadLocal 写法对 Mapper 侵入小，但容易出现忘记清理导致串请求，尤其在线程池复用场景中风险较高。更稳妥的是显式参数，或使用 try-finally 管理分页上下文。

第三步是实现方言接口。Dialect 提供 buildPageSql 和 buildCountSql 方法，不同数据库各自实现。项目初期只支持正在使用的数据库，避免为了通用性写出过度复杂的解析器。对于复杂 SQL，可以允许 Mapper 标注跳过自动 count 或指定手写 count statement。

第四步是处理插件顺序。数据权限插件、租户插件应该在分页插件之前完成 SQL 条件注入，否则 count 和分页查询可能统计的是未授权数据。SQL 日志插件最好记录最终 SQL，同时记录 statement id 和分页参数。插件顺序要通过配置明确，不要依赖偶然注册顺序。

第五步是验证。要准备单表查询、多表 join、动态条件、无 where、有 order by、group by、union、foreach in、日期参数、空条件、深分页等用例。每个用例验证数据页 SQL、count SQL、参数绑定和结果总数。核心列表还要对比旧 XML 分页结果，确保页码边界一致。

5. 关键代码或流程

分页请求模型：

public class PageRequest {
    private int pageNo = 1;
    private int pageSize = 20;
    private boolean count = true;
    private String sortKey;
    private String sortDirection = "desc";

    public long offset() {
        return (long) Math.max(pageNo - 1, 0) * limit();
    }

    public int limit() {
        return Math.min(Math.max(pageSize, 1), 500);
    }
}

方言接口：

public interface Dialect {
    String pageSql(String sql, long offset, int limit);
    String countSql(String sql);
}

public class MySqlDialect implements Dialect {
    public String pageSql(String sql, long offset, int limit) {
        return sql + " LIMIT " + offset + ", " + limit;
    }

    public String countSql(String sql) {
        return "SELECT COUNT(1) FROM (" + removeTrailingOrderBy(sql) + ") t_count";
    }
}

拦截 Executor.query 的简化示例：

@Intercepts({
    @Signature(type = Executor.class, method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class PageInterceptor implements Interceptor {

    private final Dialect dialect;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];
        PageRequest page = PageParamResolver.resolve(parameter);
        if (page == null) {
            return invocation.proceed();
        }

        BoundSql boundSql = ms.getBoundSql(parameter);
        String pageSql = dialect.pageSql(applySafeOrder(boundSql.getSql(), page),
            page.offset(), page.limit());

        MappedStatement pageMs = MappedStatementUtils.copyWithSql(ms, boundSql, pageSql);
        args[0] = pageMs;
        args[2] = RowBounds.DEFAULT;

        if (page.isCount()) {
            long total = queryCount(invocation, ms, parameter, boundSql);
            PageContext.setTotal(total);
        }
        return invocation.proceed();
    }
}

真实项目中 copyWithSql 要谨慎复制 MappedStatement 的 resultMaps、cache、timeout、statementType、keyGenerator 等属性，并处理 BoundSql 的 additionalParameters。很多分页插件 bug 都出在这里。

流程上建议是：解析分页参数、校验页大小和排序字段、生成 count SQL、执行 count、生成分页 SQL、替换 MappedStatement 或 BoundSql、执行原查询、封装 PageResult、记录 SQL 耗时。

6. 常见坑

第一个坑是直接拼接前端排序字段。排序字段必须白名单映射，排序方向只能二选一，否则分页插件会成为 SQL 注入入口。

第二个坑是 count SQL 简单粗暴移除所有 order by。子查询、窗口函数、字符串字面量中可能也包含类似文本，靠正则处理复杂 SQL 风险很高。保守方案是只移除最外层明确 order by，复杂 SQL 不优化。

第三个坑是 ThreadLocal 泄漏。分页上下文如果用 ThreadLocal，必须 try-finally 清理，否则线程复用后下一个请求可能被错误分页。

第四个坑是插件顺序错误。分页先执行，数据权限后执行，容易导致 count 统计和数据页不一致，甚至 SQL 语法错误。

第五个坑是深分页性能。limit 100000,20 或 Oracle 大 offset 都可能很慢，需要通过游标翻页、基于主键的 seek pagination 或业务限制解决。

第六个坑是把所有查询都自动 count。复杂报表 count 可能比数据页更慢，可以支持 count=false 或手写 count。

7. 面试追问

  1. 分页插件应该拦截 Executor 还是 StatementHandler？
  2. BoundSql 里有哪些关键信息？
  3. 为什么不能直接修改 SQL 字符串就完事？
  4. count SQL 怎么生成，复杂 SQL 怎么处理？
  5. Oracle 11g 和 MySQL 分页语法有什么差异？
  6. 如何防止排序字段 SQL 注入？
  7. 数据权限插件和分页插件谁先执行？
  8. ThreadLocal 分页参数有什么风险？
  9. 深分页怎么优化？
  10. 如何验证分页插件没有改变原查询语义？

8. 推荐回答

如果问“分页插件的核心实现思路”，可以回答：

我们基于 MyBatis 插件机制拦截查询执行，先从参数中解析统一的 PageRequest，再通过 MappedStatement 获取 BoundSql，也就是动态 SQL 处理后的最终 SQL。插件根据数据库方言生成 count SQL 和分页 SQL，执行 count 后把原查询改写为分页查询。实现时重点处理参数绑定、BoundSql 附加参数、排序白名单、最大页大小和插件顺序。

如果问“为什么插件顺序重要”，可以回答：

因为每个插件看到的 SQL 可能已经被前一个插件改过。数据权限应该先注入机构、渠道、用户范围等条件，分页插件再基于授权后的 SQL 生成 count 和分页 SQL。否则可能 count 统计未授权数据，或者分页后再追加 where 导致 SQL 语法错误。日志插件则要明确记录最终 SQL，方便排查。

如果问“count SQL 怎么优化”，可以回答：

默认用 select count(1) from (原SQL) t 保证语义正确，并尽量移除最外层 order by。对复杂 group by、union、报表类 SQL，不强行智能改写，可以允许手写 count statement 或关闭 count。热点列表再结合执行计划做专项优化，比如减少 join、使用覆盖索引、把深分页改为基于游标的翻页。

9. 延伸学习路线

第一阶段掌握 MyBatis 插件机制，理解 Executor、StatementHandler、BoundSql 和 MappedStatement。

第二阶段学习各数据库分页方言，尤其是 MySQL limit、Oracle ROWNUM、Oracle 12c offset fetch，以及 SQL Server 的 offset fetch。

第三阶段学习 SQL 解析和优化，了解 JSqlParser 这类工具的能力边界，但不要盲目依赖解析器处理所有生产 SQL。

第四阶段学习分页性能优化，包括索引、延迟关联、覆盖索引、seek pagination、限制最大页数和异步导出。

第五阶段结合数据权限、租户隔离、审计日志一起设计插件链。资深面试回答要体现你知道插件是全局能力，必须把正确性、安全性、性能和可观测性一起考虑。