JPA CriteriaQuery - accent insensitive

Question

I am using JPA and PostgreSQL and I want to create a CriteriaQuery and create a query where the accents are not taken into consideration.

Example: if I search the letter 'a', the database should return the values 'ã', 'a', 'á', etc. This should happen to all letters.

This is an example of code where I want to change. In this case, it is only case insensitive, not accent.

    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery query = qb.createQuery(Pessoa.class);

    Root<Pessoa> root = query.from(Pessoa.class);
    query.from(Pessoa.class);
    From from = root;

    Predicate predicate = qb.like(qb.lower(from.get("name")),
            "%" + name+ "%");
    query.where(predicate);

I want to be able to something like this: SELECT * FROM person WHERE unaccent(name) LIKE unaccent('%JOÃO%') using CriteriaQuery — henriquels, Oct 21 '15 at 17:47
so use CriteriaBuilder.function to invoke "unaccent" (whatever that SQL function is). Makes your code DB dependent, but then for something like this you likely always will be — Neil Stockton, Oct 21 '15 at 18:18

score 3 · Accepted Answer · answered Oct 21 '15 at 18:45

I used the function of the criteriaBuilder and could get what I wanted. I just called qb.function in the qb.like function.

I did it this way:

Predicate predicate = qb.like(qb.function("unaccent",         
String.class,qb.lower(from.get("name"))),
"%" + removeAccents(name) + "%");

score 2 · Answer 2 · edited Jul 28 '20 at 17:13

2

Using Oracle convert function with SF7ASCII (Portuguese) as parameter:

Predicate p = cb.like(
                    cb.function("convert", String.class, cb.lower(root.get(Api_.name)), cb.literal("SF7ASCII")),
                    "%" + StringUtils.stripAccents(name.toLowerCase()) + "%");

edited Jul 28 '20 at 17:13

xKobalt

1,498
2
13
19

answered Jul 28 '20 at 13:08

Leonardo Ferreira

21
1

JPA CriteriaQuery - accent insensitive

2 Answers2