SELECT查询使用collate binary_ci与arg映射到多个参数

Question

我试图创建一个spring数据jpa自定义查询，需要2个参数并使用collate binary_ci。一个参数使用'='与字符串进行比较，另一个参数使用LIKE与字符串进行比较。没有collate binary_ci:

时工作的示例

SELECT * FROM MYTABLE WHERE ID = :id AND ((MODEL LIKE %:in%) OR (DESCR LIKE %:in%)) ORDER BY ...

LIKE参数in映射到多个参数。我无法使这个查询工作。我尝试了很多东西，但最后，我试图包括collate binary_ci是问题。以下是我尝试过的:

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

运行这些查询让我Could not locate named parameter [in], expecting one of [in%, id]或sql statement was not ended properly或missing right parentheses

我怎样才能使它工作?

版本:Spring-Boot: (v2.4.3)

下面是我的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

Answer 1

figure out:

WHERE ID = :id AND ((MODEL LIKE %:in% collate binary_ci) OR (DESCR LIKE %:in% collate binary_ci)) ORDER BY...