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.

Answer 1

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() + "%");