search

hibernate / hibernate-reactive

A reactive API for Hibernate ORM, supporting non-blocking database drivers and a reactive style of interaction with the database.
https://hibernate.org/reactive
Apache License 2.0
442 stars 92 forks source link

Question mark not escaped in a query (using PostgreSQL JSON data) #2012

Open s4iko opened 2 weeks ago

s4iko commented 2 weeks ago

Hello,

I tried to do a query on a PostgresSQL database with JSON content, something like: select * from mytable where mytable -> 'myelement' ? 'key123'; It doesn't work as this in a native query because the question mark is interpreted as a parameter. I searched for help on this and apparently, the question mark could be escaped using one of:

None of those 2 solutions worked but should it really work ? I didn't see anything about this in the documentation... may be I missed something.

If escaping the character is not an acceptable solution, may be it is possible to add a parameter in a future version to disable the "JDBC-style parameters" and force to use :myparam or ?1 ? I saw in the documentation that "JDBC-style parameters" have a skull head, it probably means it is a devil feature or at least a feature that we should not use so optionally not interpreting this seems to be an acceptable solution ?

As a reference, the same issue fixed in Spring Data JPA: https://github.com/spring-projects/spring-data-jpa/issues/2551

Quarkus: 3.15.1 Hibernate Reactive: 2.4.0

Thank you for your help.

DavideD commented 2 weeks ago

Doesn't this work?

select * from mytable where mytable -> 'myelement' \\? 'key123'
gavinking commented 2 weeks ago

I just tried it in Hibernate ORM 7, and \\?\\? appears to work as expected.

s4iko commented 1 week ago

Thanks for all your answers.

I confirm that it works with hibernate ORM but not with Hibernate Reactive. I made 2 reproducers by generating new projects using Quarkus starter, 1st for ORM, 2nd for Reactive version. Both contains those tests:

1- query with ?
2- query with \\?
3- query with \\?\\?
4- query with \\\\?\\\\?

The #3 work with Hibernate ORM. Unfortunately, none work with Hibernate Reactive.

I attached the reproducers I hope it can help :)

code-with-quarkus-postgres.zip code-with-quarkus-postgres-reactive.zip

You can run ITs with the command: mvn verify -DskipITs=false