HQL like operator for case insensitive search

spring hibernate spring-mvc sql-like
madhu picture madhu · Nov 7, 2012 · Viewed 38.6k times · Source

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

Answer

Jigar Parekh picture Jigar Parekh · Nov 7, 2012

change your query to 

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

for more information have a look 14.3 doc

Related questions

How to solve the “failed to lazily initialize a collection of role” Hibernate exception

I have this problem: org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: mvc3.model.Topic.comments, no session or session was closed Here is the model: @Entity @Table(name = "T_TOPIC") public class Topic { @Id @GeneratedValue(strategy=…

Read More
Error creating bean with name 'entityManagerFactory' defined in class path resource : Invocation of init method failed

When I compile my spring project, I got the following error. Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed I am using STS Eclipse …

Read More
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'MyController':

I am doing spring + hibernate application. When I run the application on tomcat server I am getting some exceptions. INFO : org.springframework.web.servlet.DispatcherServlet - FrameworkServlet 'appServlet': initialization started INFO : org.springframework.web.context.support.XmlWebApplicationContext - Refreshing WebApplicationContext …

Read More