Does HQL have an equivalent for Restrictions.ilike (for case-insensitive matching)?

Question

I wrote a project for Hibernate+MySQL. Now I'm porting it to Derby (for a number of reasons).

Now I discovered that Derby is case sensitive when using LIKE in queries. This could be solved using Restrictions.ilike(...) in Criteria queries... but I've many complex HQL queries that use that. Is there a way to have a functionality similar to ilike in HQL?

Answer 1

There is no ilike equivalent functionality in HQL. As Konstantin has already pointed out in his suggestion, your best choice is to tune the database connection and set collation to TERRITORY_BASED:SECONDARY, as explained in this JIRA: DERBY-1748: Global case insensitive setting.

Take into account that all equalities (=) and likes will be case insensitive. This might go a bit too far, and be not suitable for your particular situation.

Another way of addressing this would be creating function-based indexes (if Derby supports them, of course) and tune your HQL to combine like and lower like this.

Query q = session.createQuery("... WHERE lower(entity.field) like ?)");
q.setString(0, '%' + variable.toLowerCase() + '%');

If Derby doesn't support FBI's (I think it doesn't), you could also create trigger-filled columns with the lower values and index them.

UPDATE It seems to be possible to define derived/autogenerated columns, as explained in this other JIRA: JIRA-481: implement SQL generated columns.