Question mark in native query

[kubav182]

When working with jsonb type in postgres, we can use ? operator.

SELECT * FROM table WHERE (json_col->'jsonKey')::jsonb ? :param

In JDBC driver you can use escape sequence ??
In Hibernate you can use escape sequence \\?\\?

But what should I use in Spring Data JPA? I'm getting error during startup java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?1 is not supported!

I'm using Spring Boot 2.4.5

[schauder]

This sounds like it needs fixing on our side. Could you provide a demonstrator, including the database using testcontainers, please?

[kubav182]

https://github.com/kubav182/demo-spring-data-jpa-question-mark

There is demo with working JPA and JDBC implementations. Data JPA implementation does not work and test fails.

[gregturn]

Thanks @kubav182. I've reproduced it on my end with a very simple unit test.

[gregturn]

@kubav182 I've patched it on Spring Data JPA 3.x where it ONLY blocks you if you REALLY have mixed JDBC and JPA style parameters. Anything else and we'll let it on through to the JPA provider. See commit above for examples.

[gregturn]

Backported to 2.7.x and 2.6.x.

[kubav182]

Nice work, looks good.

[gregturn]

@kubav182 Are you able to share a little about how you've wired up Postgres, JSONB operations, your own custom queries, and what version of Spring Boot you are using to pull it all together?

We are hitting another issue in the land of Postgres and JSONB operations and hitting a roadblock.

[kubav182]

@gregturn Sorry for late answer. We decided for another solution as we could not wait for release. I tried to upgrade my demo to Spring Boot 2.7.4 and it really does not work. I did investigation and the problem is in class org.springframework.data.jpa.repository.query.StringQuery and static field with regex.

public static final String POSITIONAL_OR_INDEXED_PARAMETER = "\\?(\\d*+(?![#\\w]))";

Prefix \\? matches question marks even they are escaped. If I skip this match, it works:

public static final String POSITIONAL_OR_INDEXED_PARAMETER = "[^\\\\]\\?(\\d*+(?![#\\w]))";

@Query(value = "select * from pet where (characteristics -> 'colours')\\:\\:jsonb \\?\\? :colour", nativeQuery = true)

I'm not sure about related risks.

[razorree]

Any updates on this? I still can't use ? jsonb operator in a native query (postgresql)
spring boot 2.7.18

[christophstrobl]

@razorree spring-boot:2.7.x line is already out of OSS maintenance - if the problem is still there with a recent version, please open a new issue with more details. Ideally add a complete minimal sample (something that we can unzip or git clone, build, and deploy) that reproduces the problem.