Does Hibernate HQL Support Regular expression pattern matching?

user_13347446 0
Viewed 510

I was testing different ways of data retrieval from my database (MySQL) using Hibernate. And I get to know that it is possible to use LIKE in HQL just like we do it in SQL. But when I tried REGEXP I got the following error

Exception in thread "main" java.lang.IllegalArgumentException: 
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: REGEXP near line 1, column 54 [from com.jithin.hibernate.entity.Student where fname REGEXP '@gmail.com$']

I have also tried the different forms like RLIKE and REGEXP_LIKE. But the error is same. So why does the LIKE operator is supported by HQL and why not REGEXP? Is there anyway to achieve it?

The code I used is as follows

session.beginTransaction();
List<Student> students = session.createQuery("from Student where email REGEXP 'gmnail.com$'"),getResultList();
session.getTransaction().commit();
hql hibernate mysql sql java
1 Answers

The LIKE operator is declared in the base hibernate abstract Dialect class. This is how hibernate know about it.

You can extend your hibernate dialect for example in the following way:

public class YourDialect extends MySQL8Dialect {

   public YourDialect(){
      registerFunction(
          "REGEXP_LIKE",
           new StandardSQLFunction( "REGEXP_LIKE", StandardBasicTypes.INTEGER )
      );
   }
}

Then declare this dialect in your hibernate config and then you will be able to use the REGEXP_LIKE function in hql queries.

user_62771040
Related