Olav Grønås Gjerde

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')