JPA中的条件构建器中的ignorecase [英] ignorecase in criteria builder in JPA

问题描述

我们如何在条件构建器中进行忽略大小写?如果有

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

private final CriteriaBuilder cb

然后我只能使用cb.asc或cb.desc，但不能忽略大小写.

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

推荐答案

我们如何在条件构建器中执行忽略大小写

1.在JPA程序中强制忽略大小写-是否执行工作，直接回答问题

• 对于单参数操作(即ORDER BY)，请将参数转换为小写(或大写).
• 对于两个参数的操作(例如=或LIKE或ORDER BY)，请将两个参数都转换为LC(或UC).

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

JPA ORDER BY Two Columns, Ignoring Case:

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

喜欢JPA，忽略大小写:

JPA LIKE, Ignoring Case:

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

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

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

提示:为了获得最佳性能，控件，考虑一次将字符串列转换为LOWER或INITCAP的情况-当您将INSERT/UPDATE插入数据库时​​.对用户输入的搜索模式进行相同的转换.

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.

2.替代方案:在数据库中应用归类-最佳实践，更简单，更高效

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

• 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

或

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子句(=，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> ]

JPA不能使用前4种情况，因为这些SQL命令是由JPA生成的，并且JPA标准不支持排序规则.

3.替代(专有)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");

，然后调用criteriaQuery.orderyBy或select等

这篇关于JPA中的条件构建器中的ignorecase的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！