Usage of ? in native SQL query on jsonb

user_1682820 0
Viewed 827

I need to fire a select query against Postgres jsonb column:

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

Upon running the Exception is thrown:

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

I tried escaping like \\? and ?? but that didn't help.

How to do that call properly?

jsonb jpa postgresql hibernate java
3 Answers

The proper escape sequence makes the query like this:

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

The backslashes escape hibernate parameter detection, and the two question-marks are the JDBC escape.

user_38411610

There is an alternative solution that can turn out to be an elegant workaround. Postgres operator ? is based on the jsonb_exists() function, so instead of

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

you can use

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

As the documentation says:

In JDBC, the question mark (?) is the placeholder for the positional parameters of a PreparedStatement. 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.

user_64643080
Related