JPA2: insensitivo a maiúsculas e minúsculas como em qualquer lugar

Eu tenho usado restrições de hibernação no JPA 1.0 (driver de hibernação). Há Restrictions.ilike("column","keyword", MatchMode.ANYWHERE) que testa se a palavra-chave corresponde à coluna em qualquer lugar e não faz Restrictions.ilike("column","keyword", MatchMode.ANYWHERE) entre maiúsculas e minúsculas.

Agora, estou usando o JPA 2.0 com o EclipseLink como driver, portanto, preciso usar o JPA 2.0 “Restrictions” integrado. Eu encontrei CriteriaBuilder e método like , eu também descobri como fazer correspondência em qualquer lugar (embora seja horrível e manual), mas ainda não descobri como fazê-lo insensível a maiúsculas e minúsculas.

Existe a minha solução atual:

 CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery query = builder.createQuery(User.class); EntityType type = em.getMetamodel().entity(User.class); Root root = query.from(User.class); // Where // important passage of code for question query.where(builder.or(builder.like(root.get(type.getDeclaredSingularAttribute("username", String.class)), "%" + keyword + "%"), builder.like(root.get(type.getDeclaredSingularAttribute("firstname", String.class)), "%" + keyword + "%"), builder.like(root.get(type.getDeclaredSingularAttribute("lastname", String.class)), "%" + keyword + "%") )); // Order By query.orderBy(builder.asc(root.get("lastname")), builder.asc(root.get("firstname"))); // Execute return em.createQuery(query). setMaxResults(PAGE_SIZE + 1). setFirstResult((page - 1) * PAGE_SIZE). getResultList(); 

Questões:

Existe alguma function como no driver do Hibernate?

Estou usando os critérios do JPA 2.0 corretamente? Esta é uma solução incômoda e desconfortável em comparação com as Restrições de Hibernação.

Ou alguém pode me ajudar a mudar minha solução para não diferenciar maiúsculas de minúsculas, por favor?

Muito obrigado.

Pode parecer um pouco estranho no começo, mas é seguro para o tipo. Construir consultas de strings não é, portanto, você percebe erros no tempo de execução em vez de no tempo de compilation. Você pode tornar as consultas mais legíveis usando recuos ou tomando cada etapa separadamente, em vez de gravar uma cláusula WHERE inteira em uma única linha.

Para tornar sua consulta insensível a maiúsculas e minúsculas, converta sua palavra-chave e o campo comparado em minúsculas:

 query.where( builder.or( builder.like( builder.lower( root.get( type.getDeclaredSingularAttribute("username", String.class) ) ), "%" + keyword.toLowerCase() + "%" ), builder.like( builder.lower( root.get( type.getDeclaredSingularAttribute("firstname", String.class) ) ), "%" + keyword.toLowerCase() + "%" ), builder.like( builder.lower( root.get( type.getDeclaredSingularAttribute("lastname", String.class) ) ), "%" + keyword.toLowerCase() + "%" ) ) ); 

Esse trabalho pra mim:

 CriteriaBuilder critBuilder = em.getCriteriaBuilder(); CriteriaQuery critQ = critBuilder.createQuery(Users.class); Root root = critQ.from(Users.class); Expression path = root.get("lastName"); Expression upper =critBuilder.upper(path); Predicate ctfPredicate = critBuilder.like(upper,"%stringToFind%") critQ.where(critBuilder.and(ctfPredicate)); em.createQuery(critQ.select(root)).getResultList(); 

Como comentei na resposta (atualmente) aceita, existe uma armadilha usando por um lado a function lower() DBMS e por outro lado o String.toLowerCase() do java String.toLowerCase() que ambos os methods não são garantidos para fornecer a mesma saída para o mesmo cadeia de input.

Eu finalmente encontrei uma solução muito mais segura (mas não à prova de balas) que é deixar o DBMS fazer toda a redução usando uma expressão literal:

 builder.lower(builder.literal("%" + keyword + "%") 

Então, a solução completa seria semelhante a:

 query.where( builder.or( builder.like( builder.lower( root.get( type.getDeclaredSingularAttribute("username", String.class) ) ), builder.lower(builder.literal("%" + keyword + "%") ), builder.like( builder.lower( root.get( type.getDeclaredSingularAttribute("firstname", String.class) ) ), builder.lower(builder.literal("%" + keyword + "%") ), builder.like( builder.lower( root.get( type.getDeclaredSingularAttribute("lastname", String.class) ) ), builder.lower(builder.literal("%" + keyword + "%") ) ) ); 

Editar:
Como @cavpollo me pediu para dar exemplo, eu tive que pensar duas vezes sobre a minha solução e percebi que não é muito mais seguro do que a resposta aceita:

 DB value* | keyword | accepted answer | my answer ------------------------------------------------ elie | ELIE | match | match Élie | Élie | no match | match Élie | élie | no match | no match élie | Élie | match | no match 

Ainda assim, prefiro minha solução, pois não compara o resultado de duas funções diferentes que deveriam funcionar da mesma forma. Eu aplico a mesma function em todos os arrays de caracteres para que a comparação entre a saída fique mais “estável”.

Uma solução à prova de bala envolveria o código de idioma para que o lower() do SQL seja capaz de reduzir corretamente os caracteres acentuados. (Mas isso vai além do meu humilde conhecimento)

* Valor do database com PostgreSQL 9.5.1 com localidade ‘C’

Mais fácil e eficiente para impor insensibilidade a maiúsculas e minúsculas no database do que a JPA.

  1. Sob os padrões do SQL 2003, 2006, 2008, pode fazer isso adicionando COLLATE SQL_Latin1_General_CP1_CI_AS OU COLLATE latin1_general_cs ao seguinte:

    • Definição de Coluna

       CREATE TABLE  (   [DEFAULT...] [NOT NULL|UNIQUE|PRIMARY KEY|REFERENCES...] [COLLATE ], ... )
    • Definição de Domínio

       CREATE DOMAIN  [ AS ]  [ DEFAULT ... ] [ CHECK ... ] [ COLLATE  ] 
    • Definição do conjunto de caracteres

       CREATE CHARACTER SET  [ AS ] GET  [ COLLATE  ] 

    Para obter uma descrição completa, consulte: http://savage.net.au/SQL/sql-2003-2.bnf.html#column%20definition http://dev.mysql.com/doc/refman/5.1/en/ charset-table.html http://msdn.microsoft.com/pt-br/library/ms184391.aspx

  2. No Oracle, pode definir os parâmetros NLS Session / Configuration

      SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC; SQL> ALTER SESSION SET NLS_SORT=BINARY_CI; SQL> SELECT ename FROM emp1 WHERE ename LIKE 'McC%e'; ENAME ---------------------- McCoye Mccathye 

    Ou, no init.ora (ou nome específico do SO para o arquivo de parâmetro de boot):

     NLS_COMP=LINGUISTIC NLS_SORT=BINARY_CI 

    Tipos binários podem ser insensíveis a maiúsculas / minúsculas ou insensíveis a maiúsculas e minúsculas. Quando você especifica BINARY_CI como um valor para NLS_SORT, ele designa uma sorting que é sensível ao acento e não diferencia maiúsculas de minúsculas. BINARY_AI designa um tipo binário insensível a maiúsculas e minúsculas e sem distinção entre maiúsculas e minúsculas. Você pode querer usar uma sorting binária se a ordem de sorting binária do conjunto de caracteres for apropriada para o conjunto de caracteres que você está usando. Use o parâmetro de session NLS_SORT para especificar uma sorting sem distinção entre maiúsculas e minúsculas ou sem distinção entre maiúsculas e minúsculas:

     Append _CI to a sort name for a case-insensitive sort. Append _AI to a sort name for an accent-insensitive and case-insensitive sort. 

    Por exemplo, você pode definir NLS_SORT para os seguintes tipos de valores:

     FRENCH_M_AI XGERMAN_CI 

    Definir NLS_SORT para qualquer coisa diferente de BINARY [com _CI opcional ou _AI] faz com que uma sorting use uma varredura de tabela completa, independentemente do caminho escolhido pelo otimizador. BINARY é a exceção porque os índices são criados de acordo com uma ordem binária de chaves. Assim, o otimizador pode usar um índice para satisfazer a cláusula ORDER BY quando NLS_SORT estiver definido como BINARY. Se NLS_SORT for configurado para qualquer sorting lingüística, o otimizador deverá include uma varredura completa da tabela e uma sorting completa no plano de execução.

    Ou, se NLS_COMP estiver definido como LINGUISTIC, como mostrado acima, as configurações de sorting poderão ser aplicadas localmente a colunas indexadas, em vez de globalmente no database:

     CREATE INDEX emp_ci_index ON emp (NLSSORT(emp_name, 'NLS_SORT=BINARY_CI')); 

    Referência: Ordenação Linguística ORA 11g e Busca de Cadeias ORA 11g Configurando um Ambiente de Suporte à Globalização

Solução alternativa desesperada para OpenJPA 2.3.0 e Postgresql

 public class OpenJPAPostgresqlDictionaryPatch extends PostgresDictionary { @Override public SQLBuffer toOperation(String op, SQLBuffer selects, SQLBuffer from, SQLBuffer where, SQLBuffer group, SQLBuffer having, SQLBuffer order, boolean distinct, long start, long end, String forUpdateClause, boolean subselect) { String whereSQL = where.getSQL(); int p = whereSQL.indexOf("LIKE"); int offset = 0; while (p != -1) { where.replaceSqlString(p + offset, p + offset + 4, "ILIKE"); p = whereSQL.indexOf("LIKE", p + 1); offset++; } return super.toOperation(op, selects, from, where, group, having, order, distinct, start, end, forUpdateClause, subselect); } } 

Esta é uma solução frágil e feia para fazer a operação LIKE insensível a maiúsculas e minúsculas com o database OpenJPA e Postgresql. Ele substitui o operador LIKE pelo operador ILIKE no SQL gerado.

É uma pena que o OpenJPA DBDictionary não permita alterar nomes de operadores.

Por favor, considere usar

 CriteriaBuilder.like(Expression x, Expression pattern, char escapeChar); 

para correspondência em qualquer lugar.