hibernate jpa criteriabuilder ignore case queries

Jpa

Jpa Problem Overview


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

Jpa Solutions


Solution 1 - Jpa

There is a CriteriaBuilder.upper() method:

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

Solution 2 - Jpa

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser373201View Question on Stackoverflow
Solution 1 - JpaaxtavtView Answer on Stackoverflow
Solution 2 - JpaOscar Gómez MartinView Answer on Stackoverflow