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

I am using oracle 10g and hibernate 3.3.2. I have used regular expression in sql before, now for the first time I am using it in HQL.

我使用的是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$' )");

This is my hql, when i run it without regex_like function it runs as expected. But I am not able to execute it with regex_like expression.

这是我的hql，当我不带regex_like函数运行它时，它会按预期运行。但是我不能用regex_like表达式执行它。

It says..

它说. .

nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 66.....

嵌套的异常org.hibernate.hql.ast。QuerySyntaxException:意外的AST节点:(靠近第1行，第66列…

Kindly help, how can I use regex_like in hibernate native query? OR some other alternative to do so.

请帮助，如何在hibernate本机查询中使用regex_like ?或者其他的选择。

6 个解决方案

#1

Actually, you can't compare the result of REGEXP_LIKE to anything except in conditional statements in PL/SQL.

实际上，除了PL/SQL中的条件语句之外，您无法将REGEXP_LIKE的结果与其他任何结果进行比较。

Hibernate seems to not accept a custom function without a returnType, as you always need to compare the output to something, i.e:

Hibernate似乎不接受没有returnType的自定义函数，因为您总是需要将输出与某事进行比较，例如:

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

As Oracle doesn't allow you to compare this function's result to nothing, I came up with a solution using case condition:

由于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)")
        );
    }

}

And your HQL should look like this:

你的HQL应该是这样的:

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

It will work :)

它将工作:)

#2

You can most definitely use any type of database-specific function you wish with Hibernate HQL (and JPQL as long as Hibernate is the provider). You simply have to tell Hibernate about those functions. In 3.3 the only option for that is to provide a custom Dialect and register the function from the Dialect's constructor. If you take a look at the base Dialect class you will see lots of examples of registering functions. Usually best to extend the exact Dialect you currently use and simply provide your extensions (here, registering the function).

您可以在Hibernate HQL(和JPQL，只要Hibernate是提供者)中使用任何类型的特定于数据库的函数。你只需告诉Hibernate这些函数。在3.3中，惟一的选择是提供自定义方言并从方言的构造函数中注册函数。如果您查看基方言类，您将看到许多注册函数的例子。通常最好扩展您当前使用的特定方言，并简单地提供扩展(在这里，注册函数)。

An interesting note is that Oracle does not classify regexp_like as a function. They classify it as a condition/predicate. I think this is mostly because Oracle SQL does not define a BOOLEAN datatype, even though their PL/SQL does and I would bet regexp_like is defined as a PL/SQL function returning BOOLEAN...

值得注意的是，Oracle并没有将regexp_like分类为函数。他们把它归类为条件/谓词。我认为这主要是因为Oracle SQL没有定义布尔数据类型，即使它们的PL/SQL定义了，我敢打赌regexp_like是定义为一个返回布尔值的PL/SQL函数……

Assuming you currently use Oracle10gDialect, you would do:

假设你现在使用Oracle10gDialect，你会:

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

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

I cannot remember if the HQL parser likes functions returning booleans however in terms of being a predicate all by itself. You may instead have to convert true/false to something else and check against that return:

我不记得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 ) +
                             ")";
                 }
             }
        );
    }
}

#3

You might try using the standard LIKE operator:

您可以尝试使用标准的操作符:

where company.num like '%514619915'

and then filter out the unwanted ones using a Java regex. That should reduce the number of unneeded rows that would be returned.

然后使用Java regex过滤掉不需要的内容。这将减少将返回的不需要的行数。

This would not use an index because it begins with a '%'.

这不会使用索引，因为它以'%'开头。

#4

You can't access specific database functions unless JPAQL/HQL provide a way to do so, and neither provide anything for regular expressions. So you'll need to write a native SQL query to use regexes.

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

On another, and very important point, a few colleagues (Oracle DBAs) told me to never use regexes in oracle, as they can't be indexed, which ends up in the DB performing a full DB scan. If the table has a few entries, then it's ok, but if it has lots of rows, it might cripple the performance.

另一个非常重要的问题是，一些同事(Oracle dba)告诉我不要在Oracle中使用regexe，因为它们不能被索引，这最终导致DB执行完整的DB扫描。如果该表有一些条目，那么它就可以了，但是如果它有很多行，它可能会削弱性能。

#5

For those using Hibernate criterion with sqlRestriction (Hibernate Version 4.2.7)

对于使用Hibernate标准和sqllimit的用户(Hibernate 4.2.7版本)

 Criterion someCriterion = Restrictions.sqlRestriction("regexp_like (column_name, ?, 'i')", "(^|\\s)"+searchValue+"($|\\s|.$)", StringType.INSTANCE);

#6

Or another option is to create similar function in oracle which will return numeric value based on operation result. Something like that

另一种选择是在oracle中创建类似的函数，该函数将返回基于操作结果的数值。类似的东西

CREATE OR REPLACE FUNCTION MY_REGEXP_LIKE(text VARCHAR2, pattern VARCHAR2)
RETURN NUMBER 
IS function_result NUMBER;
BEGIN
  function_result := CASE WHEN REGEXP_LIKE(text, pattern) 
  THEN 1 
  ELSE 0
  END;    
  RETURN(function_result);
END MY_REGEXP_LIKE;

and you will be able to use

你可以使用

MY_REGEXP_LIKE('bananas', 'a') = 1

#1