mybatis分页插件pagehelper，当sql中含有if时报错

男娘i 2023-08-17 16:48 179阅读 0赞

### 目录 ###

*  1 背景说明
 *  2 报错信息
 *  3 版本说明
 *  4 报错演示
 *  5解决办法
 *   *  5.1方案一：使用 case when代替if
     *  5.2方案二：把pagehelper升级到最新（最推荐）

# 1 背景说明 #

> 今天在使用mybatis的分页插件pagehelper时发现了一个问题，就是当sql中有 if(sum()条件,xx,xx) 的时候程序报错。我把sql直接拷贝出来执行是没问题的，弄了一个下午最终解决了。百度、谷歌了很久都没找到解决办法，最终一点点的实验，终于发现了问题所在。  
> 也希望有同样问题的人少走弯路

# 2 报错信息 #

*  我的代码（mybatis的mapper.xml文件）

<select id="queryAAAA" parameterType="java.util.Map" resultType="cc.xxx.xxx.entity.AAAAAAAA">
    	SELECT
    		a.time_id 'date_time',
    		SUM(a.calls) 'call_num',
    		SUM(a.ans_calls) 'talk_num',
    		IF(SUM(a.calls) = 0,0,SUM(a.ans_calls) / SUM(a.calls)) 'talk_rate'
    	FROM
    	agg_c_fact_agent_detail_hour a
    	<where>
    	    1=1
    	</where>
    	GROUP BY
    		a.time_id,
    		a.agent_no,
    		a.call_type
    </select>

*  错误日志

### Error querying database.  Cause: com.github.pagehelper.PageException: 处理排序失败: net.sf.jsqlparser.JSQLParserException
    ### The error may exist in file [D:\6Report\2.0\rpt-service\target\classes\mapper\Report.xml]
    ### The error may involve cc.wellcloud.rpt.dao.ReportDao.queryAgentOutbound_COUNT
    ### The error occurred while handling results
    ### SQL: select count(0) from (SELECT    a.time_id 'date_time',    SUM(a.calls) 'call_num',    SUM(a.ans_calls) 'talk_num',    IF(SUM(a.calls) = 0,0,SUM(a.ans_calls) / SUM(a.calls)) 'talk_rate'   FROM   agg_c_fact_agent_detail_hour a    WHERE 1=1           and a.time_id >= ?          and a.time_id <= ?                and a.tenant_id = ?    GROUP BY    a.time_id,    a.agent_no,    a.call_type) tmp_count
    ### Cause: com.github.pagehelper.PageException: 处理排序失败: net.sf.jsqlparser.JSQLParserException
    	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.4.6.jar:3.4.6]
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150) ~[mybatis-3.4.6.jar:3.4.6]
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.6.jar:3.4.6]
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
    	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.2.jar:1.3.2]
    	... 108 common frames omitted
    Caused by: com.github.pagehelper.PageException: 处理排序失败: net.sf.jsqlparser.JSQLParserException
    	at com.github.pagehelper.parser.OrderByParser.converToOrderBySql(OrderByParser.java:64) ~[pagehelper-5.1.8.jar:na]
    	at com.github.pagehelper.dialect.AbstractHelperDialect.getPageSql(AbstractHelperDialect.java:173) ~[pagehelper-5.1.8.jar:na]
    	at com.github.pagehelper.PageHelper.getPageSql(PageHelper.java:98) ~[pagehelper-5.1.8.jar:na]
    	at com.github.pagehelper.util.ExecutorUtil.pageQuery(ExecutorUtil.java:168) ~[pagehelper-5.1.8.jar:na]
    	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:104) ~[pagehelper-5.1.8.jar:na]
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.4.6.jar:3.4.6]
    	at com.sun.proxy.$Proxy128.query(Unknown Source) ~[na:na]
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.6.jar:3.4.6]
    	... 114 common frames omitted
    Caused by: net.sf.jsqlparser.JSQLParserException: null
    	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:55) ~[jsqlparser-1.2.jar:na]
    	at com.github.pagehelper.parser.OrderByParser.converToOrderBySql(OrderByParser.java:52) ~[pagehelper-5.1.8.jar:na]
    	... 121 common frames omitted
    Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "IF" "IF"
        at line 5, column 4.
    
    Was expecting one of:
    
        "*"
        "+"
        "-"
        "?"
        "@"
        "@@"
        "ACTION"
        "ANY"
        "CASCADE"
        "CASE"
        "CAST"
        "COLUMN"
        "COMMIT"
        "DO"
        "ENABLE"
        "END"
        "EXTRACT"
        "FIRST"
        "FOLLOWING"
        "GROUP_CONCAT"
        "INDEX"
        "INSERT"
        "INTERVAL"
        "KEY"
        "LAST"
        "MATERIALIZED"
        "NO"
        "NULL"
        "NULLS"
        "OPEN"
        "OVER"
        "PARTITION"
        "PERCENT"
        "PRECISION"
        "PRIMARY"
        "PRIOR"
        "RANGE"
        "REPLACE"
        "ROW"
        "ROWS"
        "SEPARATOR"
        "SIBLINGS"
        "TABLE"
        "TEMP"
        "TEMPORARY"
        "TRUNCATE"
        "UNSIGNED"
        "VALUE"
        "VALUES"
        "XML"
        "{d"
        "{t"
        "{ts"
        "~"
        <K_DATETIMELITERAL>
        <K_TIME_KEY_EXPR>
        <S_CHAR_LITERAL>
        <S_DOUBLE>
        <S_HEX>
        <S_IDENTIFIER>
        <S_LONG>
        <S_QUOTED_IDENTIFIER>
    
    	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:18047) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:17900) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.PrimaryExpression(CCJSqlParser.java:7394) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseXor(CCJSqlParser.java:7161) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.MultiplicativeExpression(CCJSqlParser.java:7119) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.AdditiveExpression(CCJSqlParser.java:7082) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseAndOr(CCJSqlParser.java:7028) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.ConcatExpression(CCJSqlParser.java:7004) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.SimpleExpression(CCJSqlParser.java:6997) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.SelectExpressionItem(CCJSqlParser.java:3377) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.SelectItem(CCJSqlParser.java:3523) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.SelectItemsList(CCJSqlParser.java:3368) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:2930) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:3107) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:2824) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:2817) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:133) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81) ~[jsqlparser-1.2.jar:na]
    	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:53) ~[jsqlparser-1.2.jar:na]
    	... 122 common frames omitted

# 3 版本说明 #

-mybatis版本
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.2</version>
    </dependency>
    
    - mybatis分页插件版本
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.2.10</version>
    </dependency>

# 4 报错演示 #

（1）经过多次尝试，考虑了不同情况，得到如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5kaXh1YW53dWxpYW5n_size_16_color_FFFFFF_t_70]

（2）得出结论：  
当且仅当 if() 的条件有sum()聚合函数时报错（其他的聚合函数还没试过）

# 5解决办法 #

## 5.1方案一：使用 case when代替if ##

例如：

CASE WHEN SUM(a.calls)=0 THEN 0 ELSE SUM(a.ans_calls) / SUM(a.calls) END 'talk_rate'

## 5.2方案二：把pagehelper升级到最新（最推荐） ##

*  （我后来才发现的，浪费一个下午时间，尴尬-\_-，这个应该是低版本的bug）  
    升级到如下版本：

<dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.2.12</version>
    </dependency>

*  方案二完美解决

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5kaXh1YW53dWxpYW5n_size_16_color_FFFFFF_t_70]: /images/20230809/29f52a4c18fa4420a02d52fb4f3d5212.png