Hibernate regexp mysql

Question

I ask this question to show how MySQL and Hibernate work with each other using regular expressions.

Problem:

SELECT * FROM table WHERE regexp column '\d' 

Decision:

Go to my answer.

Hope this helps.

Answer 1

Basically, to use the MySQL regexp function in Hibernate, we need to create an "SQLFunctionTemplate".

Now how to do it:

First: create a class called "AppMySQLDialect" and continue from MySQLDialect, then override the empty constructor and finally register the regexp function:

 public class AppMySQLDialect extends MySQLDialect { public AppMySQLDialect() { super(); /** * Function to evaluate regexp in MySQL */ registerFunction("regexp", new SQLFunctionTemplate(Hibernate.INTEGER, "?1 REGEXP ?2")); } } 

Ok, now you can use it as follows:

 FROM Entity E WHERE regexp(E.string2evaluate, '\d') = 1 

Create your HibernateQuery and execute.

Answer 2

 String range = "ABCD"; List<HRTrainee> hrTrainees = (List<HRTrainee>)sessionFactory.getCurrentSession().createCriteria(HRTrainee.class) .add(Restrictions.sqlRestriction("name REGEXP '^["+range+"]'")).list(); return hrTrainees; 

Answer 3

REGEXP is considered as a keyword in MySQL. A user can use REGEXP in a sleeping filter by registering a keyword. Create a CustomMySQL5InnoDBDialect class that extends MySQL5InnoDBDialect and register the keyword as follows:

 public class CustomMySQL5InnoDBDialect extends MySQL5InnoDBDialect { public CustomMySQL5InnoDBDialect() { super(); /* register regexp keyword */ registerKeyword("regexp"); } } 

Then change the hibernate-dialect property in persistence.xml as

 <property name="hibernate.dialect" value="com.CustomMySQL5InnoDBDialect"/> 

The user can use the regular expression in the hibernation filter as follows

 @Filters(value = { @Filter(name="applyStudentFilter",condition="id in (select s.id from student s WHERE s.address REGEXP :addressValue)"), })