CODEWITHSUNDEEP
hibernatepostgresqlcase-insensitive
Blaskowitz
Blaskowitz

Reputation: 51

Hibernate + Postgresql with case insensitive searching

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

Upvotes: 5

Views: 5408

Answers (4)

Blaskowitz
Blaskowitz

Reputation: 51

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?

Upvotes: 0

Matt
Matt

Reputation: 1119

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.

Upvotes: 1

cope360
cope360

Reputation: 6334

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.

Upvotes: 0

Matt
Matt

Reputation: 1119

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.

Upvotes: 2

Related Questions