【解决】SQL中包含问号，导致Mybatis解析错误

存在问题

今天在使用 mybatis 处理动态 sql （PostgreSQL 数据库）的时候遇到了一个问题：
sql语句中本身包含 ? 的时候 mybatis 解析参数会把 sql语句中的 ?当作占位符 给解析成变量 导致sql查询失败
案例如下：
我这里存储的是jsonb类型数据 大概如下：

1
2
3
4
5
6
7
{
    "str": "param",
    "arr": [
        "param1",
        "param2"
    ]
}

我的需求是：想要获取json列的数据中数组arr中包含param1的数据 得到的sql如下：

1
2
3
SELECT *
FROM your_table
WHERE tags->'arr' ?| ARRAY['paam1'];

这个sql在我们的sql连接器中执行是完全没问题的，但是在mybatis中使用这个sql的时候?|中的?会被mybatis当作占位符 替换成参数
例子如下：

1
2
3
4
// 这里是mapper接口
public interface testMapper {
    List<User> getUser(@Param("sysId") String sysId);
}
1
2
3
4
5
6
<!--这里是xml文件-->
<select id="getUser" resultType="User">
        SELECT *
        FROM user
        WHERE tags -> 'arr' ?| ARRAY[#{sysId}]
</select>

假如我传入的sysId是aaa那么最后我们查询出来的sql就会变成：

1
SELECT * FROM user WHERE tags -> 'arr' 'aaa'| ARRAY[?]

同时代码报错：

1
2
3
4
5
6
7
8
org.springframework.dao.DataIntegrityViolationException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
### The error may exist in file [D:\ffcs-progect\back\gops-timing-calculation\target\classes\mybatis\AnalyNetElementMapper.xml]
### The error may involve com.ffcs.iod.app.modules.analy.mapper.NetElementMapper.test-Inline
### The error occurred while setting parameters
### SQL: SELECT *         FROM user         WHERE tags -> 'arr' ?| ARRAY[?]
### Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
; 未设定参数值 2 的内容。; nested exception is org.postgresql.util.PSQLException: 未设定参数值 2 的内容。

由上可以看到?|运算符的?被替换了 而不是 我们参数 ，参数的位置还是空的。下面给出两种我测试都可以的方案，但是个人比较推荐第二种方案。

解决方案一

将?| 写成??|也就是在问号的前面多加一个问号，这样就能解决了。
例如：

1
2
3
4
5
<select id="getUser" resultType="User">
        SELECT *
        FROM user
        WHERE tags -> 'arr' ??| ARRAY[#{sysId}]
</select>

这种方法虽然在解析的时候还是会显示如下sql：

1
SELECT * FROM user WHERE tags -> 'arr' 'aaa'?| ARRAY[?]

但是代码是没有报错的，同时也可以查询出来数据。

解决方案二

使用pg数据库内置函数jsonb_exists_any()，这个方法比较推荐

1
2
3
4
5
<select id="getUser" resultType="User">
        SELECT *
        FROM user
        WHERE jsonb_exists_any(tags -> 'arr', ARRAY[#{sysId}])
</select>