CODEWITHSUNDEEP
javapostgresqlhibernatejpajsonb
injecteer
injecteer

Reputation: 20707

Usage of ? in native SQL query on jsonb

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?

Upvotes: 7

Views: 2205

Answers (3)

coladict
coladict

Reputation: 5095

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.

Upvotes: 8

klin
klin

Reputation: 121814

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)

Upvotes: 4

Laurenz Albe
Laurenz Albe

Reputation: 247575

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.

Upvotes: 0

Related Questions