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? Это неудобное и неудобное решение по сравнению с Hibernate Restrictions.
Или кто-нибудь может мне помочь, как изменить мое решение, чтобы не учитывать регистр, пожалуйста?
Большое спасибо.
9 ответов
Поначалу это может показаться немного неловким, но это безопасно для типов. Построение запросов из строк не так, поэтому вы замечаете ошибки во время выполнения, а не во время компиляции. Вы можете сделать запросы более удобочитаемыми, используя отступы или делая каждый шаг отдельно, вместо того, чтобы писать целое предложение 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() + "%"
)
)
);
Как я прокомментировал в (в настоящее время) принятом ответе, есть ловушка, использующая с одной стороны СУБД lower() функция и с другой стороны Ява String.toLowerCase() поскольку оба метода не гарантированы, чтобы обеспечить тот же самый вывод для той же самой входной строки.
Я наконец нашел гораздо более безопасное (но не пуленепробиваемое) решение, которое позволяет СУБД выполнять все понижения, используя буквальное выражение:
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 + "%")
)
)
);
Редактировать:
Когда @cavpollo попросил меня привести пример, мне пришлось дважды подумать о своем решении и понять, что оно не намного безопаснее, чем принятый ответ:
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
Тем не менее, я предпочитаю свое решение, так как оно не сравнивает результаты двух разных функций, которые должны работать одинаково. Я применяю одну и ту же функцию ко всем массивам символов, чтобы сравнение результатов стало более "стабильным".
Пуленепробиваемое решение будет включать локаль, чтобы SQL lower() стать способным правильно опускать акцентированные символы. (Но это выходит за рамки моих скромных знаний)
* Значение в БД в PostgreSQL 9.5.1 с языком 'C'
Если вы используете такую базу данных, как Postgres, которая поддерживает ilike что обеспечивает гораздо лучшую производительность, чем при использовании lower() ни одно из предложенных решений не решает проблему должным образом.
Решением может быть пользовательская функция.
Вы пишете следующий HQL-запрос:
SELECT * FROM User WHERE (function('caseInSensitiveMatching', name, '%test%')) = true
Где caseInSensitiveMatchingэто имя нашей пользовательской функции. Вname это путь к собственности, которую вы хотите сравнить, и %test% это шаблон, с которым вы хотите сопоставить его.
Цель состоит в том, чтобы преобразовать запрос HQL в следующий запрос SQL:
SELECT * FROM User WHERE (name ilike '%test%') = true
Для этого мы должны реализовать наш собственный диалект с зарегистрированной пользовательской функцией:
public class CustomPostgreSQL9Dialect extends PostgreSQL9Dialect {
/**
* Default constructor.
*/
public CustomPostgreSQL9Dialect() {
super();
registerFunction("caseInSensitiveMatching", new CaseInSensitiveMatchingSqlFunction());
}
private class CaseInSensitiveMatchingSqlFunction implements SQLFunction {
@Override
public boolean hasArguments() {
return true;
}
@Override
public boolean hasParenthesesIfNoArguments() {
return true;
}
@Override
public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
return StandardBasicTypes.BOOLEAN;
}
@Override
public String render(Type firstArgumentType, @SuppressWarnings("rawtypes") List arguments,
SessionFactoryImplementor factory) throws QueryException {
if (arguments.size() != 2) {
throw new IllegalStateException(
"The 'caseInSensitiveMatching' function requires exactly two arguments.");
}
StringBuilder buffer = new StringBuilder();
buffer.append("(").append(arguments.get(0)).append(" ilike ").append(arguments.get(1)).append(")");
return buffer.toString();
}
}
}
Вышеупомянутая оптимизация в нашей ситуации привела к увеличению производительности в 40 раз по сравнению с версией с lowerфункция, поскольку Postgres может использовать индекс в соответствующем столбце. В нашей ситуации время выполнения запроса можно было сократить с 4,5 секунд до 100 мс.
В lower препятствует эффективному использованию индекса и поэтому работает намного медленнее.
Эта работа для меня:
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();
Проще и эффективнее обеспечить соблюдение регистра в базе данных, чем JPA.
В соответствии со стандартами SQL 2003, 2006, 2008 это можно сделать, добавив
COLLATE SQL_Latin1_General_CP1_CI_ASИЛИ ЖЕ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 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 Настройка среды поддержки глобализации
Отчаянный обходной путь для 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 не позволяет изменять имена операторов.
чтобы использовать подход Томаса Ханзикера с построителем критериев спящего режима, вы можете предоставить конкретную реализацию предиката, подобную следующей
public class ILikePredicate extends AbstractSimplePredicate implements Serializable {
private final Expression<String> matchExpression;
private final Expression<String> pattern;
public ILikePredicate(
CriteriaBuilderImpl criteriaBuilder,
Expression<String> matchExpression,
Expression<String> pattern) {
super(criteriaBuilder);
this.matchExpression = matchExpression;
this.pattern = pattern;
}
public ILikePredicate(
CriteriaBuilderImpl criteriaBuilder,
Expression<String> matchExpression,
String pattern) {
this(criteriaBuilder, matchExpression, new LiteralExpression<>(criteriaBuilder, pattern));
}
public Expression<String> getMatchExpression() {
return matchExpression;
}
public Expression<String> getPattern() {
return pattern;
}
@Override
public void registerParameters(ParameterRegistry registry) {
Helper.possibleParameter(getMatchExpression(), registry);
Helper.possibleParameter(getPattern(), registry);
}
@Override
public String render(boolean isNegated, RenderingContext renderingContext) {
String match = ((Renderable) getMatchExpression()).render(renderingContext);
String pattern = ((Renderable) getPattern()).render(renderingContext);
return String.format("function('caseInSensitiveMatching', %s, %s) = %s", match, pattern, !isNegated);
}
}
В качестве ответа user547122 , короче говоря, для каждого желаемого поля в вашем корне добавьте следующий предикат в список предикатов .
criteriaBuilder.like(criteriaBuilder.lower(root.get(<desired field on your root>)), "%" + text.toLowerCase(Locale.ROOT) + "%")
Затем получите TypedQuery с желаемым ИЛИ-И, как показано ниже.
entityManager.createQuery(criteriaQuery.where(criteriaBuilder.and(predicateList.toArray(new Predicate[]{}))));
Пожалуйста, подумайте об использовании
CriteriaBuilder.like(Expression<String> x, Expression<String> pattern, char escapeChar);
для сопоставления в любом месте.