hibernate jpa criteriabuilder ignore case queries
JpaJpa 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()+"%"));