java - query - JPA2: insensible a mayúsculas y minúsculas, como combinar en cualquier lugar

typedquery

Question

He estado utilizando Restricciones de Hibernate en JPA 1.0 (controlador Hibernate). Se ha definido Restrictions.ilike("column","keyword", MatchMode.ANYWHERE) que prueba si la palabra clave coincide con la columna en cualquier lugar y no distingue entre mayúsculas y minúsculas.

Ahora, estoy usando JPA 2.0 con EclipseLink como controlador, así que tengo que usar el JPA 2.0 incorporado en "Restricciones". Encontré CriteriaBuilder y un método like , también he descubierto cómo hacer que coincida en cualquier lugar (aunque es sabio y manual), pero aún no he descubierto cómo hacerlo sin distinción de mayúsculas y minúsculas.

Ahí está mi solución asombrosa actual:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<User> query = builder.createQuery(User.class);
EntityType<User> type = em.getMetamodel().entity(User.class);
Root<User> 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();

Preguntas:

¿Hay alguna función como en el controlador de Hibernate?

¿Estoy usando los criterios de JPA 2.0 correctamente? Esta es una solución incómoda e incómoda en comparación con las Restricciones de Hibernate.

¿O alguien puede ayudarme a cambiar mi solución para que no distinga entre mayúsculas y minúsculas, por favor?

Muchas gracias.

Answer 1

Como comenté en la respuesta (actualmente) aceptada, hay una falla al usar por un lado la función lower() DBMS y por otro lado el String.toLowerCase() java ya que ambos métodos no tienen garantía de proporcionar el mismo resultado para el mismo cadena de entrada.

Finalmente encontré una solución mucho más segura que es permitir que el DBMS baje todo utilizando una expresión literal:

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

Entonces la solución completa serí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 + "%")
        )
    )
);

Answer 2

Este trabajo para mí:

CriteriaBuilder critBuilder = em.getCriteriaBuilder();

CriteriaQuery<CtfLibrary> critQ = critBuilder.createQuery(Users.class);
Root<CtfLibrary> root = critQ.from(Users.class);

Expression<String> path = root.get("lastName");
Expression<String> upper =critBuilder.upper(path);
Predicate ctfPredicate = critBuilder.like(upper,"%stringToFind%")
critQ.where(critBuilder.and(ctfPredicate));
em.createQuery(critQ.select(root)).getResultList();

Answer 3

Más fácil y más eficiente para aplicar la insensibilidad de mayúsculas y minúsculas dentro de la base de datos que JPA.

1. Bajo los estándares SQL 2003, 2006, 2008, puede hacer esto agregando COLLATE SQL_Latin1_General_CP1_CI_AS O COLLATE latin1_general_cs a lo siguiente:

   • Definición de columna

     CREATE TABLE <table name> (
       <column name> <type name> [DEFAULT...] 
                                 [NOT NULL|UNIQUE|PRIMARY KEY|REFERENCES...]
                                 [COLLATE <collation name>], 
       ...
     )

   • Definición del dominio

     CREATE DOMAIN <domain name> [ AS ] <data type>
       [ DEFAULT ... ] [ CHECK ... ] [ COLLATE <collation name> ]

   • Definición del conjunto de caracteres

     CREATE CHARACTER SET <character set name>
     [ AS ] GET <character set name> [ COLLATE <collation name> ]

   Para una descripción completa de lo anterior, 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/en-us/library/ms184391.aspx

2. En Oracle, puede establecer parámetros de sesión / configuración de NLS

    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

   O bien, en init.ora (o nombre específico del sistema operativo para el archivo de parámetros de inicialización):

   NLS_COMP=LINGUISTIC
   NLS_SORT=BINARY_CI

   Los géneros binarios pueden ser insensibles a mayúsculas y minúsculas o insensibles a los acentos. Cuando especifica BINARY_CI como un valor para NLS_SORT, designa una ordenación que es sensible al acento y no distingue entre mayúsculas y minúsculas. BINARY_AI designa una ordenación binaria insensible a los acentos y que no distingue entre mayúsculas y minúsculas. Es posible que desee utilizar una clasificación binaria si el orden de clasificación binario del conjunto de caracteres es apropiado para el conjunto de caracteres que está utilizando. Use el parámetro de sesión NLS_SORT para especificar una clasificación insensible a mayúsculas y minúsculas o insensible a acentos:

   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 ejemplo, puede establecer NLS_SORT a los siguientes tipos de valores:

   FRENCH_M_AI
   XGERMAN_CI

   Establecer NLS_SORT en cualquier otra cosa que no sea BINARY [con _CI o _AI opcional] hace que una ordenación utilice una exploración de tabla completa, independientemente de la ruta elegida por el optimizador. BINARY es la excepción porque los índices se crean de acuerdo con un orden binario de claves. Por lo tanto, el optimizador puede usar un índice para satisfacer la cláusula ORDER BY cuando NLS_SORT se establece en BINARY. Si NLS_SORT se establece en cualquier clasificación lingüística, el optimizador debe incluir una exploración de tabla completa y una clasificación completa en el plan de ejecución.

   O bien, si NLS_COMP está configurado como LINGÜÍSTICO, como se indicó anteriormente, la configuración de clasificación se puede aplicar localmente a las columnas indexadas, en lugar de a nivel global en la base de datos:

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

   Referencia: ORA 11g Clasificación lingüística y búsqueda de cadenas ORA 11g Configuración de un entorno de soporte de globalización

Answer 4

Por favor considera usar

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

para hacer coincidir en cualquier lugar.

Answer 5

Puede parecer un poco incómodo al principio, pero es seguro para el tipo. Crear consultas a partir de cadenas de caracteres no lo es, por lo que observa errores en el tiempo de ejecución en lugar de en tiempo de compilación. Puede hacer que las consultas sean más legibles utilizando indentaciones o tomando cada paso por separado, en lugar de escribir una cláusula WHERE completa en una sola línea.

Para que su consulta no distinga entre mayúsculas y minúsculas, convierta su palabra clave y el campo comparado en 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() + "%"
        )
    )
);

Answer 6

Solución desesperada para OpenJPA 2.3.0 y 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 es una solución frágil y fea para hacer una operación LIKE insensible a mayúsculas y minúsculas con OpenJPA y la base de datos Postgresql. Reemplaza el operador LIKE al operador ILIKE en el SQL generado.

Es una lástima que OpenJPA DBDictionary no permita cambiar los nombres de los operadores.