Hibernate + Postgresql with case insensitive searching

Question

Hi, I've been googling around for a straight-forward solution on getting our new postgresql + hibernate setup working with case insensitive search, without luck. We had previously been using mysql which has a policy on case insensative searching however Postgresql seems to lack this.

For example, I'd like Hibernate/Postgresql to return the same result for each of the below:

SELECT * FROM Foo where bar = 'Hello World';

and

SELECT * FROM Foo where bar = 'hello wOrlD';

The only solution I've found is to somehow embed an 'ilike' keyword into the resulting queries, however you'd think there'd be some kind of configuration in Hibernate that would cater for this? My experience with Hibernate and Postgresql is limited, so any input would be appreciated.

Thanks

Answer 1

In SQL I've played the trick before, which I believe is SQL compliant in most RDBMS's:

SELECT * FROM UPPER(bar) = 'HELLO WORLD'

You can capitalize before you pass the parameter in. This would probably be the same as using the Restrictions.ilike() criteria for a query in Hibernate.

Answer 2

There is not a way to just simply tell Postgres to ignore case.

If changing the type of all the columns where you need this comparison is an option, then look at the citext datatype.

Answer 3

Spring can be handy in configuring things like how you connect hibernate and postgres together, transaction (if needed), and how you obtain A HibernateSession object. In fact using spring you can use HibernateTemplates (a Spring object) that will proxy a lot of stuff for you.

However, you still have program-ability around what queries you run. In this case using a HibernateTemplate you could write:

String parameter = "hello wOrlD"
DetachedCriteria criteria = DetachedCriteria.forClass(objectClass)
                             .add(Restrictions.ilike("name", parameter)));
List<Object> result = template.findByCriteria(criteria);

or you could look into using Restrictions.eq or sqlRestriction to implement the sql piece of it.

Answer 4

Thanks for both your answers; there does not seem to be an easy way to get around this problem on a database/jdbc level. So our solution here was, albeit not being optimal:

1. Enforce casing standards in the logical data.
2. When we receive requests from a client, the request string is filtered through a toLowerCase() function.

When we were using mysql it was just a matter of setting a policy in my.cfg/ini - I don't understand why it's made more complicated in postgresql. Such is life though eh?