how to use Oracle's regexp_like in Hibernate H

Question

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.

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.

It says..

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

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

Answer 1

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

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

Answer 2

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.

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.

Answer 3

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).

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...

Assuming you currently use Oracle10gDialect, you would do:

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:

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 4

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

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

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:

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:

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

It will work :)

Answer 5

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.

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

Answer 6

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

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