在postgresql的使用jsonb数据结构，存储的是一个json array，当查询使用包含某个字符串的时候，要用到 ? 操作符。
例如 select * from table where jsonb_column ? 'string'
结合mybatis的xml一起使用的时候，会误把 ? 操作符当作preparestatement的参数占位符，因此报错：
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
### The error may exist in User.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: seelct * from table where jsonb_column ? ?
### Cause: org.postgresql.util.PSQLException: 未设定参数值 2 的内容。
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:122)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:73)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:69)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
at com.sun.proxy.$Proxy2.getCount(Unknown Source)
at org.beiyu.study.mybatis.MyBatisTest.testMb(MyBatisTest.java:29)
at org.beiyu.study.mybatis.MyBatisTest.main(MyBatisTest.java:18)
在https://jdbc.postgresql.org/documentation/head/statement.html可以看到解决办法，使用 ?? 转义即可。
In JDBC, the question mark (?) is the placeholder for the positional parameters of aPreparedStatement.
There are, however, a number of PostgreSQL operators that contain a question mark. To keep such
question marks in a SQL statement from being interpreted as positional parameters, use two question marks
(??) as escape sequence. You can also use this escape sequence in aStatement, but that is not required.
Specifically only in aStatementa single (?) can be used as an operator.
网友评论