How to use regular expression in Hibernate MySQL?

Question

How to add regexp in MySQL for a select query in hibernate. I am using ajax data table. my project is in spring 4 MVC and Hibernate with MySQL. I already create this in hibernate with MongoDB but I don't know how to use in MySQL.

hibernate with mongodb code:

Query query = new Query();
query.addCriteria(Criteria.where("isDeleted").is(IsDeletedEnum.NOTDELETED));
String search = (String) rowStartIdxAndCount[4];
if (StringUtils.isNotBlank(search)) {
    String searchExp = ".*" + search + ".*";
    Criteria searchCriteria = new Criteria();
    searchCriteria.orOperator(Criteria.where("name").regex(searchExp, "i"),
        Criteria.where("mobileno").regex(searchExp, "i"),
        Criteria.where("email").regex(searchExp, "i"),
        Criteria.where("role").regex(searchExp, "i"));
    query.addCriteria(searchCriteria);
}

and I am trying in Hibernate with MySQL. but no idea what's next that I have to write? I have to add regex in name, mobileno, email and role field which is used with or operator.

    Session session = getSessionFactory().openSession();
    session.beginTransaction();
    String search = (String) rowStartIdxAndCount[4];
    String searchExp = ".*" + search + ".*";
    String hql ="SELECT * FROM UserBean WHERE isDeleted=isDeleted OR name REGEXP 'searchExp''i'";
    Query query = session.createQuery(hql);
    query.setParameter("isDeleted", IsDeletedEnum.NOTDELETED);

so how to do that? please help me.

do you mean how to search operation on multiple column ? @Harsh in hibernate — Divyesh Kanzariya
– Divyesh Kanzariya, Commented Aug 30, 2017 at 6:23
yes, but with or operation and regular expression.you can see MongoDB code.that was criteria based query and I have to create hql based query. — Harsh
– Harsh, Commented Aug 30, 2017 at 6:25
give me SQL I convert it to HQL. I am not much familiar with NoSQL — Divyesh Kanzariya
– Divyesh Kanzariya, Commented Aug 31, 2017 at 6:56

Rahul Raj · Accepted Answer · 2017-08-30 06:38:55Z

3

Make criteria one by one:

criteria1
.setProjection(Projections.property("name"))
.add(Restrictions.sqlRestriction("REGEXP pattern")

criteria2
.setProjection(Projections.property("mobileno"))
.add(Restrictions.sqlRestriction("REGEXP pattern")

criteria3
.setProjection(Projections.property("email"))
.add(Restrictions.sqlRestriction("REGEXP pattern")

criteria4
.setProjection(Projections.property("role"))
.add(Restrictions.sqlRestriction("REGEXP pattern")

After that add those criteria to hibernate query:

query.addCriteria(criteria1);
query.addCriteria(criteria2);
query.addCriteria(criteria3);
query.addCriteria(criteria4);

answered Aug 30, 2017 at 6:38

Rahul Raj

3,4598 gold badges42 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harsh Over a year ago

how to add (searchExp, "i") in a regular expression? I have to add both in that.and is there any other way for creating it in HQL query based?

Collectives™ on Stack Overflow

How to use regular expression in Hibernate MySQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related