spring JPA 标准构建器中的 ignorecase

ignorecase in criteria builder in JPA

提问by ronan

How can we do a ignorecase in the criteria builder? If I have

我们如何在标准构建器中忽略大小写？如果我有

private final CriteriaBuilder cb

then I can only use cb.ascor cb.desc, but not ignoring case.

那么我只能使用cb.ascor cb.desc，但不能忽略大小写。

回答by Glen Best

How can we do a ignorecase in the criteria builder

我们如何在标准构建器中做一个 ignorecase

1. Force Ignorecase in JPA Program - Does the Job, Answers the Q Directly

1. 在 JPA 程序中强制忽略大小写 - 完成工作，直接回答 Q

• For a one-arg operation (i.e. ORDER BY), convert argument to lowercase (or uppercase).
• For a two-arg operation (e.g. = or LIKE or ORDER BY), convert both args to LC (or UC).

• 对于单参数操作（即 ORDER BY），将参数转换为小写（或大写）。
• 对于双参数操作（例如 = 或 LIKE 或 ORDER BY），将两个参数都转换为 LC（或 UC）。

JPA ORDER BY Two Columns, Ignoring Case:

JPA ORDER BY 两列，忽略大小写：

 Order lcSurnameOrder = criteriaBuilder.order(
     criteriaBuilder.lower(Person_.surname));
 Order lcFirstnameOrder = criteriaBuilder.order(
     criteriaBuilder.lower(Person_.firstname));    
 criteriaQuery.orderBy(lcSurnameOrder, lcFirstnameOrder);

JPA LIKE, Ignoring Case:

JPA LIKE，忽略大小写：

 Predicate lcSurnameLikeSearchPattern = criteriaBuilder.like(
     criteriaBuilder.lower(Person_.surname), 
     searchPattern.toLowerCase());
 criteriaQuery.where(lcSurnameLikeSearchPattern);

Assumes Person_ canonical metamodel class was generated from Person entity, to give strong-typed use of JPA criteria API.

假设 Person_ 规范元模型类是从 Person 实体生成的，以提供 JPA 标准 API 的强类型使用。

TIP: For best performance & control, consider converting string columns to LOWER case or INITCAP case just once - when you INSERT/UPDATE into the database. Do the same conversion for user-entered search patterns.

提示：为了获得最佳性能和控制，请考虑将字符串列转换为 LOWER case 或 INITCAP case 一次 - 当您 INSERT/UPDATE 到数据库中时。对用户输入的搜索模式进行相同的转换。

2. ALTERNATIVE: Apply Collation in the Database - Best Practice, Simpler, More Performant

2. 替代方案：在数据库中应用排序规则 - 最佳实践、更简单、更高性能

• The SQL-99 standard has a built-in modifer to compare characters in Strings according to rules:

  COLLATE <collation name>
  

  Can use when comparing, sorting and grouping on strings. A common example that ignores case:

  COLLATE SQL_Latin1_General_CP1_CI_AS
  

  Or

  COLLATE latin1_general_cs
  

  You can even create your own custom collation:

  CREATE COLLATION <collation name> FOR <character set specification>
    FROM <existing collation name> [ <pad characteristic> ]
  

• Collation is applied in the DB via one of the following alternatives (from localised to global effect):

  • WHERE Clause (=, LIKE, HAVING, >, >=, etc)

    WHERE <expression> = <expression> [COLLATE <collation name>]
    
    WHERE <expression> LIKE <expression> [COLLATE <collation name>]
    

  • SELECT DISTINCT Clause

    SELECT DISTINCT <expression> [COLLATE <collation name>], ...
    

  • ORDER BY Clause

    ORDER BY <expression> [COLLATE <collation name>]
    

  • GROUP BY Clause

    GROUP BY <expression> [COLLATE <collation name>]
    

  • Column Definition

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

  • Domain Definition

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

  • Character Set Definition

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

• The first 4 cases can't be used with JPA, because these SQL commands are generated by JPA, and JPA standard does not support collation.

• The last 3 cases can be used with JPA.
• So: create TABLES with COLUMNS that have collation "turned on" and subsequently ORDER BY, =, LIKE, etc will ignore case automatically. Then no work is required in JPA - no need for any conversion or requesting to ignore case.

• SQL-99 标准有一个内置的修饰符，可以根据规则比较字符串中的字符：

  COLLATE <collation name>
  

  可以在对字符串进行比较、排序和分组时使用。忽略大小写的常见示例：

  COLLATE SQL_Latin1_General_CP1_CI_AS
  

  或者

  COLLATE latin1_general_cs
  

  您甚至可以创建自己的自定义排序规则：

  CREATE COLLATION <collation name> FOR <character set specification>
    FROM <existing collation name> [ <pad characteristic> ]
  

• 通过以下替代方法之一（从局部效果到全局效果）在 DB 中应用整理：

  • WHERE 子句（=、LIKE、HAVING、>、>= 等）

    WHERE <expression> = <expression> [COLLATE <collation name>]
    
    WHERE <expression> LIKE <expression> [COLLATE <collation name>]
    

  • SELECT DISTINCT 子句

    SELECT DISTINCT <expression> [COLLATE <collation name>], ...
    

  • 按条款排序

    ORDER BY <expression> [COLLATE <collation name>]
    

  • 按条款分组

    GROUP BY <expression> [COLLATE <collation name>]
    

  • 列定义

    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> ]
    

• 前 4 种情况不能与 JPA 一起使用，因为这些 SQL 命令是由 JPA 生成的，而 JPA 标准不支持整理。

• 最后 3 个案例可以与 JPA 一起使用。
• 所以：创建带有“打开”排序规则的 COLUMNS 的 TABLES，随后 ORDER BY、=、LIKE 等将自动忽略大小写。那么在 JPA 中不需要任何工作 - 不需要任何转换或请求忽略大小写。

3. ALTERNATIVE (PROPRIETARY) Oracle also provides NLS settings to ignore case across entire DB instance (can be set in config files):

3. ALTERNATIVE (PROPRIETARY) Oracle 还提供 NLS 设置以忽略整个数据库实例的大小写（可以在配置文件中设置）：

ALTER SESSION SET NLS_COMP='BINARY';    -- Case Sensitive
ALTER SESSION SET NLS_COMP='ANSI';      -- Ignore for LIKE but not =,<,etc
ALTER SESSION SET NLS_COMP='LINGUISTIC';-- Ignore for LIKE,=,<,etc (post 10gR2)

ALTER SESSION SET NLS_SORT='BINARY' ;   -- Case Sensitive
ALTER SESSION SET NLS_SORT='BINARY_CI'; -- Ignore
ALTER SESSION SET NLS_SORT='XSPANISH';  -- Ignore according to language rules
ALTER SESSION SET NLS_SORT='LATIN1_GENERAL_CS';  

Plus functions to ignore case as one-off

加上一次性忽略大小写的功能

ORDER BY NLSSORT(supplier_name,'NLS_SORT=BINARY_CI') ;

You can call this via

你可以通过调用这个

criteriaBuilder.function("nlssort", String.class, dept_.suppler_name, "NLS_SORT=BINARY_CI");

and then call criteriaQuery.orderyByor select, etc

然后调用criteriaQuery.orderyByorselect等