SpringMVC 分頁攔截器實作：基於 MyBatis 的實體分頁方案

SpringMVC 分頁攔截器核心實作

本文展示一個基於 MyBatis 攔截器機制的實體分頁實作方案，透過攔截 StatementHandler.prepare() 方法，在 SQL 執行前動態改寫查詢語句，新增 LIMIT 子句實現 MySQL 實體分頁，並自動查詢總筆數計算總頁數。

核心攔截器類別

@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PageInterceptor implements Interceptor {
    private static final String DEFAULT_PAGE_SQL_ID = ".*Page$";
    private String pageSqlId = DEFAULT_PAGE_SQL_ID;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler handler = (StatementHandler) invocation.getTarget();
        MetaObject metaHandler = MetaObject.forObject(handler, 
            DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);

        // 解除代理鏈，取得原始目標物件
        while (metaHandler.hasGetter("h")) {
            Object obj = metaHandler.getValue("h");
            metaHandler = MetaObject.forObject(obj, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
        }
        while (metaHandler.hasGetter("target")) {
            Object obj = metaHandler.getValue("target");
            metaHandler = MetaObject.forObject(obj, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
        }

        Configuration config = (Configuration) metaHandler.getValue("delegate.configuration");
        Properties props = config.getVariables();
        if (props != null && StringUtils.isNotBlank(props.getProperty("pageSqlId"))) {
            pageSqlId = props.getProperty("pageSqlId");
        }

        MappedStatement ms = (MappedStatement) metaHandler.getValue("delegate.mappedStatement");
        // 僅攔截符合規則的 MappedStatement（預設以 Page 結尾）
        if (ms.getId().matches(pageSqlId)) {
            BoundSql boundSql = (BoundSql) metaHandler.getValue("delegate.boundSql");
            Object paramObj = boundSql.getParameterObject();
            if (paramObj == null) throw new NullPointerException("parameterObject is null!");
            
            @SuppressWarnings("unchecked")
            Map<String, Object> paramMap = (Map<String, Object>) paramObj;
            PageParameter page = (PageParameter) paramMap.get("0");
            String sql = boundSql.getSql();

            // 改寫 SQL：新增 LIMIT 子句
            String pageSql = buildPageSqlForMysql(sql, page);
            metaHandler.setValue("delegate.boundSql.sql", pageSql);

            // 關閉 MyBatis 記憶體分頁
            metaHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
            metaHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);

            Connection conn = (Connection) invocation.getArgs()[0];
            // 查詢總筆數並設定分頁參數
            setPageParameter(sql, conn, ms, boundSql, page);
        }
        return invocation.proceed();
    }

    // MySQL 分頁 SQL 建構
    public String buildPageSqlForMysql(String sql, PageParameter page) {
        StringBuilder sb = new StringBuilder(100);
        long offset = (page.getCurrentPage() - 1L) * page.getPageSize();
        sb.append(sql).append(" LIMIT ").append(offset).append(",").append(page.getPageSize());
        return sb.toString();
    }

    // 查詢總筆數並計算總頁數
    private void setPageParameter(String sql, Connection conn, MappedStatement ms, 
                                  BoundSql boundSql, PageParameter page) {
        String countSql = "SELECT COUNT(0) FROM (" + sql + ") AS total";
        try (PreparedStatement stmt = conn.prepareStatement(countSql)) {
            BoundSql countBS = new BoundSql(ms.getConfiguration(), countSql, 
                boundSql.getParameterMappings(), boundSql.getParameterObject());
            setParameters(stmt, ms, countBS, boundSql.getParameterObject());
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    int total = rs.getInt(1);
                    page.setTotalCount(total);
                    page.setTotalPage((total + page.getPageSize() - 1) / page.getPageSize());
                }
            }
        } catch (SQLException e) {
            logger.error("Ignore this exception", e);
        }
    }

    @Override
    public Object plugin(Object target) {
        return (target instanceof StatementHandler) ? Plugin.wrap(target, this) : target;
    }
    @Override public void setProperties(Properties properties) {}
}

分頁參數封裝類別

public class PageParameter {
    public static final int DEFAULT_PAGE_SIZE = 10;
    private int pageSize = DEFAULT_PAGE_SIZE;
    private int currentPage = 1;
    private int totalCount, totalPage, prePage, nextPage;
    // 建構子、Getter/Setter 略
}

MyBatis 設定檔註冊攔截器

<configuration>
    <plugins>
        <plugin interceptor="com.example.PageInterceptor"></plugin>
    </plugins>
</configuration>

使用範例

Map<String, Object> params = new HashMap<>();
PageParameter page = new PageParameter();
page.setCurrentPage(1);
params.put("0", page);
userService.findUserPage(params); // 對應 Mapper 中 id 以 Page 結尾的查詢

此方案優點在於非侵入式：業務程式碼無需修改，僅需在 Mapper 方法命名遵循慣例（以 Page 結尾）即可自動獲得分頁能力。缺點是依賴 MySQL LIMIT 語法，跨資料庫需擴充 buildPageSqlForMysql 方法。