hibernate jpa criteriabuilder ignore case queries

Question

How to do a like ignore case query using criteria builder. For description property I want to do something like upper(description) like '%xyz%'

I have the following query

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery<Person> personCriteriaQuery = criteriaBuilder.createQuery(Person.class);
    Root<Person> personRoot = personCriteriaQuery.from(Person.class);

    personCriteriaQuery.select(personRoot);
    personCriteriaQuery.where(criteriaBuilder.like(personRoot.get(Person_.description), "%"+filter.getDescription().toUpperCase()+"%"));
    List<Person> pageResults = entityManager.createQuery(personCriteriaQuery).getResultList();

Sorry, an off-topic question, when do you use the class **Person_**?, I'm so curious why use underscores in a class name. — Marcos Echagüe, Sep 04 '19 at 15:44
@MarcosEchagüe This classes are generated automatically with `hibernate-jpamodelgen`. They are used to get the field names instead of using raw Strings. Also refactoring becomes easier with it. For more information have a look at https://hibernate.org/orm/tooling/ — Felix Seifert, Oct 02 '19 at 05:21

score 141 · Accepted Answer · edited Jan 28 '21 at 18:14

141

There is a CriteriaBuilder.upper() method:

personCriteriaQuery.where(criteriaBuilder.like(
    criteriaBuilder.upper(personRoot.get(Person_.description)), 
    "%"+filter.getDescription().toUpperCase()+"%"));

edited Jan 28 '21 at 18:14

hooknc

4,854
5
31
60

answered Jan 08 '11 at 19:58

axtavt

239,438
41
511
482

Functional, but can be a bit slow on very large data sets, because the db server has to upper all of the records to do the comparison. – Max May 30 '22 at 13:53
You can mitigate this by adding an index on upper/lower (column). – Mariano LEANCE Aug 16 '22 at 19:38
As gagarwa also mentioned (in the other answer's comments), unless the default locale of JRE (since no locale is used in toUpperCase function), and character set of the DB (hiberanate/JPA will be running the query against) match, this may not work for certain locales. oscar-gómez-martin mentioned lowecase could be a workaround for German, but in Turkish (where "i".toUpperCase is NOT "I", and "I".toLowerCase is NOT "i") that wouldn't work either! [Answer](https://stackoverflow.com/a/47477470/381673) from @Ghurdyl tries to tackle this problem. – OzgurH Apr 07 '23 at 21:09

score 30 · Answer 2 · answered Sep 19 '17 at 17:10

30

If the database contains German words with letters like Fußballschuhe in the column, java will modify the parameter in uppercase method to FUSSBALLSCHUHE and the query will not match. Lowercase will work in this case:

personCriteriaQuery.where(criteriaBuilder.like(
    criteriaBuilder.lower(personRoot.get(Person_.description)), 
    "%"+filter.getDescription().toLowerCase()+"%"));

answered Sep 19 '17 at 17:10

Oscar Gómez Martin

301
3
2

4

Maybe pass a Locale into the toUpperCase or toLowerCase method? – Brian Blain Jun 08 '19 at 03:48
The database may also perform the uppercase without considering locale, so make sure that your program is consistent with the database first. – gagarwa May 12 '21 at 07:00

hibernate jpa criteriabuilder ignore case queries

2 Answers2

Linked