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%"));

score 14 · Accepted Answer · answered Mar 01 '11 at 22:02

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.

@mmcrae yes, it should work. What happens when you try using it? — JB Nizet, Jan 30 '15 at 20:08

score 2 · Answer 2 · answered Sep 05 '17 at 18:42

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(?)

answered Sep 05 '17 at 18:42

Tom Chamberlain

2,955
20
24

score -1 · Answer 3 · answered Jul 02 '12 at 04:56

-1

In PostgreSQL there is 'ILIKE' and 'LIKE' different operators, So case sensitivity in independent of column properties that's the reason this thing happens

answered Jul 02 '12 at 04:56

Charudatta

17

score -1 · Answer 4 · edited Nov 15 '14 at 08:20

-1

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%"));

edited Nov 15 '14 at 08:20

Gareth Davis

27,701
12
73
106

answered Nov 15 '14 at 08:07

Ch Pratap

7
1

The second comment line in this example contradicts the first line in this answer! – Tom Chamberlain Sep 05 '17 at 18:38

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

4 Answers4