MyBatis 分頁插件實戰：基於攔截器的通用物理分頁實現

方案選型：為什麼選擇攔截器模式？

• 無侵入：Mapper XML 與 Java 代碼無需修改分頁邏輯；
• 多資料庫支援：透過方言適配 MySQL/Oracle/PostgreSQL/SQL Server；
• 效能優於內存分頁：直接在 SQL 層面 LIMIT/ROWNUM，避免全量查詢。

架構圖

Controller → Service → Mapper (ListPage) 
                    ↑
            PagePlugin 攔截器
                    ↓
            動態改寫 SQL + 查總數

核心模組代碼

1. 分頁參數實體

public class PageInfo implements Serializable {
    private int showCount = 9;      // 頁大小
    private int totalResult;        // 總記錄數（查詢後回填）
    private int currentPage;        // 當前頁碼
    private int currentResult;      // 偏移量 (page-1)*size
    private String sortField;       // 排序欄位
    private String order;           // ASC/DESC
    // Getter/Setter 省略
}

2. MyBatis 配置註冊攔截器

<!DOCTYPE configuration ...>
<configuration>
    <typeAliases>
        <typeAlias type="com.ij34.model.Article" alias="Article"/>
        <typeAlias type="com.ij34.pages.PageInfo" alias="PageInfo"/>
    </typeAliases>
    <plugins>
        <plugin interceptor="com.ij34.pages.PagePlugin">
            <property name="dialect" value="mysql"/>
            <property name="pageSqlId" value=".*ListPage.*"/>  
        </plugin>
    </plugins>
</configuration>

3. 攔截器核心邏輯 (MyBatis 3.3.x 兼容)

@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}))
public class PagePlugin implements Interceptor {

    private String dialect;
    private String pageSqlId;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
        BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(handler, "delegate");
        MappedStatement ms = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement");

        // 僅攔截匹配 pageSqlId 的查詢
        if (ms.getId().matches(pageSqlId)) {
            BoundSql boundSql = delegate.getBoundSql();
            Object param = boundSql.getParameterObject();
            Connection conn = (Connection) invocation.getArgs()[0];

            // 1. 查總數
            String countSql = "SELECT COUNT(0) FROM (" + boundSql.getSql() + ") tmp_count";
            try (PreparedStatement ps = conn.prepareStatement(countSql)) {
                copyParameters(ps, ms, boundSql, param);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) {
                        PageInfo page = extractPage(param);
                        page.setTotalResult(rs.getInt(1));
                    }
                }
            }

            // 2. 改寫分頁 SQL
            String pageSql = buildPageSql(boundSql.getSql(), extractPage(param));
            ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql);
        }
        return invocation.proceed();
    }

    private String buildPageSql(String sql, PageInfo page) {
        if (page == null) return sql;
        if ("mysql".equals(dialect)) {
            return sql + " LIMIT " + page.getCurrentResult() + "," + page.getShowCount();
        } else if ("oracle".equals(dialect)) {
            return "SELECT * FROM (SELECT t.*, ROWNUM rn FROM (" + sql + ") t WHERE ROWNUM <= " 
                 + (page.getCurrentResult() + page.getShowCount()) + ") WHERE rn > " + page.getCurrentResult();
        }
        return sql;
    }
    // 參數複製、反射工具類略...
}

4. Mapper 介面

public interface UserMapper {
    List<Article> selectArticle(int id);           // 普通查詢
    List<Article> ListPage(@Param("page") PageInfo page, @Param("userid") int userid); // 分頁查詢
}

5. Controller 分頁參數組裝

@GetMapping("/pagelist")
public ModelAndView pageList(@RequestParam(defaultValue = "1") int page,
                             @RequestParam(defaultValue = "9") int size) {
    PageInfo pi = new PageInfo();
    pi.setShowCount(size);
    pi.setCurrentPage(page);
    pi.setCurrentResult((page - 1) * size);

    List<Article> list = mapper.ListPage(pi, 1);
    int totalPage = (pi.getTotalResult() + size - 1) / size;

    String nav = String.format(
        "<a href='?page=%d'>上一頁</a> <a href='?page=%d'>下一頁</a>",
        Math.max(1, page-1), Math.min(totalPage, page+1));

    return new ModelAndView("list")
        .addObject("articles", list)
        .addObject("pageStr", nav);
}

效能與避坑提醒

1. 總數查詢優化：大表建議維護計數表或使用 EXPLAIN 分析，避免 COUNT(*) 全表掃描；
2. 參數映射：攔截器需完整複製原 BoundSql 參數，否則 WHERE 條件失效；
3. 方言擴展：新增 PostgreSQL LIMIT ... OFFSET ...、SQL Server OFFSET ... ROWS FETCH NEXT ... 只需在 buildPageSql 增加分支；
4. MyBatis 3.4+：StatementHandler 結構變更，需遷移至 Plugin.wrap 新 API，建議直接升級 PageHelper 成熟插件。