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(); 

Answer 1

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(); 

Answer 2

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), '%')