?在jsonb上进行本地SQL查询

Question

我需要对Postgresjsonb列触发一个select查询:

entityManager.createNativeQuery(
"select * from table where jsonbcol -> 'usernames' ? :un"
).setParameter("un", userName).getResultList()

运行时抛出异常:

org.hibernate.engine.query.ParameterRecognitionException: Mixed parameter strategies - 
use just one of named, positional or JPA-ordinal strategy

我试着像\?和??一样逃跑，但没有帮助。

如何正确调用?

Answer 1

正确的转义序列使查询如下:

entityManager.createNativeQuery(
"select * from table where jsonbcol -> 'usernames' \?\? :un"
).setParameter("un", userName).getResultList()

反斜杠表示hibernate参数检测，两个问号表示JDBC转义。

Answer 2

有一种替代的解决方案可以成为一种优雅的变通方法。Postgres操作符?基于jsonb_exists()函数，所以不用

where jsonbcol -> 'usernames' \?\? :un

可以用

where jsonb_exists(jsonbcol -> 'usernames', :un)

Answer 3

正如文档所说:

在JDBC中，问号(?)是PreparedStatement位置参数的占位符。但是，有许多PostgreSQL操作符包含问号。为了防止SQL语句中的这些问号被解释为位置参数，请使用两个问号(??)作为转义序列。