Hibernate Query using Like and Where

Asked

Viewed 68 times

0

Beauty guys ? So I’m doing a query with Hibernate, which is the following:

Query query = session.createQuery("SELECT c FROM Cliente c WHERE c.filial =:filial AND c.ativo =:ativo AND c.tipoCliente =:tipoCliente AND c.cnpj_cpf LIKE :cnpj_cpf_formatado OR  REPLACE(REPLACE(REPLACE( c.cnpj_cpf, '.', '' ),'/',''),'-','') LIKE  :cnpj_cpf OR c.razaoSocialNome LIKE :razaoSocialNome", Cliente.class)
            .setParameter("razaoSocialNome", "%" + texto.toUpperCase() + "%")
            .setParameter("cnpj_cpf", "%" + texto.toUpperCase() + "%")
            .setParameter("cnpj_cpf_formatado", "%" + texto.toUpperCase() + "%")
            .setParameter("ativo", true)
            .setParameter("filial", filial)
            .setParameter("tipoCliente", tipoCliente)
            .setHint(QueryHints.HINT_CACHEABLE, true)
            .setHint(QueryHints.HINT_CACHE_REGION, "CACHE_REGION_Cliente_todosClientesAtivosPesquisaAutoComplete");

    List<Cliente> resultList = null;
    try {
        resultList = query
                .setMaxResults(limite)
                .getResultList();
    } catch (Exception ignored) {
    }

    return resultList;

The problem is as follows, There are two Customers who are equal, minus the type Customer, that one is the type AFFILIATE, and the other CUSTOMER. I’m doing the query by searching for Tipocliente.CLIENT, and not for the affiliate, but I don’t know why, because of the like, comes together in the affiliate results too, if I take the Ikes, only the CLIENT type comes. Do you have a solution for that ?

java postgresql hibernate

  • your problem is not necessarily related to the Ikes, but to the 'or' apparently, please give more details of how your data is and what you are filling in the parameters

  • Opa Lucaz beauty ? thanks for the reply, I will update there in the post!

1 answer

0


Your JPQL is with the OR without the parentheses.

Make sure this new JPQL can help you:

SELECT c
FROM Cliente c
WHERE c.filial =:filial
    AND c.ativo =:ativo
    AND c.tipoCliente =:tipoCliente
    AND ( c.cnpj_cpf LIKE :cnpj_cpf_formatado
    OR REPLACE(REPLACE(REPLACE( c.cnpj_cpf, '.', '' ),'/',''),'-','') LIKE  :cnpj_cpf
    OR c.razaoSocialNome LIKE :razaoSocialNome )

Browser other questions tagged java postgresql hibernate

You are not signed in. Login or sign up in order to post.