How to use Oracle regexp_like in Hibernate HQL?

Question

I am using oracle 10g and hibernate 3.3.2 . I used regex in sql before, now for the first time I use 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 the regex_like function, it works as expected. But I can not execute it with regex_like expression.

It says.

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

Please help, how can I use regex_like in the original hibernate request? OR some other alternative for this.

Answer 1

Actually, you cannot compare the result of REGEXP_LIKE with anything other than conditional statements in PL / SQL.

Hibernate doesn't seem to accept a user-defined function without returnType, since you always need to compare the output with something, like this:

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

Since Oracle does not allow comparing the result of this function with anything, I came up with a solution using the 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 2

You can definitely use any type of database-specific function that you want to use Hibernate HQL (and JPQL if Hibernate is a provider). You just have to tell Hibernate about these features. In 3.3, the only option for this is to provide a custom Dialect and register a function from the Dialect constructor. If you look at the base class of Dialect, you will see many examples of registering functions. It is usually best to extend the exact Dialect that you are currently using and simply provide your extensions (here, by registering the function).

It is interesting to note that Oracle does not classify regexp_like as a function. They classify it as a condition / predicate. I think this is mainly because Oracle SQL does not define the BOOLEAN data type, although it does PL / SQL, and I would set regexp_like as the PL / SQL function returning BOOLEAN ...

Assuming you are currently using Oracle10gDialect, you would do:

 public class MyOracle10gDialect extends Oracle10gDialect { public Oracle10gDialect() { super(); registerFunction( "regexp_like", new StandardSQLFunction( "regexp_like", StandardBasicTypes.BOOLEAN ) ); } } 

I can't remember if the HQL parser loves functions that return Booleans, but in terms of the fact that it is a predicate in itself. Instead, you can convert true / false to another and check for this:

 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

You can try using the standard LIKE operator:

 where company.num like '%514619915' 

and then filter out the unnecessary with a Java regular expression. This should reduce the number of unnecessary rows to be returned.

This will not use the index because it starts with "%".

Answer 4

You cannot access specific database functions unless JPAQL / HQL provides you with a way to do this and provides nothing for regular expressions. Therefore, you need to write your own SQL query to use regular expressions.

In another, and very important point, several colleagues (Oracle DBAs) told me to never use regular expressions in oracle, since they cannot be indexed, which ends in a database that performs a full database scan. If there are several records in the table, then this is normal, but if there are many rows in it, this can lead to crippling performance.

Answer 5

For those using the Hibernate criteria with sqlRestriction (Hibernate Version 4.2.7)

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

Answer 6

Or another option is to create a similar function in oracle, which will return a numeric value based on the result of the operation. 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 can use

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