JPA2: нечувствительность к регистру

Я использую ограничения Hibernate в JPA 1.0 (драйвер Hibernate). Определен Restrictions.ilike("column","keyword", MatchMode.ANYWHERE), который проверяет, соответствует ли ключевое слово, совпадающее с столбцом, и нечувствительно к регистру.

Теперь я использую JPA 2.0 с EclipseLink в качестве драйвера, поэтому мне нужно использовать встроенную JPA 2.0 "Ограничения". Я нашел CriteriaBuilder и метод like, я также выяснил, как сделать его подходящим в любом месте (хотя он является серьезным и ручным), но все же я не понял, как сделать это без учета регистра.

Есть мое нынешнее сильное решение:

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();

Вопросы:

Есть ли какая-либо функция, например, в драйвере Hibernate?

Я правильно использую критерии JPA 2.0? Это неудобное и неудобное решение по сравнению с ограничениями в спящем режиме.

Или может кто-нибудь помочь мне, как изменить мое решение, чтобы оно было нечувствительным к регистру, пожалуйста?

Большое спасибо.

Ответ 1

Вначале это может показаться немного неудобным, но оно безопасно для типов. Построение запросов из строк не так, поэтому вы замечаете ошибки во время выполнения, а не во время компиляции. Вы можете сделать запросы более читабельными, используя отступы или делая каждый шаг отдельно, вместо написания всего предложения WHERE в одной строке.

Чтобы сделать ваш запрос нечувствительным к регистру, преобразуйте как ваше ключевое слово, так и сравниваемое поле в нижний регистр:

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() + "%"
        )
    )
);

Ответ 2

Проще и эффективнее обеспечить неприемлемость регистра в базе данных, чем JPA.

В соответствии со стандартами SQL 2003, 2006, 2008 это можно сделать, добавив COLLATE SQL_Latin1_General_CP1_CI_AS OR COLLATE latin1_general_cs к следующему:
- Определение столбца
```
CREATE TABLE <table name> (
  <column name> <type name> [DEFAULT...] 
                            [NOT NULL|UNIQUE|PRIMARY KEY|REFERENCES...]
                            [COLLATE <collation name>], 
  ...
)
```
- Определение домена
```
CREATE DOMAIN <domain name> [ AS ] <data type>
  [ DEFAULT ... ] [ CHECK ... ] [ COLLATE <collation name> ]
```
- Определение набора символов
```
CREATE CHARACTER SET <character set name>
[ AS ] GET <character set name> [ COLLATE <collation name> ]
```
Для полного описания выше обратитесь: 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
В Oracle можно установить параметры сеанса/конфигурации 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
```
Или, в init.ora (или конкретное имя для файла параметров инициализации):
```
NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI
```
Двоичные сортировки могут быть нечувствительными к регистру или без акцента. Когда вы указываете BINARY_CI как значение для NLS_SORT, он обозначает сортировку, чувствительную к акценту и не учитывающую регистр. BINARY_AI обозначает несимметричную и нечувствительную к регистру двоичную сортировку. Вы можете использовать двоичную сортировку, если двоичный порядок сортировки набора символов подходит для набора символов, который вы используете. Используйте параметр сеанса NLS_SORT для указания нечувствительности к регистру или несимметричной сортировке:
```
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. 
```
Например, вы можете установить NLS_SORT на следующие типы значений:
```
FRENCH_M_AI
XGERMAN_CI
```
Настройка NLS_SORT на что-либо, кроме BINARY [с опциональным _CI или _AI], вызывает сортировку для использования полного сканирования таблицы, независимо от пути, выбранного оптимизатором. BINARY - это исключение, поскольку индексы строятся в соответствии с двоичным порядком ключей. Таким образом, оптимизатор может использовать индекс для удовлетворения предложения ORDER BY, когда NLS_SORT установлен в BINARY. Если NLS_SORT настроен на любую лингвистическую сортировку, оптимизатор должен включать полное сканирование таблицы и полную сортировку в плане выполнения.

Или, если для NLS_COMP установлено значение LINGUISTIC, как указано выше, параметры сортировки могут быть применены локально к индексированным столбцам, а не глобально через базу данных:
```
CREATE INDEX emp_ci_index ON emp (NLSSORT(emp_name, 'NLS_SORT=BINARY_CI'));
```
Ссылка: ORA 11g Лингвистическая сортировка и поиск строк ORA 11g Настройка среды поддержки глобализации

Ответ 3

Эта работа для меня:

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();

Ответ 4

Отчаянное обходное решение для OpenJPA 2.3.0 и 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);
  }

}

Это хрупкое и уродливое обходное решение для работы без учета регистра LIKE с базами данных OpenJPA и Postgresql. Он заменяет оператор LIKE оператором ILIKE в сгенерированном SQL.

Плохо, что OpenJPA DBDictionary не позволяет изменять имена операторов.

Ответ 5

Как я прокомментировал (в настоящее время) принятый ответ, существует ошибка, связанная с использованием функции SGBD lower() с одной стороны, а с другой стороны, java String.toLowerCase(), поскольку оба метода не гарантируют предоставление того же выхода для того же строка ввода.

Наконец-то я нашел гораздо более безопасное решение, которое позволяет SGBD делать все понижение, используя буквальное выражение:

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

Итак, полное решение будет выглядеть так:

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 + "%")
        )
    )
);

Ответ 6

Пожалуйста, используйте

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

для соответствия в любом месте.