问题
mybatis-plus使用 PaginationInnerInterceptor 分页插件,在调用分页查询方法时(****Service.page(new Page(param.getPage(),param.getPageSize()),queryWrapper) )报如下错误
2022-07-22 17:07:20.699 [TID: N/A] WARN 12444 --- [io-18080-exec-1] c.b.m.e.p.i.PaginationInnerInterceptor : optimize this sql to a count sql has exception, sql:"sql语句略", exception:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "," ","
at line 1, column 191.
Was expecting one of:
"&"
"::"
";"
"<<"
">>"
"ACTION"
"ACTIVE"
"ALGORITHM"
"ARCHIVE"
"ARRAY"
***略****
原因
分页插件在处理count时会默认进行sql优化,优化失败会抛出异常。无法优化的sql会降级到非优化的count方法。
代码摘要如下:
其中 Select select = (Select) CCJSqlParserUtil.parse(sql);这行代码报错了
protected String autoCountSql(IPage<?> page, String sql) {
if (!page.optimizeCountSql()) {
return lowLevelCountSql(sql);
}
try {
Select select = (Select) CCJSqlParserUtil.parse(sql);
**************优化逻辑略*********************
return select.toString();
} catch (JSQLParserException e) {
// 无法优化使用原 SQL
logger.warn("optimize this sql to a count sql has exception, sql:\"" + sql + "\", exception:\n" + e.getCause());
} catch (Exception e) {
logger.warn("optimize this sql to a count sql has error, sql:\"" + sql + "\", exception:\n" + e);
}
return lowLevelCountSql(sql);
}
解决办法
解决思路:既然优化报错,那就别优化了。
上边代码方法开头有如下判断:
if (!page.optimizeCountSql()) {
return lowLevelCountSql(sql);
}
只要把 optimizeCountSql 设置为false就可以了,如下
Page page = new Page(param.getPage(),param.getPageSize()),queryWrapper)
page.setOptimizeCountSql(false);
也可以重新定义一个类继承Page,设置optimizeCountSql 默认=false