Case Insensitive Search Using Hibernate

Question

I use Hibernate to ORM my Java application in an Oracle database (not something that deals with the database manufacturer, we can switch to another database one day) and I want to get objects from the database according to the ones provided by the user strings. For example, when searching for people, if a user searches for people who live in "fran", I want to be able to give it to people in San Francisco.

SQL is not my strong suit, and I prefer Hibernate Criteria building code for hard-coded strings, as they are. Can someone point me in the right direction how to do this in code, and if that is not possible, what does the hard SQL code look like?

Thank,

Yuval = 8 -)

Answer 1

In the simple case that you are describing, look at Restrictions.ilike (), which does a case insensitive search.

 Criteria crit = session.createCriteria(Person.class); crit.add(Restrictions.ilike('town', '%fran%'); List results = crit.list(); 

Answer 2

 Criteria crit = session.createCriteria(Person.class); crit.add(Restrictions.ilike('town', 'fran', MatchMode.ANYWHERE); List results = crit.list(); 

Answer 3

If you use the Spring HibernateTemplate to interact with Hibernate, here is how you will do case-insensitive searches to the user's email address:

 getHibernateTemplate().find("from User where upper(email)=?", emailAddr.toUpperCase()); 

Answer 4

The usual approach to ignoring the case is to convert the database values ​​and the input value to upper or lower case - the resulting sql will have something like

 select f.name from f where TO_UPPER(f.name) like '%FRAN%' 

In sleeping criteria constraints .like (...). ignoreCase ()

I am more familiar with Nhibernate, so the syntax may not be 100% accurate

see pro hibernate 3 extract and docs 15.2 sleep mode for more information . Narrowing the result set

Answer 5

You also do not need to enter% wildcards. You can pass MatchMode ( documents for previous releases here ) to indicate how to behave. START , ANYWHERE , EXACT and END correspond to the parameters.

Answer 6

Most database mappings are not case sensitive by default, but in the SQL Server world it can be installed at the instance, database, and column level.

Answer 7

You can see how to use a compass with a shell over lucene.

http://www.compass-project.org/

By adding a few annotations to domain objects, you will reach that level.

Compass provides a simple API for working with Lucene. If you know how to use ORM, then you will feel at home with Compass with simple operations to save and delete and query.

From the site itself. "Based on Lucene's top, Compass simplifies common Lucene usage patterns, such as Google-style searches, indexes, and more advanced concepts like caching and sub indexes. Compass also uses built-in optimizations for parallel commits and merges."

I have used this in the past and I find it wonderful.

Answer 8

This can also be done using the Example criterion in the org.hibernate.criterion package.

 public List findLike(Object entity, MatchMode matchMode) { Example example = Example.create(entity); example.enableLike(matchMode); example.ignoreCase(); return getSession().createCriteria(entity.getClass()).add( example).list(); } 

Another way that I find it useful to do the above.

Answer 9

Starting with Hibernate 5.2, session.createCriteria deprecated. Below is a solution using JPA 2 CriteriaBuilder. It uses like and upper :

  CriteriaBuilder builder = session.getCriteriaBuilder(); CriteriaQuery<Person> criteria = builder.createQuery(Person.class); Root<Person> root = criteria.from(Person.class); Expression<String> upper = builder.upper(root.get("town")); criteria.where(builder.like(upper, "%FRAN%")); session.createQuery(criteria.select(root)).getResultList();