这里写自定义目录标题
先了解一下PageHelper的使用原理
参考原文链接:https://www.lovecto.cn/20180805/185.html
大概流程就是,实现了org.apache.ibatis.plugin包下的Interceptor接口,向mybatis里面注册了一个拦截器,该拦截器会在sql查询的时候进行拦截,主要看intercept方法
//调用方法判断是否需要进行分页,如果不需要,直接返回结果
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);
}
这个方法不难看出,判断是否需要进行count查询,是根据方法beforeCount判断
@Override
public boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
Page page = getLocalPage();
return !page.isOrderByOnly() && page.isCount();
}
这个方法里面就是根据ThreadLocal里面的Page对象做判断,这也是我们使用PageHelper的时候一定再写在dao方法前面的原因,原理大概就是这样,细节看别人的文章就行,下面来说一下我使用的时候遇到的一个大坑,就是上面intercept方法中的计算总条数的一个方法PageInterceptor.cuont()方法。
计算总条数遇到的坑
需求就是一个数据同步的接口,然后我测试环境写完接口之后,很顺畅吗,然后开开心心的上了生产环境,然后上产上调用接口的时候发现,纳尼,一个接口要5s,于是就是看日志找问题,不看不知道,一看就发现了一个,很奇怪的sql,如下
看到这个sql就都明白了,为什么查询count不直接cuont,而是把所有的数据都读到内存中然后再cuont呢,于是,去看代码,没问题啊,不就是这样用吗,骂骂咧咧的说垃圾框架,于是就是找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;
}
这个地方时pagehelper的另外一个技巧,可以自定义count语句查询,只用在dao层写一个cuont方法
,方法名称用原方法名加_COUNT就可以用自己写的count语句,因为我没有自定义,我就看自己生成的count语句哪里有问题,在ExecutorUtil中的executeAutoCount()方法中,有获取方言sql的方法。
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;
}
然后一路最终,来到了CountSqlParser类中的getSmartCountSql()方法中
/**
* 获取智能的countSql
*
* @param sql
* @param name 列名,默认 0
* @return
*/
public String getSmartCountSql(String sql, String name) {
//解析SQL
Statement stmt = null;
//特殊sql不需要去掉order by时,使用注释前缀
if(sql.indexOf(KEEP_ORDERBY) >= 0){
return getSimpleCountSql(sql, name);
}
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (Throwable e) {
//无法解析的用一般方法返回count语句
return getSimpleCountSql(sql, name);
}
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, name);
}
//处理with-去order by
processWithItemsList(select.getWithItemsList());
//处理为count查询
sqlToCount(select, name);
String result = select.toString();
return result;
}
这个方法里面发现,有一个getSimpleCountSql()方法,这个方法,就是自动给我生成的sql语句
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();
}
所以问题就大概的定位到了,于是在debug模式下,发现CCJSqlParserUtil.parse(sql);报错了,报错内容
Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: “.” “.”
at line 2, column 3.
Was expecting one of:
"&"
","
"::"
";"
"<<"
">>"
"ACTION"
"ANY"
"AS"
"BYTE"
"CASCADE"
"CAST"
"CHAR"
"COLLATE"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"CONNECT"
"DESCRIBE"
"DO"
"ENABLE"
"END"
"EXCEPT"
"EXTRACT"
"FIRST"
"FN"
"FOLLOWING"
"FOR"
"FROM"
"GROUP"
"HAVING"
"INDEX"
"INSERT"
"INTERSECT"
"INTERVAL"
"INTO"
"ISNULL"
"KEY"
"LAST"
"MATERIALIZED"
"MINUS"
"NO"
"NULLS"
"OPEN"
"ORDER"
"OVER"
"PARTITION"
"PATH"
"PERCENT"
"PRECISION"
"PRIMARY"
"PRIOR"
"RANGE"
"REPLACE"
"ROW"
"ROWS"
"SEPARATOR"
"SIBLINGS"
"START"
"TABLE"
"TEMP"
"TEMPORARY"
"TOP"
"TRUNCATE"
"TYPE"
"UNION"
"UNSIGNED"
"VALUE"
"VALUES"
"WHERE"
"XML"
"ZONE"
"^"
"|"
<EOF>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<S_CHAR_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:20872)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:20722)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:80)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:36)
at com.smk.js.action.manage.ManageDataAction.main(ManageDataAction.java:99)
最终定位到,是我的查询语句里面有一个START关键字,导致sql解析失败,于是总结出来的结论就是,数据库创建字段的时候还是要避免使用数据库关键字段,如果不可避免的使用了话,写sql的时候时候关键字一定要加上``号。