32

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();
medium
  • 3,708
  • 14
  • 50
  • 63

2 Answers2

67

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();
axtavt
  • 228,184
  • 37
  • 489
  • 472
  • 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
  • 1
    cast `pn.name` and the parameter `firstName` to lower case in your query – djmj Feb 13 '13 at 22:45
1

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), '%')
MR AND
  • 356
  • 4
  • 23