How to use ilike and % in createSQLQuery in hibernate java

Question

I have made a sql query now i need to add search from it. it needs to search from userfullname the given keyword query is working in postgresql but it is not working with CreateSqlQuery.

sqlQuery = "select * from ( " + sqlQuery + ") a where a.payeeName ilike :searchpayeename ";
SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery)
                .addScalar("id", new LongType());
 query.setParameter("searchpayeename", "%"+payee + "%");

It is not giving me result while if i run same query in sql it is giving result. Any Idea.

Possible duplicate of [Hibernate Named Query Using Like and % % operators?](https://stackoverflow.com/questions/14119241/hibernate-named-query-using-like-and-operators) — Mebin Joe, Jul 30 '19 at 11:19
sqlQuery = "select * from ( " + sqlQuery + ") a where a.payeeName ilike :searchpayeename "; — charmi, Jul 30 '19 at 12:18

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Operator iLike not work with jpql, only with native query.

If you want use jpql you need simulate the iLike function using toLowerCase() on both sides of query.

jpqlQuery = "SELECT a FROM EntityName a WHERE LOWER(a.payeeName) LIKE :searchpayeename";

SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(jpqlQuery)
                .addScalar("id", new LongType());

query.setParameter("searchpayeename", "%" + payee.toLowerCase() + "%");

Or using createNativeQuery:

sqlQuery = "SELECT * FROM EntityName a WHERE " +
           "LOWER(a.payeeName) LIKE LOWER(CONCAT('%',:searchpayeename, '%'))";

SQLQuery query = sessionFactory.getCurrentSession().createNativeQuery(sqlQuery)
                .addScalar("id", new LongType());

query.setParameter("searchpayeename", "%" + payee.toLowerCase() + "%");

How to use ilike and % in createSQLQuery in hibernate java

1 Answers1

Operator iLike not work with jpql, only with native query.