myBatis-plus page分页插件的排序无效(failed to concat orderBy from IPage, exception=null)

主要原因是 jsqlparser 解析sql时出错,抛出异常 mybatis-plus捕获 记录了failed to concat orderBy from IPage, exception=null

logger.warn("failed to concat orderBy from IPage, exception=" + e.getMessage());

请检查sql中是否有关键字未用 ` `, 可以使用 CCJSqlParserUtil.parse(sql);来判断sql是否可以被解析.

本人遇到的问题: 原先的sql没啥问题,但是新需求是添加一个字段,从json中取出图片路径.就使用了mysql json函数

SELECT s.id,
               s.template_id,
               s.`name`,
               s.type_id,
               s.publish,
               s.publish_time,
               s.`status`,
               s.created_id,
               s.created_time,
               s.update_id,
               s.update_time,
               s.deleted,
               IFNULL(count(scd.id), 0) as heat,
               JSON_UNQUOTE(
                       json_extract (
                               s.`data`,
                               CONCAT( REPLACE ( JSON_UNQUOTE ( json_search ( s.`data`, 'one', 'headerBanner') ), 'type', '' ), IF(#{params.imgTypeId} = 2 , 'data.mobile','data.web'))
                           )
               ) as imgPath
        FROM `subject` s
                 inner join subject_activity_type sat on sat.id = s.type_id
                 left join subject_click_detail scd on scd.subject_id = s.id and scd.deleted = 0
                 ${ew.customSqlSegment}

然后排序就无法使用了

排查发现,新加的函数无法被正确解析,直接抛出 ParseException

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
    at line 14, column 28.

Was expecting one of:

    "&"
    ","
    "."
   // ... 省略
    <S_IDENTIFIER>
    <S_QUOTED_IDENTIFIER>

遂发现jsqlparser无法解析json函数,尝试升级jsqlparser版本后解决问题

            <dependency>
                <groupId>com.github.jsqlparser</groupId>
                <artifactId>jsqlparser</artifactId>
                <version>3.1</version>
            </dependency>