CODEWITHSUNDEEP
javapostgresqljpanon-ascii-charactersaccent-insensitive
henriquels
henriquels

Reputation: 546

JPA CriteriaQuery - accent insensitive

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

Upvotes: 4

Views: 10742

Answers (2)

Leonardo Ferreira
Leonardo Ferreira

Reputation: 21

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

Upvotes: 2

henriquels
henriquels

Reputation: 546

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

Upvotes: 3

Related Questions