Accent insensitive Hibernate

Trabalhamos em minha empresa com Oracle e Postgre, necessitava de uma forma de rodar em nossa aplicação uma consulta de busca palavras ignorando acentos e caixa.
O banco de nossos clientes não são padronizados, ou seja, temos bancos sem controle de caixa e temos banco que gravam somente caixa alta, outros buscam ignorando acentos e outros não ignoram.
Em nosso negócio não temos como opção gravar as palavras sem acentuação.
Por isso a necessidade de realizar buscas de palavras ignorando acentos e caixa.
Passei algumas horas pesquisando e encontrei uma forma que resolveu meu problema e acho que ajudará alguns outros:

        String parametro = Normalizer.normalize("antiá", Normalizer.Form.NFD).replaceAll("[^\\p{ASCII}]", "").toUpperCase();

Aqui eu recebo a palavra digitada pelo usuário, neste caso “antiá” e a transformo em ANTIA e retorno como parâmetro para parametro
Depois eu crio a seguinte Query:

String query = " FROM Medicamento cm WHERE upper(TRANSLATE(cm.dsMedicamento,'ÀÁáàÉÈéèÍíÓóÒòÚú','AAaaEEeeIiOoOoUu')) LIKE  '" + parametro + "%'";

Onde dsMedicamento é um parâmetro String da classe Medicamento.

Testei tanto no Oracle 10 como no Postgre 8.3 e em ambos tive o resultado correto.

Agora eu tenho uma segunda missão, aqui na empresa utilizamos Criteria para realizar as consultas, e gostaria de ajuda para converter essa Query em Criteria.
Alguém pode me dar um help?

Não há um padrão para todos os bancos, mas no MySQL as consultas com like ignoram acentos e cases. Já no PostgreSQL você pode usar ilike para ignorar o case, porém as buscas consideram acentos. Já no Oracle não existe ilike e ele considera acentos nas buscas.

Usando o criteria do Hibernate você pode usar o ILIKE para fazer as buscas independente do case, porém para acentos não tem. Mas cabe lembrar que qualquer function que você escrever no HQL e não for padrão o Hibernate simplesmente envia o comando para o banco resolver. Mas no criteria não lembro se dá para colocar funções customizadas.

Uma sugestão, aí depende se você pode ou não fazer isso no seu projeto, é usar o Lucene integrado ao Hibernate Search. Buscas complexa no banco de dados às vezes são complexas, como é seu caso. O Hibernate Search te permite fazer alguma coisa mais inteligente e até mais rápida que consultas complexas no banco.

Ahh, outra sugestão é usar soundex. Você pode criar uma coluna de cache para o soundex do nome do remédio na inclusão do medicamento. Quando você precisar pesquisar pode usar algo como:

E se vc tentasse colocar essa parte do Translate no: .add(Restrictions.sqlRestriction("(TRANSLATE…))

Eu tentei testar, mas no Mysql que tenho aqui disponivel para teste, ele não aceita o comando TRANSLATE.

Pessoal, antes de mais nada obrigado pela ajuda…

[quote=romarcio]E se vc tentasse colocar essa parte do Translate no: .add(Restrictions.sqlRestriction("(TRANSLATE…))

Eu tentei testar, mas no Mysql que tenho aqui disponivel para teste, ele não aceita o comando TRANSLATE.[/quote]

Romarcio, sua dica deu certo:

Criteria select = sessao.createCriteria(ClassificacaoMedicamento.class);
        String parametro = Normalizer.normalize("ANTIÁ", Normalizer.Form.NFD).replaceAll("[^\\p{ASCII}]", "").toUpperCase();
        select.add(Restrictions.sqlRestriction("TRANSLATE({alias}.ds_classificacao_medicamento,'ÀÁáàÉÈéèÍíÓóÒòÚú','AAaaEEeeIiOoOoUu') LIKE  '" + parametro + "%'"));

Pena que o nome da coluna não pode ser o parâmetro da classe, mas o nome da coluna na tabela, mas isso não é problema…

Brigadão!!!

Boa Tarde pessoal, td bem??

Como ficaria minha criteria usando o normalizer???

@SuppressWarnings("unchecked") public List<Funcionario> pesquisaFuncionarios(String nome, String cpf, String rg) { Criteria c = session.createCriteria(Funcionario.class); String parameter = Normalizer.normalize(nome, Normalizer.Form.NFKD).replaceAll( "\\p{InCombiningDiacriticalMarks}+", ""); List<Funcionario> results = new ArrayList<Funcionario>(); c.add(Restrictions.ilike("nome", nome, MatchMode.ANYWHERE)); c.add(Restrictions.like("cpf", cpf, MatchMode.ANYWHERE)); c.add(Restrictions.like("rg", rg, MatchMode.ANYWHERE)); // c.add(Restrictions.eq("orgaogestor.cod_orgaogestor", orgao)); results = (List<Funcionario>) c.list(); return results; }

Alguma ajuda??

valeu.

Fantástico. Essa dica me ajudou bastante.

Obrigado a todos!