First create a Postgres Function
-- This function is for working around the lack of support for ILIKE operator in Hibernate
CREATE OR REPLACE FUNCTION ILIKE_FN(name text, name_arg text) RETURNS bool
LANGUAGE SQL
RETURN name ILIKE name_arg;
Then do the following in your CriteriaBuilder
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> q = cb.createQuery(Person.class);
Root<Person> root = q.from(Person.class);
root.fetch("metadata", JoinType.LEFT);
List<Predicate> predicates = new ArrayList<>();
if(personFilter.getName() != null){
// We have created a function named ILIKE_FN in psql that can handle the
// ILIKE operator that hibernate doesn't support
Expression<Boolean> ilikeExp = cb.function(
"ILIKE_FN",
Boolean.class,
root.get("name"),
cb.literal(personFilter.getName().toUpperCase())
);
predicates.add(cb.isTrue(ilikeExp));
}
if(personFilter.getId() != null){
predicates.add(cb.equal(root.get("id"), personFilter.getId()));
}
q.where(predicates.toArray(new Predicate[0]));
TypedQuery<Person> query = entityManager.createQuery(q);
List<Person> persons = query.getResultList();
And you will get the following SQL generated:
select
a1_0.id,
a1_0.description,
a1_0.name,
m1_0.id,
m1_0.key,
m1_0.value,
from
person a1_0
left join
person_metadata m1_0
on a1_0.id=m1_0.person_id
where
ILIKE_FN(a1_0.name,'OLAV GJERDE')