how to use Oracle's regexp_like in Hibernate HQL?

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

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 2

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