如何在Hibernate HQL中使用Oracle的regexp_like？

Question

我正在使用oracle 10g和hibernate 3.3.2。我以前在sql中使用过正则表达式，现在我第一次在HQL中使用它。

Query query = getSession().createQuery("From Company company 
where company.id!=:companyId and 
regexp_like(upper(rtrim(ltrim(company.num))), '^0*514619915$' )");

这是我的hql，当我在没有类似regex_的函数的情况下运行它时，它会按预期运行。但我无法用类似正则表达式的表达式执行它。

它说...

嵌套异常是org.hibernate.hql.ast.QuerySynTaxException：意外的AST节点：（靠近第1行，第66列......

敬请帮助，我如何在hibernate native query中使用类似正则表达式的？或者其他替代方法。

Answer 1

除非JPAQL/HQL提供了一种访问特定数据库函数的方法，而且也没有为正则表达式提供任何内容，否则您无法访问特定的数据库函数。因此，您需要编写一个本机SQL查询来使用正则表达式。

在另一个非常重要的问题上，一些同事（Oracle DBA）告诉我不要在Oracle中使用正则表达式，因为它们不能被索引，最终会在数据库中执行完整的数据库扫描。如果表中有几个条目，那么就可以了，但是如果表中有很多行，则可能会影响性能。

Answer 2

您绝对可以使用Hibernate HQL（和JPQL，只要Hibernate是提供者）使用任何类型的特定于数据库的函数。您只需告诉Hibernate这些函数。在3.3中，唯一的选择是提供自定义Dialect并从Dialect的构造函数注册函数。如果您查看基本Dialect类，您将看到许多注册函数的示例。通常最好扩展您当前使用的确切Dialect并简单地提供您的扩展（此处为注册函数）。

有趣的是，Oracle没有将类regexp\u分类为函数。他们将其归类为条件/谓词。我认为这主要是因为Oracle SQL没有定义布尔数据类型，即使他们的PL/SQL定义了布尔数据类型，我敢打赌类regexp\u被定义为返回布尔值的PL/SQL函数。。。

假设您当前使用Oracle10gDialect，您将执行以下操作：

public class MyOracle10gDialect extends Oracle10gDialect {
    public Oracle10gDialect() {
        super();

        registerFunction( 
            "regexp_like", 
             new StandardSQLFunction( "regexp_like", StandardBasicTypes.BOOLEAN )
        );
    }
}

我不记得HQL解析器是否喜欢返回布尔值的函数，但是就其本身作为谓词而言。相反，您可能必须将true/false转换为其他内容并检查该返回：

public class MyOracle10gDialect extends Oracle10gDialect {
    public Oracle10gDialect() {
        super();

        registerFunction( 
            "regexp_like", 
             new StandardSQLFunction( "regexp_like", StandardBasicTypes.INTEGER ) {
                 @Override
                 public String render(
                         Type firstArgumentType, 
                         List arguments, 
                         SessionFactoryImplementor factory) {
                     return "some_conversion_from_boolean_to_int(" + 
                             super.render( firstArgumentType, arguments, factory ) +
                             ")";
                 }
             }
        );
    }
}

Answer 3

实际上，除了PL/SQL中的条件语句之外，您不能将REGEXP_LIKE的结果与任何东西进行比较。

Hibernate似乎不接受没有returType的自定义函数，因为您总是需要将输出与某些东西进行比较，即：

REGEXP_LIKE('bananas', 'a', 'i') = 1

由于Oracle不允许您将此函数的结果与任何结果进行比较，因此我使用case条件提出了一个解决方案：

public class Oracle10gExtendedDialect extends Oracle10gDialect {

    public Oracle10gExtendedDialect() {
        super();
        registerFunction(
          "regexp_like", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN,
          "(case when (regexp_like(?1, ?2, ?3)) then 1 else 0 end)")
        );
    }

}

您的HQL应该如下所示：

REGEXP_LIKE('bananas', 'a', 'i') = 1

它将工作：）