java JPA2：不区分大小写，就像在任何地方匹配一样

3 月，1 周 Questions & Answers 85323

我一直在JPA1.0（Hibernate驱动程序）中使用Hibernate限制。定义了Restrictions.ilike("column","keyword", MatchMode.ANYWHERE)，用于测试关键字是否与列匹配，并且不区分大小写

现在，我使用带有EclipseLink的JPA2.0作为驱动程序，所以我必须使用JPA2.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驱动程序的功能

我是否正确使用JPA2.0标准？与Hibernate限制相比，这是一个尴尬和不舒服的解决方案

或者有人能帮我把我的解决方案改成不区分大小写吗

非常感谢

Tags:

共 (6) 个答案

# 1 楼答案
与JPA相比，在数据库中实施不区分大小写更容易、更高效
1. 根据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#column%20definition http://dev.mysql.com/doc/refman/5.1/en/charset-table.html http://msdn.microsoft.com/en-us/library/ms184391.aspx
2. 在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 session参数指定不区分大小写或不区分重音的排序：
```
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_排序设置为以下类型的值：
```
FRENCH_M_AI
XGERMAN_CI
```
  将NLS_SORT设置为除二进制之外的任何值[使用可选的_CI或_AI]都会导致排序使用完整表扫描，而不管优化器选择的路径如何。二进制是例外，因为索引是根据密钥的二进制顺序构建的。因此，当NLS_SORT设置为二进制时，优化器可以使用索引来满足ORDER BY子句。如果NLS_SORT设置为任何语言排序，则优化器必须在执行计划中包括完整表扫描和完整排序
  
  或者，如果NLS_COMP设置为语言，如上所述，则排序设置可以在本地应用于索引列，而不是在数据库中全局应用：
```
CREATE INDEX emp_ci_index ON emp (NLSSORT(emp_name, 'NLS_SORT=BINARY_CI'));
```
  参考：ORA 11g Linguistic Sorting and String Searching ORA 11g Setting Up a Globalization Support Environment

# 2 楼答案

这是我的工作：

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

# 3 楼答案

正如我在（当前）接受的答案中所评论的，一方面使用DBMS的lower()函数，另一方面使用java的String.toLowerCase()函数存在一个陷阱，因为这两种方法都不能保证为相同的输入字符串提供相同的输出

我最终找到了一个更安全（但不是防弹）的解决方案，即让DBMS使用文字表达式执行所有降低操作：

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()能够正确地降低重音字符。（但这超出了我微薄的知识范围）

*带“C”语言环境的PostgreSQL 9.5.1的Db值

# 4 楼答案

一开始可能有点尴尬，但它是类型安全的。从字符串生成查询不是这样，所以您会在运行时而不是编译时注意到错误。通过使用缩进或单独执行每个步骤，而不是在一行中编写整个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() + "%"
        )
    )
);

# 5 楼答案

如果您使用的是像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();
            }

        }

    }

在我们的情况下，与使用lower函数的版本相比，上述优化使性能提高了40倍，因为Postgres可以利用相应列上的索引。在我们的情况下，查询执行时间可以从4.5秒减少到100毫秒

lower阻止了索引的有效使用，因此速度要慢得多

# 6 楼答案

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

}

对于使用OpenJPA和Postgresql数据库执行不区分大小写的操作来说，这是一个脆弱而丑陋的解决方案。它将生成的SQL中的LIKE运算符替换为ILIKE运算符

OpenJPA DBDictionary不允许更改运算符名称，这太糟糕了

Python中文网

有 Java 编程相关的问题?