HQL query with LIKE having issues

Question

I am working on a search query using HQL and everything works find until I get to the LIKE clause. No matter what I do it does not seem to execute the LIKE clause properly. Here is my query.

String QUERY = "FROM Person as p WHERE p.createUser = : createUser 
     AND p.personId in (SELECT pn.personId FROM PersonName pn WHERE pn.personNameType = 'FIRST' AND pn.name LIKE '%:firstName%')";

(List<Person>)session.createQuery(QUERY).setString("createUser", createUser).setString("firstName", firstName).list();

score 73 · Accepted Answer · answered Mar 24 '11 at 13:54

Parameters inside string literals are not resolved.

You need to add %s to parameter values with string concatenation - either at the program side

String QUERY = "FROM Person as p WHERE p.createUser = : createUser 
     AND p.personId in " +
     "(SELECT pn.personId FROM PersonName pn " + 
     "WHERE pn.personNameType = 'FIRST' " + 
     "AND pn.name LIKE :firstName)";

(List<Person>)session.createQuery(QUERY)
    .setString("createUser", createUser)
    .setString("firstName", "%" + firstName + "%").list();

or at the database side:

String QUERY = "FROM Person as p WHERE p.createUser = : createUser 
     AND p.personId in " +
     "(SELECT pn.personId FROM PersonName pn " + 
     "WHERE pn.personNameType = 'FIRST' " + 
     "AND pn.name LIKE CONCAT('%', :firstName, '%'))";

(List<Person>)session.createQuery(QUERY)
    .setString("createUser", createUser)
    .setString("firstName", firstName).list();

What about case sensitivity. Is there a way to make it case insensitive? Thanks for the reply! @axtavt — medium, Mar 24 '11 at 13:58
cast `pn.name` and the parameter `firstName` to lower case in your query — djmj, Feb 13 '13 at 22:45

score 3 · Answer 2 · edited Jun 13 '21 at 08:01

3

we can use multiple concat to resolve this issue.

SELECT pn.personId FROM PersonName pn WHERE pn.personNameType = 'FIRST' AND pn.name LIKE concat(concat('%', :firstName), '%')

edited Jun 13 '21 at 08:01

Grim

1,938
10
56
123

answered Apr 15 '19 at 19:55

MR AND

376
7
29

1

Works like a charm. I use `AND pn.name LIKE '%' || :firstName || '%'` for the shorter way. – Grim Jun 13 '21 at 08:02

HQL query with LIKE having issues

2 Answers2