記一次 PageHelper 分頁未生效問題排查
作者：raledong
來源：SegmentFault 思否社區(qū)
問題描述
最近在項(xiàng)目中使用PageHelper分頁工具+Mybatis實(shí)現(xiàn)分頁查詢邏輯，但是發(fā)現(xiàn)分頁邏輯并沒有生效，代碼片段如下：
public PageDTO<ChargeMetaDO> pageByParams(SearchChargeReq searchChargeRequest, int pageNo, int pageSize) {
PageHelper.startPage(pageNo, pageSize);
ChargePOExample example = buildExample(searchChargeRequest);
long total = chargeMapper.countByExample(example);
List<ChargeMetaDO> chargeMetaDoList = selectByExample(example);
return new PageDTO<>(total, chargeMetaDoList);
}
這段代碼中傳入了查詢參數(shù)和分頁信息，并返回總頁數(shù)和當(dāng)前頁號(hào)的數(shù)據(jù)。但是實(shí)際執(zhí)行的時(shí)候返回了全部的數(shù)據(jù)。
修復(fù)方式
排查的時(shí)候發(fā)現(xiàn)，count代碼通過mybatis生成的sql語句中包含了分頁參數(shù)，但是select語句卻沒有，因此將查詢數(shù)據(jù)列表的請求放在計(jì)算總數(shù)據(jù)行數(shù)前面即可解決這個(gè)問題。
public PageDTO<ChargeMetaDO> pageByParams(SearchChargeReq searchChargeRequest, int pageNo, int pageSize) {
PageHelper.startPage(pageNo, pageSize);
ChargePOExample example = buildExample(searchChargeRequest);
// 需要先執(zhí)行分頁查詢，再查詢總數(shù)
List<ChargeMetaDO> chargeMetaDoList = selectByExample(example);
long total = chargeMapper.countByExample(example);
return new PageDTO<>(total, chargeMetaDoList);
}
原因分析
這里閱讀了一下PageHepler，簡單介紹一下PageHelper是如何將分頁信息塞入當(dāng)查詢請求中的。
在調(diào)用PageHelper.startPage方法后最終會(huì)進(jìn)入這段邏輯：
protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal();
public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
Page<E> page = new Page(pageNum, pageSize, count);
page.setReasonable(reasonable);
page.setPageSizeZero(pageSizeZero);
Page<E> oldPage = getLocalPage();
if (oldPage != null && oldPage.isOrderByOnly()) {
page.setOrderBy(oldPage.getOrderBy());
}
setLocalPage(page);
return page;
}
protected static void setLocalPage(Page page) {
LOCAL_PAGE.set(page);
}
這里要關(guān)注setLocalPage這個(gè)方法，這一行代碼將分頁的信息放入了當(dāng)前線程上下文LOCAL_PAGE中，使得后續(xù)的同線程內(nèi)查詢可以從該變量中取到分頁信息。
那么這個(gè)分頁信息是在哪里被編入到SQL的呢？PageHelper工具實(shí)現(xiàn)了一個(gè)mybatis的攔截器PageInterceptor，在請求經(jīng)過該攔截器時(shí)會(huì)讀取LOCAL_PAGE中的分頁信息并寫入到SQL中。這里通過Dialect接口進(jìn)行了抽象，Dialect接口定義了在經(jīng)過該切面時(shí)的各種行為（如是否跳過，執(zhí)行前操作，執(zhí)行后操作等），并子類PageHelper實(shí)現(xiàn)分頁邏輯。
public class PageInterceptor implements Interceptor {
public Object intercept(Invocation invocation) throws Throwable {
try {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement)args[0];
Object parameter = args[1];
RowBounds rowBounds = (RowBounds)args[2];
ResultHandler resultHandler = (ResultHandler)args[3];
Executor executor = (Executor)invocation.getTarget();
CacheKey cacheKey;
BoundSql boundSql;
if (args.length == 4) {
boundSql = ms.getBoundSql(parameter);
cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
} else {
cacheKey = (CacheKey)args[4];
boundSql = (BoundSql)args[5];
}
this.checkDialectExists();
List resultList;
// 需要跳過該Dialect
if (!this.dialect.skip(ms, parameter, rowBounds)) {
// 是否要執(zhí)行count操作
if (this.dialect.beforeCount(ms, parameter, rowBounds)) {
// 計(jì)算數(shù)據(jù)總量
Long count = this.count(executor, ms, parameter, rowBounds, resultHandler, boundSql);
// 判斷是否需要執(zhí)行count后的邏輯
if (!this.dialect.afterCount(count, parameter, rowBounds)) {
// 無需執(zhí)行分頁，直接返回
Object var12 = this.dialect.afterPage(new ArrayList(), parameter, rowBounds);
return var12;
}
}
// 分頁查詢數(shù)據(jù)
resultList = ExecutorUtil.pageQuery(this.dialect, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
} else {
// rowBounds用參數(shù)值，不使用分頁插件處理
resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
}
// 分頁結(jié)束，返回結(jié)果
Object var16 = this.dialect.afterPage(resultList, parameter, rowBounds);
return var16;
} finally {
// 分頁操作的收尾工作
if (this.dialect != null) {
this.dialect.afterAll();
}
}
}
private Long count(Executor executor, MappedStatement ms, Object parameter,
RowBounds rowBounds, ResultHandler resultHandler,
BoundSql boundSql) throws SQLException {
String countMsId = ms.getId() + countSuffix;
Long count;
//先判斷是否存在手寫的 count 查詢
MappedStatement countMs = ExecutorUtil.getExistedMappedStatement(ms.getConfiguration(), countMsId);
if (countMs != null) {
count = ExecutorUtil.executeManualCount(executor, countMs, parameter, boundSql, resultHandler);
} else {
// 優(yōu)先獲取緩存的SQL語句，減少重新生成的成本
countMs = msCountMap.get(countMsId);
//自動(dòng)創(chuàng)建
if (countMs == null) {
//拼接count語句
countMs = MSUtils.newCountMappedStatement(ms, countMsId);
msCountMap.put(countMsId, countMs);
}
// 執(zhí)行count語句
count = ExecutorUtil.executeAutoCount(dialect, executor, countMs, parameter, boundSql, rowBounds, resultHandler);
}
return count;
}
}
上文中對(duì)關(guān)鍵行增加了注釋，可以看到intercept方法其實(shí)就是對(duì)Dialect的幾個(gè)方法進(jìn)行編排：
public interface Dialect {
/**
* 跳過 count 和 分頁查詢
*
* @param ms MappedStatement
* @param parameterObject 方法參數(shù)
* @param rowBounds 分頁參數(shù)
* @return true 跳過，返回默認(rèn)查詢結(jié)果，false 執(zhí)行分頁查詢
*/
boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds);
/**
* 執(zhí)行分頁前，返回 true 會(huì)進(jìn)行 count 查詢，false 會(huì)繼續(xù)下面的 beforePage 判斷
*
* @param ms MappedStatement
* @param parameterObject 方法參數(shù)
* @param rowBounds 分頁參數(shù)
* @return
*/
boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds);
/**
* 執(zhí)行完 count 查詢后
*
* @param count 查詢結(jié)果總數(shù)
* @param parameterObject 接口參數(shù)
* @param rowBounds 分頁參數(shù)
* @return true 繼續(xù)分頁查詢，false 直接返回
*/
boolean afterCount(long count, Object parameterObject, RowBounds rowBounds);
/**
* 執(zhí)行分頁前，返回 true 會(huì)進(jìn)行分頁查詢，false 會(huì)返回默認(rèn)查詢結(jié)果
*
* @param ms MappedStatement
* @param parameterObject 方法參數(shù)
* @param rowBounds 分頁參數(shù)
* @return
*/
boolean beforePage(MappedStatement ms, Object parameterObject, RowBounds rowBounds);
/**
* 生成分頁查詢 sql
*
* @param ms MappedStatement
* @param boundSql 綁定 SQL 對(duì)象
* @param parameterObject 方法參數(shù)
* @param rowBounds 分頁參數(shù)
* @param pageKey 分頁緩存 key
* @return
*/
String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey);
/**
* 分頁查詢后，處理分頁結(jié)果，攔截器中直接 return 該方法的返回值
*
* @param pageList 分頁查詢結(jié)果
* @param parameterObject 方法參數(shù)
* @param rowBounds 分頁參數(shù)
* @return
*/
Object afterPage(List pageList, Object parameterObject, RowBounds rowBounds);
/**
* 完成所有任務(wù)后
*/
void afterAll();
/**
* 設(shè)置參數(shù)
*
* @param properties 插件屬性
*/
void setProperties(Properties properties);
}
PageHelper中主要是對(duì)PageAutoDialect加了一層代理實(shí)現(xiàn)，二者的代碼如下：
public class PageHelper extends PageMethod implements Dialect {
private PageParams pageParams;
private PageAutoDialect autoDialect;
@Override
public boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
if (ms.getId().endsWith(MSUtils.COUNT)) {
throw new RuntimeException("在系統(tǒng)中發(fā)現(xiàn)了多個(gè)分頁插件，請檢查系統(tǒng)配置!");
}
// 獲取上下文中的分頁信息
Page page = pageParams.getPage(parameterObject, rowBounds);
if (page == null) {
// 上下文中無分頁信息，跳過分頁攔截器
return true;
} else {
//設(shè)置默認(rèn)的 count 列
if (StringUtil.isEmpty(page.getCountColumn())) {
page.setCountColumn(pageParams.getCountColumn());
}
autoDialect.initDelegateDialect(ms);
return false;
}
}
/**
* 代理模式
*/
@Override
public boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
return autoDialect.getDelegate().beforeCount(ms, parameterObject, rowBounds);
}
/**
* 代理模式
*/
@Override
public boolean afterCount(long count, Object parameterObject, RowBounds rowBounds) {
return autoDialect.getDelegate().afterCount(count, parameterObject, rowBounds);
}
@Override
public boolean beforePage(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
return autoDialect.getDelegate().beforePage(ms, parameterObject, rowBounds);
}
@Override
public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
return autoDialect.getDelegate().getPageSql(ms, boundSql, parameterObject, rowBounds, pageKey);
}
public String getPageSql(String sql, Page page, RowBounds rowBounds, CacheKey pageKey) {
return autoDialect.getDelegate().getPageSql(sql, page, pageKey);
}
@Override
public Object afterPage(List pageList, Object parameterObject, RowBounds rowBounds) {
//這個(gè)方法即使不分頁也會(huì)被執(zhí)行，所以要判斷 null
AbstractHelperDialect delegate = autoDialect.getDelegate();
if (delegate != null) {
return delegate.afterPage(pageList, parameterObject, rowBounds);
}
return pageList;
}
@Override
public void afterAll() {
AbstractHelperDialect delegate = autoDialect.getDelegate();
if (delegate != null) {
delegate.afterAll();
autoDialect.clearDelegate();
}
// 清理線程上下文中的分頁信息，防止影響后續(xù)的調(diào)用
clearPage();
}
}
public abstract class AbstractHelperDialect extends AbstractDialect implements Constant {
/**
* 獲取分頁參數(shù)
*
* @param <T>
* @return
*/
public <T> Page<T> getLocalPage() {
return PageHelper.getLocalPage();
}
@Override
public final boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
//該方法不會(huì)被調(diào)用
return true;
}
@Override
public boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
// page配置中要求count
Page page = getLocalPage();
return !page.isOrderByOnly() && page.isCount();
}
@Override
public boolean afterCount(long count, Object parameterObject, RowBounds rowBounds) {
Page page = getLocalPage();
page.setTotal(count);
if (rowBounds instanceof PageRowBounds) {
((PageRowBounds) rowBounds).setTotal(count);
}
//pageSize < 0 的時(shí)候，不執(zhí)行分頁查詢
//pageSize = 0 的時(shí)候，還需要執(zhí)行后續(xù)查詢，但是不會(huì)分頁
if (page.getPageSize() < 0) {
return false;
}
return count > ((page.getPageNum() - 1) * page.getPageSize());
}
@Override
public boolean beforePage(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
Page page = getLocalPage();
// 頁數(shù)大于0，需要進(jìn)行分頁查詢
if (page.isOrderByOnly() || page.getPageSize() > 0) {
return true;
}
return false;
}
// 該方法會(huì)在當(dāng)前SQL后面添加上分頁信息，需要查看MySqlDialect子類的實(shí)現(xiàn)
@Override
public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
String sql = boundSql.getSql();
Page page = getLocalPage();
//支持 order by
String orderBy = page.getOrderBy();
if (StringUtil.isNotEmpty(orderBy)) {
pageKey.update(orderBy);
sql = OrderByParser.converToOrderBySql(sql, orderBy);
}
if (page.isOrderByOnly()) {
return sql;
}
return getPageSql(sql, page, pageKey);
}
@Override
public Object afterPage(List pageList, Object parameterObject, RowBounds rowBounds) {
Page page = getLocalPage();
if (page == null) {
return pageList;
}
page.addAll(pageList);
if (!page.isCount()) {
page.setTotal(-1);
} else if ((page.getPageSizeZero() != null && page.getPageSizeZero()) && page.getPageSize() == 0) {
page.setTotal(pageList.size());
} else if(page.isOrderByOnly()){
page.setTotal(pageList.size());
}
return page;
}
@Override
public void afterAll() {
}
@Override
public String getPageSql(String sql, Page page, CacheKey pageKey) {
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
sqlBuilder.append(sql);
if (page.getStartRow() == 0) {
sqlBuilder.append(" LIMIT ? ");
} else {
sqlBuilder.append(" LIMIT ?, ? ");
}
return sqlBuilder.toString();
}
}
總結(jié)
PageHelper本質(zhì)上是通過線程上下文變量來實(shí)現(xiàn)，并且僅生效于設(shè)置分頁參數(shù)后的第一條SQL。因此需要先執(zhí)行分頁查詢，再執(zhí)行count。
評(píng)論
圖片
表情