設計目標
- 零侵入：Mapper XML 無需修改
- 多資料庫支援：MySQL/Oracle/PostgreSQL/SQL Server
- 高效能：物理分頁 (Limit/Offset/Rownum) 避免全表掃描
- 易擴充：方言策略模式
核心攔截器實作
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})
})
public class PaginationInterceptor implements Interceptor {
private static final String PAGE_PARAM = "page"; // 分頁參數鍵名
private Dialect dialect; // 方言策略
@Override
public Object intercept(Invocation invocation) throws Throwable {
if (invocation.getTarget() instanceof StatementHandler) {
StatementHandler stmtHandler = (StatementHandler) invocation.getTarget();
MetaObject meta = SystemMetaObject.forObject(stmtHandler);
MappedStatement ms = (MappedStatement) meta.getValue("delegate.mappedStatement");
// 1. 判斷是否需要分頁 (Mapper 方法名包含 page)
String msId = ms.getId();
String methodName = msId.substring(msId.lastIndexOf('.') + 1);
if (!methodName.toLowerCase().contains(PAGE_PARAM)) {
return invocation.proceed(); // 不攔截，直接執行
}
// 2. 取得原始 SQL 與參數
BoundSql boundSql = (BoundSql) meta.getValue("delegate.boundSql");
String originalSql = boundSql.getSql();
Object paramObj = boundSql.getParameterObject();
// 3. 解析 Page 物件 (支援 Map/POJO/單一參數)
Page page = extractPage(paramObj);
if (page == null) return invocation.proceed();
// 4. 查詢總筆數 (必要時)
if (page.isCountTotal()) {
long total = queryTotalCount(originalSql, boundSql, invocation);
page.setTotal(total);
}
// 5. 改寫分頁 SQL
String pageSql = dialect.buildPageSql(originalSql, page.getOffset(), page.getLimit());
meta.setValue("delegate.boundSql.sql", pageSql);
}
return invocation.proceed();
}
private Page extractPage(Object paramObj) {
if (paramObj instanceof Page) return (Page) paramObj;
if (paramObj instanceof Map) {
Map map = (Map) paramObj;
return (Page) map.get(PAGE_PARAM);
}
return null;
}
private long queryTotalCount(String sql, BoundSql boundSql, Invocation invocation) {
String countSql = dialect.buildCountSql(sql);
Connection conn = (Connection) invocation.getArgs()[0];
try (PreparedStatement ps = conn.prepareStatement(countSql)) {
// 參數綁定 (複製原 BoundSql 參數)
ParameterHandler handler = (ParameterHandler)
SystemMetaObject.forObject(boundSql).getValue("parameterHandler");
handler.setParameters(ps);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getLong(1) : 0;
}
}
}
@Override public Object plugin(Object target) { return Plugin.wrap(target, this); }
@Override public void setProperties(Properties properties) { /* 方言配置解析 */ }
}
方言策略介面與實作
public interface Dialect {
String buildPageSql(String originalSql, int offset, int limit);
String buildCountSql(String originalSql);
boolean supportsOffsetFetch(); // SQL:2008 標準
}
// MySQL / MariaDB / PostgreSQL / H2 / SQLite
public class LimitOffsetDialect implements Dialect {
@Override
public String buildPageSql(String sql, int offset, int limit) {
return sql + " LIMIT " + limit + " OFFSET " + offset;
}
@Override public String buildCountSql(String sql) {
return "SELECT COUNT(*) FROM (" + sql + ") AS tmp_count";
}
@Override public boolean supportsOffsetFetch() { return false; }
}
// Oracle 12c+ (OFFSET FETCH)
public class Oracle12cDialect implements Dialect {
@Override
public String buildPageSql(String sql, int offset, int limit) {
return sql + " OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
}
@Override public String buildCountSql(String sql) {
return "SELECT COUNT(*) FROM (" + sql + ")";
}
@Override public boolean supportsOffsetFetch() { return true; }
}
// Oracle 11g 及以下 (ROWNUM 子查詢)
public class Oracle11gDialect implements Dialect {
@Override
public String buildPageSql(String sql, int offset, int limit) {
int end = offset + limit;
return "SELECT * FROM (SELECT tmp.*, ROWNUM rn FROM (" + sql + ") tmp WHERE ROWNUM <= " + end + ") WHERE rn > " + offset;
}
@Override public String buildCountSql(String sql) {
return "SELECT COUNT(*) FROM (" + sql + ")";
}
@Override public boolean supportsOffsetFetch() { return false; }
}
// SQL Server 2012+ (OFFSET FETCH)
public class SqlServer2012Dialect implements Dialect {
@Override
public String buildPageSql(String sql, int offset, int limit) {
return sql + " ORDER BY (SELECT 0) OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
}
@Override public String buildCountSql(String sql) {
return "SELECT COUNT(*) FROM (" + sql + ") AS tmp";
}
@Override public boolean supportsOffsetFetch() { return true; }
}
分頁參數物件
public class Page implements Serializable {
private int pageNum = 1; // 當前頁 (1-based)
private int pageSize = 10; // 每頁筆數
private long total = 0; // 總筆數
private boolean countTotal = true; // 是否查總數
private String orderBy; // 排序欄位
private boolean asc = true; // 升序/降序
// 計算屬性
public int getOffset() { return (pageNum - 1) * pageSize; }
public int getLimit() { return pageSize; }
public int getPages() { return total == 0 ? 0 : (int)Math.ceil((double)total / pageSize); }
// 靜態工廠
public static Page of(int pageNum, int pageSize) {
Page p = new Page();
p.pageNum = Math.max(1, pageNum);
p.pageSize = Math.max(1, Math.min(pageSize, 500)); // 限制最大頁面
return p;
}
public static Page from(Map params) {
Page p = new Page();
p.pageNum = parseInt(params.get("pageNum"), 1);
p.pageSize = parseInt(params.get("pageSize"), 10);
p.orderBy = (String) params.get("orderBy");
p.asc = parseBoolean(params.get("asc"), true);
return p;
}
}
Controller 使用範例
@RestController
@RequestMapping("/employees")
public class EmployeeController {
@Autowired EmployeeService employeeService;
@GetMapping
public Result> list(
@RequestParam(defaultValue = "1") int pageNum,
@RequestParam(defaultValue = "10") int pageSize,
@RequestParam(required = false) String keyword,
@RequestParam(required = false) String orderBy,
@RequestParam(defaultValue = "true") boolean asc) {
// 1. 封裝查詢參數
Map params = new HashMap<>();
params.put("keyword", keyword);
params.put("page", Page.of(pageNum, pageSize));
params.put("orderBy", orderBy);
params.put("asc", asc);
// 2. 呼叫 Service (Mapper 方法名需包含 "page")
List list = employeeService.selectPage(params);
Page page = (Page) params.get("page");
// 3. 封裝回傳
return Result.success(new PageResult<>(list, page.getTotal(), page.getPageNum(), page.getPageSize()));
}
}
Mapper XML 定義
Spring 整合配置
@Configuration
@MapperScan("com.example.mapper")
public class MyBatisConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor interceptor = new PaginationInterceptor();
Properties props = new Properties();
props.setProperty("dialect", "mysql"); // mysql/oracle12c/oracle11g/sqlserver2012
interceptor.setProperties(props);
return interceptor;
}
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(dataSource);
factory.setPlugins(paginationInterceptor()); // 註冊攔截器
factory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath:mapper/*.xml"));
return factory.getObject();
}
}
進階優化技巧
| 優化點 | 實作策略 |
|---|
| Count 查詢優化 | 簡化 Count SQL：移除 ORDER BY、JOIN、SELECT * 只保留 WHERE |
| 總數快取 | Redis 快取總筆數，TTL 30-60 秒，減輕 DB 壓力 |
| 游標分頁 | 大數據量 (百萬+) 改用 Cursor/Seek 分頁：WHERE id > lastId LIMIT n |
| 合理化參數 | 限制 pageSize 最大值 (如 500)、pageNum 不得超過總頁數 |
| SQL 注入防護 | ${orderBy} 白名單驗證、禁止用戶直接控制排序欄位 |
