CODEWITHSUNDEEP
springhibernatespring-mvcsql-like
madhu
madhu

Reputation: 1018

HQL like operator for case insensitive search

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...

Upvotes: 29

Views: 47437

Answers (2)

Vasile Bors
Vasile Bors

Reputation: 656

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

Upvotes: 16

Jigar Parekh
Jigar Parekh

Reputation: 6273

change your query to 

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

for more information have a look 14.3 doc

Upvotes: 68

Related Questions