前言
PageHelper是一个很好用的分页插件,在这个插件中使用分页会执行两句sql
1、count语句的sql,因为分页需要总数
2、分页语句,使用分页参数进行服务端分页的sql
分析
我们在调用分页方法时正常会调用
PageHelper.startPage(1,10);
但是如果我们不需要分页总数时可以关闭count的sql语句查询,使用重载的方法即可
/**
* 开始分页
*
* @param pageNum 页码
* @param pageSize 每页显示数量
*/
public static <E> Page<E> startPage(int pageNum, int pageSize) {
return startPage(pageNum, pageSize, DEFAULT_COUNT);
}
/**
* 开始分页
*
* @param pageNum 页码
* @param pageSize 每页显示数量
* @param count 是否进行count查询
*/
public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count) {
return startPage(pageNum, pageSize, count, null, null);
}
调用
PageHelper.startPage(1,10,false);
本文要讲的是在PageHelper里面执行count时会对sql语句进行优化,而这个解析的过程中可能会存在一些解析失败的情况,导致sql语句报错
例如执行
select a.* from (table1 a,table2 b) where a.id = b.id;
正常情况下我们会觉得count的语句会是这样的
select count(*) from (select a.* from (table1 a,table2 b) where a.id = b.id) table;
但是实际上PageHelper会对count语句做一个优化
在我们报错的日志看sql是这样的
select count(0) from (select a.* from (table1 a table2 b) where a.id = b.id) table;
我们会发现table1 a,table2 b之间的逗号不见了,这就是解析失败的场景
那么如何才能关闭sql语句的解析优化呢,我们从源码开始了解下
源码
我们从PageHelper的核心开始看,先看PageInterceptor类,这个类是使用的Mybatis插件来实现的,插件机制这里不做分析,可以看这篇
@Intercepts(
{
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
}
)
public class PageInterceptor implements Interceptor {
private volatile Dialect dialect;
private String countSuffix = "_COUNT";
protected Cache<String, MappedStatement> msCountMap = null;
private String default_dialect_class = "com.github.pagehelper.PageHelper";
@Override
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) {
//4 个参数时
boundSql = ms.getBoundSql(parameter);
cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
} else {
//6 个参数时
cacheKey = (CacheKey) args[4];
boundSql = (BoundSql) args[5];
}
checkDialectExists();
List resultList;
//调用方法判断是否需要进行分页,如果不需要,直接返回结果
if (!dialect.skip(ms, parameter, rowBounds)) {
//判断是否需要进行 count 查询
if (dialect.beforeCount(ms, parameter, rowBounds)) {
//查询总数
// 核心代码
Long count = count(executor, ms, parameter, rowBounds, resultHandler, boundSql);
//处理查询总数,返回 true 时继续分页查询,false 时直接返回
if (!dialect.afterCount(count, parameter, rowBounds)) {
//当查询总数为 0 时,直接返回空的结果
return dialect.afterPage(new ArrayList(), parameter, rowBounds);
}
}
resultList = ExecutorUtil.pageQuery(dialect, executor,
ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
} else {
//rowBounds用参数值,不使用分页插件处理时,仍然支持默认的内存分页
resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
}
return dialect.afterPage(resultList, parameter, rowBounds);
} finally {
dialect.afterAll();
}
}
看核心代码处的count方法
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 {
countMs = msCountMap.get(countMsId);
//自动创建
if (countMs == null) {
//根据当前的 ms 创建一个返回值为 Long 类型的 ms
countMs = MSUtils.newCountMappedStatement(ms, countMsId);
msCountMap.put(countMsId, countMs);
}
// 核心代码
count = ExecutorUtil.executeAutoCount(dialect, executor, countMs, parameter, boundSql, rowBounds, resultHandler);
}
return count;
}
看executeAutoCount方法
public static Long executeAutoCount(Dialect dialect, Executor executor, MappedStatement countMs,
Object parameter, BoundSql boundSql,
RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
Map<String, Object> additionalParameters = getAdditionalParameter(boundSql);
//创建 count 查询的缓存 key
CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
//调用方言获取 count sql
// 核心代码
String countSql = dialect.getCountSql(countMs, boundSql, parameter, rowBounds, countKey);
//countKey.update(countSql);
BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
//当使用动态 SQL 时,可能会产生临时的参数,这些参数需要手动设置到新的 BoundSql 中
for (String key : additionalParameters.keySet()) {
countBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
}
//执行 count 查询
Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
Long count = (Long) ((List) countResultList).get(0);
return count;
}
看getCountSql方法,到了PageHelper的getCountSql
@Override
public String getCountSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey countKey) {
return autoDialect.getDelegate().getCountSql(ms, boundSql, parameterObject, rowBounds, countKey);
}
AbstractHelperDialect的getCountSql方法
public String getCountSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey countKey) {
Page<Object> page = getLocalPage();
String countColumn = page.getCountColumn();
if (StringUtil.isNotEmpty(countColumn)) {
return countSqlParser.getSmartCountSql(boundSql.getSql(), countColumn);
}
// 核心代码
return countSqlParser.getSmartCountSql(boundSql.getSql());
}
调用了countSqlParser.getSmartCountSql
public String getSmartCountSql(String sql) {
return getSmartCountSql(sql, "0");
}
public String getSmartCountSql(String sql, String name) {
//解析SQL
Statement stmt = null;
//特殊sql不需要去掉order by时,使用注释前缀
// 核心代码
if(sql.indexOf(KEEP_ORDERBY) >= 0){
return getSimpleCountSql(sql);
}
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (Throwable e) {
//无法解析的用一般方法返回count语句
return getSimpleCountSql(sql);
}
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
try {
//处理body-去order by
processSelectBody(selectBody);
} catch (Exception e) {
//当 sql 包含 group by 时,不去除 order by
return getSimpleCountSql(sql);
}
//处理with-去order by
processWithItemsList(select.getWithItemsList());
//处理为count查询
sqlToCount(select, name);
String result = select.toString();
return result;
}
看到了上面的核心代码
if(sql.indexOf(KEEP_ORDERBY) >= 0){
return getSimpleCountSql(sql);
}
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (Throwable e) {
//无法解析的用一般方法返回count语句
return getSimpleCountSql(sql);
}
KEEP_ORDERBY定义
public static final String KEEP_ORDERBY = "/*keep orderby*/";
也就是当sql中包含/*keep orderby*/
字符串时会走getSimpleCountSql
public String getSimpleCountSql(final String sql, String name) {
StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
stringBuilder.append("select count(");
stringBuilder.append(name);
stringBuilder.append(") from (");
stringBuilder.append(sql);
stringBuilder.append(") tmp_count");
return stringBuilder.toString();
}
getSimpleCountSql也就是简单的对sql做了个count的拼接
而另外的情况就会调用CCJSqlParserUtil.parse(sql),CCJSqlParserUtil类是jsqlparser-1.2.jar的一个工具类,用于对sql进行解析的,也就是会把我们的sql做一个优化解析。
但是例如我们上述这种场景,我们的sql在mysql服务端是可以正常执行的,但是被优化后就不能正常执行了,这个时候我们只要在我们的sql中加上/*keep orderby*/
即可使用简单的count拼接了
总结
PageHelper在解析优化count语句时可能不能理解我们的语义,导致解析后不是我们需要的sql,这个时候我们就只能想办法关闭解析优化了
/*keep orderby*/
是一个注释,实际上不会对我们的sql执行造成影响,并且这种方法也不是PageHelper提供给我们的关闭count语句优化的方法,但是基于源码,我们这样做确实可以解决问题
可以看到PageHelper的源码看起来还是相对简单的,并且注释也是中文的,看起来并不费劲。这里就是对自己使用过程中出现问题解决方案的一个总结了