HQL like operator for case insensitive search

Question

I am implementing an autocomplete functionality using Jquery, when I type the name, it fetches the record from the db, The records stored in db are mixture of capital & small letters. I have written a HQL Query which fetches me the records with case-sensitive, but I need to records irrespective of case. Here is the query,

List<OrganizationTB> resultList = null;
Query query = session.createQuery("from DataOrganization dataOrg where dataOrg.poolName   
like '%"+ poolName +"%'");
resultList =  query.list();

Ex : If I have pool names, HRMS Data set, Hrms Data, Hr data etc... if I type HR or hr I need to get all the 3 records, which I'm not able to.

Please help...

score 67 · Accepted Answer · answered Nov 07 '12 at 09:09

67

change your query to

"from DataOrganization dataOrg where lower(dataOrg.poolName)   
like lower('%"+ poolName +"%')"

for more information have a look 14.3 doc

answered Nov 07 '12 at 09:09

Jigar Parekh

6,163
7
44
64

2

Don't you think that `poolName.toLowerCase()` is a good choice over `lower('%"+ poolName +"%')"` for like expression, as it prevents extra overhead for HQL function conversion to DB specific function? – Arun Kumar Oct 28 '14 at 05:17
1

@ArunKumar i think if you `poolName.toLowerCase()` then comparsion will be done at Java-Pojo, there will not be any SQL optimization where as in case of function `lower` there is possibility at dialect level to have SQL optimization and will consume less memory with less no. of objects fetched from database. – Jigar Parekh Oct 30 '14 at 05:17

Vasile Bors · Answer 2 · 2021-12-13T08:42:23.790

16

A good solution is:

List<OrganizationTB> resultList = null;
Query query = session.createQuery("from DataOrganization dataOrg where lower(dataOrg.poolName) like lower(:poolName)");
query.setParameter("poolName", '%'+poolName+'%', StringType.INSTANCE);
resultList =  query.list();

So you protect your code from SQL injection

edited Dec 13 '21 at 08:42

answered Oct 17 '15 at 13:44

Vasile Bors

656
6
14

And you have table fields content and query string both lower-cased. – WesternGun Nov 30 '15 at 12:20
2

It would be cleaner if you let just one layer to do lowercase work. You have left side lowercased by HQL and right side lowercased by Java. – horvoje Aug 28 '21 at 18:04
@horvoje you are right. I edited. – Vasile Bors Dec 13 '21 at 08:43

HQL like operator for case insensitive search

2 Answers2

Linked