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.

Solution 1

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

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

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

Comments

[Gunther Schadow]

why would you want to force it to return anything other than boolean? Oracle SQL is just idiotically designed to not have a boolean data type, but you don't need that 0 and 1 or 'Y' and 'N' result here.

[Leena]

I found the solution stackoverflow.com/questions/59193689/… useful for this

[Ollie]

You can still use indexes but you'd need to use Oracle's function based indexes. DBA's advising you to "Never use REGEXES"? I'd politely ask them to do some research about that as you'd be missing out on some VERY powerful functionality.

[Augusto]

They said never in the context of databases, not in other situation. And you might be right, I've heard them talking about starting to use function based indexes as the next cool thing :). So the advice would be: only use regexes on a table if it's small or has a function index on the column. Thanks for the comment Ollie!

[Big Ed]

My practice is to use a regex where it will give me exactly the data I want, but to add the closest LIKE expression to take advantage of indexes.

[manurajhada]

Thanks Steve, can you please provide me any sample code or reference url about how to resister the function in hibernate?

[Lucas Holt]

if you control the regex, you can make a function index for it and oracle will use it. However, if you have a dynamic regular expression that may vary, you cannot create a function index for it.

[Gunther Schadow]

Why would you want to return 1 and 0 if all you need is return the boolean as is?

[hashpyrit]

This looked really good but I just tried it and it doesn't work. Seems as though hibernate is insistent on having a function that must equal something. I still get an exception about an AST node