CODEWITHSUNDEEP
javahibernate
jon077
jon077

Reputation: 10449

Case-insensitive equals using Hibernate Criteria

I've seen Restrictions.ilike('property', '%value%'), but would like to generate SQL like: lower(property) = 'value'. Any ideas?

I used: 

Restrictions.eq("email", email).ignoreCase()

since Expression is deprecated. The SimpleExpression will call toLowerCase() on the value, so it is not necessary to do it beforehand.

See: SimpleExpression source

Upvotes: 62

Views: 100861

Answers (7)

muraguri2005
muraguri2005

Reputation: 129

You can use Criterion.ilike. For more information check this link.

Upvotes: 0

daemonThread
daemonThread

Reputation: 1024

If requirement is lower(property) = 'value' than best way is to use Restrictions.eq with ignoreCase() instead of using ilike cuz here the exact value is know. ilike is beneficial to search a pattern or when we are not aware of the exact value. Only problem with ignoreCase() is, it will not restrict your results to lower case and also include results with other cases , but i think is is the only available option hibernate offers.

 Criteria criteria = session.createCriteria(ClassName.class);
 criteria.add(Restrictions.eq("PROPERTY", propertyName).ignoreCase()).uniqueResult();

Now if I understand your question correctly, your db has these values: ABC, abc, aBC, aBc...and so on, you want to convert them to lower case and then compare with some value say "ABC" or "abc". YOu can do this by further processing the list you got.

You can save this result in a list.

List<ClassName> listOfAllMatches =  criteria.list(); // gives all matched results (ABC, abc, aBC, aBc)



List<ClassName> finalResult = listOfAllMatches.stream().map(x -> x.getPropertyName().toLowerCase()).filter(y ->y.getPropertyName()== value).collect(Collectors.toList());

//convert this field to lower case using map() and filter your results with filter() function.

YOU can further add this list to Criteria and process it.

Upvotes: 1

Satyam Koyani
Satyam Koyani

Reputation: 4274

As Andy's answer suggests, this for case-insensitive searches but it is also works through to Hibernate version 4.1:

crit(Restrictions.eq("firstName", firstName).ignoreCase());

Versions 4.1.1 and later of Hibernate do not support the ignoreCase() method on Restriction.eq(). For that, we have to use ilike with MatchMode.

Criteria crit = session.createCriteria(ENTITY.class);
crit.add(Restrictions.ilike('PROPERTY NAME', 'VALUE', MatchMode.ANYWHERE));

As an example, for a USER entity with id, name, surname properties, a case-insensitive search based on name will be: 

Criteria crit = session.createCriteria(USER.class);
crit.add(Restrictions.ilike('name', 'Satyam', MatchMode.ANYWHERE));

This will return all results, case insensitive.

Upvotes: 14

Mohd Kose Avase
Mohd Kose Avase

Reputation: 1

crit(Restrictions.eq("firstName", firstName).ignoreCase()); works.

This did returns a SimpleExpression Object but It generates lower(firstname) query. So this works.

Upvotes: -1

Andy A
Andy A

Reputation: 4301

Expression is now deprecated. Use Restrictions instead ...

crit(Restrictions.eq("firstName", firstName).ignoreCase());

Upvotes: 79

John Wagenleitner
John Wagenleitner

Reputation: 11035

Be careful of using ilike because it would allow someone to enter things like "test%" and match. I use the following to do a case-insensitive equal in one app:

...
Criteria crit=session.createCriteria(Event.class);
crit.add(Expression.eq("rsvpCode","test1").ignoreCase());
...

Upvotes: 73

Uri
Uri

Reputation: 89729

I'm not absolutely sure, but when you use Restriction.eq you obtain a SimpleExpression object, and that object suppports an ignoreCase() operation which I've never tried using but sounds like it could make a difference.

Kudos to Hibernate for not documenting what this method actually does.

Upvotes: 7

Related Questions