16

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?

Jonik
  • 80,077
  • 70
  • 264
  • 372
gotch4
  • 13,093
  • 29
  • 107
  • 170

3 Answers3

20

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 lower(?)");
q.setString(0, '%' + variable + '%');

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.

Xavi López
  • 27,550
  • 11
  • 97
  • 161
  • but gotch4 prefers to keep his HQL queries – Konstantin Pribluda Nov 02 '11 at 10:06
  • He has no other option if he doesn't want to change the connection collation. And I understood he doesn't want to turn them into criteria queries. – Xavi López Nov 02 '11 at 10:18
  • 1
    I understand him - could be hell of a refactoring and testing – Konstantin Pribluda Nov 02 '11 at 10:27
  • I started the troublesome work of converting everything to Criteria... this way I hope I will avoid this in the future. – gotch4 Nov 02 '11 at 10:51
  • It's alright. Just take into account that using `ilike` will be the same as modifying the HQL with `WHERE lower(entity.field) LIKE ?` and applying `toLowerCase()` to the parameter. If those `entity.field` were indexed you can run into performance issues. – Xavi López Nov 02 '11 at 10:59
  • 4
    if you like, you can use "lower(entity.field) like lower(?)" too, if you wish to let DB to process the String lower case. In my case I prefer this way to let the statement easier to read... – Rodrigo Leitão Jul 31 '12 at 18:00
  • 1
    There's a subtle bug lurking in the example code here: There is no guarantee that the JDK's String#toLowerCase() will actually always produce the same result as the "lower()" function on the database server. So unless you want spent hours debugging (and yes, this has happened to me), I suggest to always do the conversion on the DB-side. – Tobias Gierke Mar 30 '23 at 13:47
  • @TobiasGierke That is a very valid point. I have updated the answer to squash that out. As long as DB-side `lower('%')` is guaranteed to remain the same :) Concatenating `'%' || lower(?) || '%'` in the query itself probably a safer move, but not sure right now how would that play out if possible at all in a HQL query. – Xavi López Mar 30 '23 at 14:37
2

I'm affraid, there is no way to achieve this via HQL, as it is just translated to native SQL. I think it could be possible by tuning schema:

http://old.nabble.com/case-insensitive-searching-td17756019.html

Konstantin Pribluda
  • 12,329
  • 1
  • 30
  • 35
0

a easy workaround would be to convert both words to to upper case (or lower case) something like this:

from DomesticCat cat where upper(cat.name) like 'FRI%'

references: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html

luisZavaleta
  • 1,160
  • 11
  • 21