How to Solve mybatis-plus Paging Plug-in PaginationInnerInterceptor error

Questions

mybatis-plus using PaginationInnerInterceptor paging plugin, when calling the paging query method (****Service.page(new Page(param.getPage(),param.getPageSize()),queryWrapper )) reports the following error:

 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" 
    ***略****

reason

The paging plugin will default to sql optimization when processing count, and will throw an exception if optimization fails. The sql that cannot be optimized will be downgraded to the non-optimized count method.
The code summary is as follows.
where Select select = (Select) CCJSqlParserUtil.parse(sql); This line of code reports error:

    protected String autoCountSql(IPage<?> page, String sql) {
        if (!page.optimizeCountSql()) {
            return lowLevelCountSql(sql);
        }
        try {
            Select select = (Select) CCJSqlParserUtil.parse(sql);
            **************Optimization Logic*********************
            return select.toString();
        } catch (JSQLParserException e) {
            // Unable to optimize the use of the original 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);
    }

Solution:

Add the following codes before your codes:

if (!page.optimizeCountSql()) {
return lowLevelCountSql(sql);
}

just set optimizeCountSql to false, as follows

Page page = new Page(param.getPage(),param.getPageSize()),queryWrapper)
page.setOptimizeCountSql(false);

You can also redefine a class to inherit Page, and set optimizeCountSql default to =false

 

Read More: