Case-insensitive search using Hibernate

Question

I'm using Hibernate for ORM of my Java app to an Oracle database (not that the database vendor matters, we may switch to another database one day), and I want to retrieve objects from the database according to user-provided strings. For example, when searching for people, if the user is looking for people who live in 'fran', I want to be able to give her people in San Francisco.

SQL is not my strong suit, and I prefer Hibernate's Criteria building code to hard-coded strings as it is. Can anyone point me in the right direction about how to do this in code, and if impossible, how the hard-coded SQL should look like?

Thanks,

Yuval =8-)

Answer 1

Most default database collations are not case-sensitive, but in the SQL Server world it can be set at the instance, the database, and the column level.

Answer 2

You could look at using Compass a wrapper above lucene.

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

By adding a few annotations to your domain objects you get achieve this kind of thing.

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

From the site itself. "Building on top of Lucene, Compass simplifies common usage patterns of Lucene such as google-style search, index updates as well as more advanced concepts such as caching and index sharding (sub indexes). Compass also uses built in optimizations for concurrent commits and merges."

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

Answer 3

The usual approach to ignoring case is to convert both the database values and the input value to upper or lower case - the resultant sql would have something like

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

In hibernate criteria restrictions.like(...).ignoreCase()

I'm more familiar with Nhibernate so the syntax might not be 100% accurate

for some more info see pro hibernate 3 extract and hibernate docs 15.2. Narrowing the result set

Answer 4

For the simple case you describe, 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 5

You also do not have to put in the '%' wildcards. You can pass MatchMode in to tell the search how to behave. Begging, Anywhere, Exact, and End matches I believe are the options.

Answer 6

If you use Spring's HibernateTemplate to interact with Hibernate, here is how you would do a case insensitive search on a user's email address:

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

Answer 7

Criteria crit = session.createCriteria(Person.class);

crit.add(Restrictions.ilike('town', 'fran', MatchMode.ANYWHERE);

List results = crit.list();