Hibernate Named Query Using Like and % % operators?

Question

In my Hibernate JPA Sample code..

public List<AttendeesVO> addAttendees(String searchKeyword) {
    TypedQuery<AttendeesVO> query = entityManager.createQuery(" select at from AttendeesVO at where at.user.firstName LIKE :searchKeyword",AttendeesVO.class);
    query.setParameter("searchKeyword", searchKeyword+"%");
    return query.getResultList();
}

it is working fine when giving entire String firstName=Narasimham

But it is not working when we give any character of Narasimham i.e a or n

Actually my thinking is i'm giving Like operator with % % so it working any character of given String..

score 61 · Accepted Answer · answered Jan 02 '13 at 09:06

61

you are using query.setParameter("searchKeyword", searchKeyword+"%");

instead of query.setParameter("searchKeyword", "%"+searchKeyword+"%");

first one will return rows for Narasimham N Na Nar Nara etc.

answered Jan 02 '13 at 09:06

Subin Sebastian

10,870
3
37
42

Ajinkya · Answer 2 · 2013-01-02T09:17:49.183

But it is not working when we give any character of Narasimham i.e a or n

Because you are doing case sensitive search. Try N, Na, Nar instead. If you want to perform a case insensitive search try using upper or lower. like

entityManager.createQuery("select at from AttendeesVO at where lower(at.user.firstName) LIKE lower(:searchKeyword)",AttendeesVO.class);

Actually my thinking is i'm giving Like operator with % %

searchKeyword+"%" means return values which starts with searchKeyword.
"%"+searchKeyword+"%" means return values which contains searchKeyword.
Decide as per your requirment.

score 9 · Answer 3 · edited Jan 18 '19 at 11:21

I would add my voice to @ssk to use two % before and after the keyword. Or I think its more professional solution if you configured it in the query itself like this :

public List<AttendeesVO> addAttendees(String searchKeyword) {
    TypedQuery<AttendeesVO> query = entityManager.createQuery(" select at from AttendeesVO 
    at where at.user.firstName LIKE CONCAT('%',:searchKeyword,'%')",AttendeesVO.class);
    query.setParameter("searchKeyword", searchKeyword);
    return query.getResultList();
}

because the '%' its part of the query not of the parameter what you may fill it out later

The CONCAT() function adds two or more expressions together. https://www.w3schools.com/sql/func_mysql_concat.asp

Hibernate Named Query Using Like and % % operators?

3 Answers3

Linked