I am trying to create a spring data jpa custom query that takes 2 args and uses collate binary_ci
. One arg is compared to a string using '=', the other is compared to a string using LIKE.
Example that works without collate binary_ci
:
SELECT * FROM MYTABLE WHERE ID = :id AND ((MODEL LIKE %:in%) OR (DESCR LIKE %:in%)) ORDER BY ...
The LIKE arg in
is mapped to multiple parameters. I cannot get this query to work. I have tried multiple things, but in the end, my attempt to include collate binary_ci
is the issue. Here is what I've tried:
WHERE ID = :id AND ((MODEL LIKE %:in%) OR (DESCR LIKE %:in%)) collate binary_ci ORDER BY ...
WHERE ID = :id AND (MODEL LIKE %:in% OR DESCR LIKE %:in%) collate binary_ci ORDER BY ...
WHERE ID = :id AND ((MODEL LIKE %:in%) collate binary_ci OR (DESCR LIKE %:in%) collate binary_ci) ORDER BY
Running these queries gets me either Could not locate named parameter [in], expecting one of [in%, id]
or sql statement was not ended properly
or missing right parentheses
How can I make this work?
Version: Spring-Boot: (v2.4.3)
Here are version values from my sqldeveloper:
org.openide.specification.version 6.2
org.osgi.framework.os.version 10.0.0
org.osgi.framework.version 1.7.0
os.version 10.0
osgi.framework.version 3.9.1.v20140110-1610
Figured it out:
WHERE ID = :id AND ((MODEL LIKE %:in% collate binary_ci) OR (DESCR LIKE %:in% collate binary_ci)) ORDER BY...