NamedQuery中使用regexp_like编译报错，问题出在哪里？

问题背景 #

原始可正常编译运行的 NamedQuery：

@NamedQuery(name = "foo", query = "SELECT x from Y y WHERE y.a = :b AND y.c = :d AND e = 'Y' ORDER BY y.f DESC");

为实现字段 y.a 的大小写不敏感匹配，修改后的查询：

@NamedQuery(name = "foo", query = "SELECT x from Y y WHERE regexp_like(y.a, '^:b$', 'i') AND y.c = :d AND e = 'Y' ORDER BY y.f DESC");

修改后编译报错，错误信息：

2023-10-16 11:13:33,202 ERROR [main] o.h.h.PARSER - <AST>:1:105: unexpected AST node: (

疑问：regexp_like 调用必须带左括号，为何会触发语法解析错误？

问题根源 #

1. 参数绑定错误：你把参数 :b 写在了字符串常量 '^:b$' 内部，JPA 会将整个 '^:b$' 当作普通字符串处理，不会解析其中的 :b 作为参数。这种写法破坏了 regexp_like 的参数结构，导致 HQL 解析器无法识别正确的语法。
2. HQL 原生支持限制：regexp_like 是数据库（如 Oracle）的原生函数，HQL 本身并不原生支持该函数。直接在 HQL 中使用会导致解析器无法识别函数的语法规则。

解决方案 #

方案1：改用原生SQL查询 #

如果依赖数据库的 regexp_like 功能，直接使用原生SQL查询，并正确拼接参数：

@NamedNativeQuery(name = "foo", query = "SELECT x from Y y WHERE regexp_like(y.a, '^' || :b || '$', 'i') AND y.c = :d AND e = 'Y' ORDER BY y.f DESC", resultClass = Y.class);

通过数据库的字符串拼接符 || 将参数 :b 融入正则表达式，确保参数被正确绑定。

方案2：使用HQL通用大小写转换 #

如果不想依赖特定数据库的原生函数，可使用HQL自带的大小写转换函数实现不敏感匹配：

@NamedQuery(name = "foo", query = "SELECT x from Y y WHERE LOWER(y.a) = LOWER(:b) AND y.c = :d AND e = 'Y' ORDER BY y.f DESC");

将字段和参数统一转成小写（或大写），这种方式跨数据库通用，无需依赖特定函数。

方案3：给Hibernate注册自定义函数（仅适用于Hibernate作为JPA实现） #

如果坚持在HQL中使用 regexp_like，可以在Hibernate中注册该数据库原生函数：

1. 自定义方言类：

public class CustomOracleDialect extends OracleDialect {
    public CustomOracleDialect() {
        super();
        registerFunction("regexp_like", new StandardSQLFunction("regexp_like", StandardBasicTypes.BOOLEAN));
    }
}

2. 修改Hibernate配置，指定使用自定义方言：

<property name="hibernate.dialect">com.yourpackage.CustomOracleDialect</property>

3. 修正HQL中的参数拼接方式：

@NamedQuery(name = "foo", query = "SELECT x from Y y WHERE regexp_like(y.a, concat('^', :b, '$'), 'i') AND y.c = :d AND e = 'Y' ORDER BY y.f DESC");

使用HQL的 concat 函数拼接正则表达式，确保 :b 被解析为参数。

内容的提问来源于stack exchange，提问作者Sachin Kainth