Difference between Restrictions.like and .ilike in Hibernate Criteria API

Question

Hibernate's Criteria API has Restrictions.ilike function which has the following contract:

A case-insensitive "like", similar to Postgres ilike operator

That's cool. But the same class also has like function, having much more vague contract:

Apply a "like" constraint to the named property

example

Criteria cr = session.createCriteria(Employee.class);  // To get records having fistName starting with zara cr.add(Restrictions.like("firstName", "zara%"));  // Case sensitive form of the above restriction. cr.add(Restrictions.ilike("firstName", "zara%")); 

Answer 1

The case-sensitivity of the like operator, in MySQL, depends on the type of the columns and on their collation (see http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html).

Hibernate is "just" a layer on top of SQL. The contract of the like operator is to issue a SQL like operator. If you want it to be the same for MySQL and PostgreSQL, then choose the right types and collations in the database schemas. But Hibernate can't magically make all the databases use the same rules.

Design your application, choose and design your database so that the behavior you observe is the behavior you expect.

PS : but I agree that the javadoc of Hibernate is... perfectible.

Answer 2

ilike will be doing a lower of your input value, and a lower of the column value e.g.

select * from table where lower(column) like lower(?)