Search code examples
javamysqlhibernatehqlrlike

What's the equivalent of mysql RLIKE operator in Hibernate Query?

I have the following query which runs perfectly in mysql.

SELECT * FROM Orders as o, Products as p  where o.productinfo RLIKE p.code;

Here I am joining two tables Orders and Products with RLIKE.

I am trying to implement the same in Hibernate.

Query query = session.createQuery("FROM Orders as o, Products as p  where o.productinfo RLIKE p.code");
List<Object[]> results = query.getResultList();

When I used RLIKE, the following error is thrown in run time. 

{"errormessage":"org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: RLIKE

I tried to implement the same with LIKE query and matched it with '%p.code%'.

Query query = session.createQuery("FROM Orders as o, Products as p  where o.productinfo LIKE '%p.code%'");

But it matches with the string "p.code" rather then the value.

What is the equivalent of RLIKE in HQL? Is there a different way to join two tables with LIKE in HQL?

Thanks.

Answer by @YCF_L: For any one trying to join two tables with like operator in Hibernate (mysql) can do it the following way. 

SELECT * FROM Orders as o, Products as p  where o.productinfo LIKE CONCAT('%',p.code,'%');
Solution

  • What's the equivalent of mysql RLIKE operator in Hibernate Query?

    RLIKE is the synonym for REGEXP so you can implement it in hibernate using REGEXP_LIKE, you can take a look about this here : How to search in multiple columns using one like operator in HQL (hibernate sql)

    I tried to implement the same with LIKE query and matched it with '%p.code%'.

    ..., Products as p  where o.productinfo LIKE '%p.code%'");

    But it matches with the string "p.code" rather then the value.

    This is true, because you don't pass the correct value of p.code, you pass it like a String, instead you have two ways :

    Query query = session.createQuery("....Products as p  where o.productinfo LIKE '%:code%'");
//------------------------------------------------------------------------------^^^^^^^
query.setParameter("code", p.code);

    Or you can concatenate your code with your Query, but the first solution is better.

    Query query = session.createQuery("....  where o.productinfo LIKE '%" + p.code + "%'");

    EDIT

    You can use like with CONCAT without specify the '' like this :

    SELECT * FROM Orders as o, Products as p  where o.productinfo LIKE CONCAT('%',p.code,'%');