I ask this question to show how MySQL and Hibernate work each other with Regular Expressions.
The problem:
SELECT * FROM table WHERE regexp column '\d'
Solution:
Go to my answer.
Hope this helps.
Add a comment
|
3 Answers
Basically, to use MySQL regexp function in Hibernate we need to create a "SQLFunctionTemplate".
Now, how to do it:
First: Create a class called "AppMySQLDialect" and extends 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 lets use it as follow:
FROM Entity E WHERE regexp(E.string2evaluate, '\d') = 1
Create your HibernateQuery and execute.
Comments
String range = "ABCD";
List<HRTrainee> hrTrainees =
(List<HRTrainee>)sessionFactory.getCurrentSession().createCriteria(HRTrainee.class)
.add(Restrictions.sqlRestriction("name REGEXP '^["+range+"]'")).list();
return hrTrainees;
Comments
REGEXP is treated as a keyword in MySQL. User can use REGEXP in hibernate filter by registering the keyword. Create a class 'CustomMySQL5InnoDBDialect' extending 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"/>
User can use the regexp in hibernate filter as follows
@Filters(value = { @Filter(name="applyStudentFilter",condition="id in (select s.id from student s WHERE s.address REGEXP :addressValue)"),
})
