@Query annotation and named parameters in quoted strings

Question

So, here is the native MySQL query where I want to get all the rows based on JSON key-value pairs (ev_data is json type column), and it works just fine:

SELECT * FROM event WHERE JSON_CONTAINS(ev_data, '{"key":"key1","value":"val1"}','$.items')

An example of ev_data column content:

{"items":[{"key":"key1","value":"val1"},{"key":"key2","value":"val2"}]}

Now, I am using Spring Boot's CrudRepository interface and @Query annotation for my custom fetch method as follows:

@Query(value="SELECT * FROM event WHERE JSON_CONTAINS(ev_data, '{\"key\" : \"key1\", \"value\" : :value}', '$.items')", nativeQuery=true)
Set<Event> findAllByItemKey1Value(@Param("value") String value);

This, however, won't work and trigger an exception like: java.lang.IllegalArgumentException: Parameter with that name [value] did not exist because, apparently, you are not allowed to use parameters (named or indexed) inside quoted strings. Not sure if it's JPA/JPQL or Spring Boot thing but it is a fact.

You have a string literal '{\"key\" : \"key1\", \"value\" : :value}' ... and the parameter is in that, so hence is treated a string literal NOT a parameter. The message tells you exactly that. — user3973283
– user3973283, Commented Oct 31, 2018 at 19:04

ADi3ek · Accepted Answer · 2018-10-31 18:32:49Z

3

The only workaround solution I have found so far is by using MySQL string REPLACE() function:

@Query(value="SELECT * FROM event WHERE JSON_CONTAINS(ev_data, REPLACE('{\"key\" : \"key1\", \"value\" : \"*v*\"}','*v*', :value), '$.items')", nativeQuery=true)
Set<Event> findAllByItemKey1Value(@Param("value") String value);

It's not very elegant but it works for me and similar trick should also work for any named parameters used inside quotes.

Anyway, please let me know if you think this can be done better or more kosher, so to speak ;-)

answered Oct 31, 2018 at 18:32

ADi3ek

6757 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

@Query annotation and named parameters in quoted strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related