MyBatis Plus 使用 PostgreSQL 数组报错

解决方案

postgreSQL Array Operators && cause problem #723

• 使用 JSqlParser 4.0
• 使用 MyBatis Plus 3.4.2，该版本引用了 JSqlParser 4.0。参考 MyBatis Plus » 3.4.2

本人尚未验证，欢迎反馈。

问题描述

问题重现

项目使用的是 PostgreSQL 11.3 和 Mybatis Plus 3.1.0（JSqlParser 1.4），建表使用了数组字段：

create table some_table
(
    -- omitted columns
    data_tags                 varchar(1000)[]                     not null
);

错误日志节选：

### The error may exist in file [/path/to/mapper.xml]
### The error may involve com.path.to.mapper
### The error occurred while executing a query
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
 Error SQL: select use_date from some_table
        where deleted = false
            and data_tags && array [
                   
                    ?
                 , 
                    ?
                 , 
                    ?
                 , 
                    ?
                 , 
                    ?
                  
                ]::varchar[]
        order by use_date desc
        limit 1
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy180.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:99)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61)
	at com.sun.proxy.$Proxy254.findLastMedicationTime(Unknown Source)

上面这条 SQL 是可以在 PostgreSQL 上运行的，但是使用 MyBatis Plus 却报错。

继续看错误日志：

Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
 Error SQL: select use_date from some_table
        where deleted = false
            and data_tags && array [
                   
                    ?
                 , 
                    ?
                 , 
                    ?
                 , 
                    ?
                 , 
                    ?
                  
                ]::varchar[]
        order by use_date desc
        limit 1
	at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39)
	at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:74)
	at com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler.sqlParser(AbstractSqlParserHandler.java:66)
	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:127)
	at com.yingzi.bizcenter.common.mybatis.interceptor.TenantPaginationInterceptor.intercept(TenantPaginationInterceptor.java:216)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy404.prepare(Unknown Source)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at sun.reflect.GeneratedMethodAccessor301.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy403.query(Unknown Source)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:108)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy403.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	... 57 common frames omitted
Caused by: net.sf.jsqlparser.JSQLParserException: null
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:154)
	at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:60)
	... 76 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "[" "["
    at line 3, column 35.

Was expecting one of:

    "&&"
    ";"
    "AND"
    "CONNECT"
    "EXCEPT"
    "FOR"
    "GROUP"
    "HAVING"
    "INTERSECT"
    "MINUS"
    "ORDER"
    "START"
    "UNION"
    <EOF>

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:19398)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:19248)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:547)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:152)
	... 77 common frames omitted

以上日志可以看出，报错的直接原因是 JSqlParser 解析 SQL 出错。

查找解决方案

到 https://github.com/JSQLParser/JSqlParser 搜索相关 issue，

https://github.com/JSQLParser/JSqlParser/issues?q=is%3Aissue+postgresql+array

找到一条问题比较接近的 issue：postgreSQL Array Operators && cause problem #723