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

java sql hibernate hql derby
gotch4 picture gotch4 · Nov 2, 2011 · Viewed 15.6k times · Source

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

Xavi López picture Xavi López · Nov 2, 2011

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.

Related questions

Inner Joins Query in HQL

I'm unable to execute HQL for Inner Joins, Query is executing correct at sql but not in HQL. I don't know where i'm missing. Your help is appreciable. ***Error***: org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ON near line 1, column 148 […

Read More
"Not supported for DML operations" with simple UPDATE query

I'm getting the error Not supported for DML operations when I use the following HQL... @Query("UPDATE WorkstationEntity w SET w.lastActivity = :timestamp WHERE w.uuid = :uuid") void updateLastActivity(@Param("uuid") String uuid, @Param("timestamp") Timestamp timestamp); What could be …

Read More
Hibernate 5.2 version -> A lot of Query methods deprecate?

I'm new to Hibernate. I'm trying to get a list of first name and last name of all administrators. There are two warnings in my following code. I already tried to search a lot online. 1) Query is a raw type. …

Read More