JPA - There is the possibility of using Matchmode.ANYWHERE with Predicate

Question

Usually when I need to do a search picking up a result through a single word, using Criteria and Restrictions I do as follows:

criteria.add(Restrictions.ilike("nome", filtro.getNome(), MatchMode.ANYWHERE));

In the above case using Matchmode.ANYWHERE for a product that has in its name the word Notebook, I can type only Notebook that is generated a list with all the names that has Notebook in the name.

Due to a need of the project, I’m having to do the research in a different way, so I decided to do a test using Predicate Follow the complete test class:

import java.io.Serializable;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import br.com.lefacil.papelaria.modelo.ProdutoPap;

public class TesteBDCriteria implements Serializable {

    private static final long serialVersionUID = 1L;

    @SuppressWarnings("unused")
    public static List<ProdutoPap> pesquisarProdutos(){

        EntityManagerFactory factory = Persistence.createEntityManagerFactory("xxPU");
        EntityManager manager = factory.createEntityManager();

        EntityTransaction trx = manager.getTransaction();
        trx.begin();

        CriteriaBuilder cb = manager.getCriteriaBuilder();
        CriteriaQuery<ProdutoPap>
                 cq = cb.createQuery(ProdutoPap.class);

            Root<ProdutoPap> produtoRoot = cq.from(ProdutoPap.class);

            Long idEmpresaLogado = 1L;
            String sku = null;
            String nomeProduto = "Caderno";
            //Caderno Universitário 1 Matéria  50 Folhas - Tilibra
            Predicate prod = cb.equal(produtoRoot.get("empresa"), idEmpresaLogado);
            Predicate condicoes = null;

            if(idEmpresaLogado != null) {
                condicoes = cb.and(prod);
            }

            if(idEmpresaLogado != null && sku == null && nomeProduto != null) {
                System.out.println("Entrou no sku == null nome não nulo");
                Predicate nomes = produtoRoot.get("nome").in(nomeProduto);
                condicoes = cb.and(prod,nomes);
            }

            TypedQuery<ProdutoPap> tq = manager.createQuery(cq.select(produtoRoot).where(condicoes));

            trx.commit();   
            return tq.getResultList();
    }

    public static void main(String[] args) {
        List<ProdutoPap> produtos = pesquisarProdutos();

        for(ProdutoPap p: produtos) {
            System.out.println(p.getNome());
        }
    }
}

Is there any way to use:

Predicate nomes = produtoRoot.get("nome").in(nomeProduto);

I can do the search using only a chunk of data, as in the example with Matchmode.ANYWHERE.

Answer 1

I had the same need and researched several solutions on the Internet that did not use customizations of the SQL dialect of the database used by the JPA provider. The least bad solution I got was as in the following example:

            Expression<String> field = root.get(Entity_.attribute);
            Expression<String> fieldTranslate = criteriaBuilder.function("TRANSLATE", String.class, field, criteriaBuilder.literal(AcentosUtil.getListaComAcentos()), criteriaBuilder.literal(AcentosUtil.getListaSemAcentos()));
            Expression<String> fieldLower = criteriaBuilder.lower(fieldTranslate);              
            String valueTranslate = AcentosUtil.removeAcentos(parameter);
            String valueLower = valueTranslate.toLowerCase();
            Expression<String> valueIlike = criteriaBuilder.literal("%" + valueLower + "%");
            
            predicate = criteriaBuilder.and(predicate, criteriaBuilder.like(fieldLower, valueIlike));