jpa:执行不区分大小写的命令 [英] jpa: perform case insensitive order by

问题描述

我有以下查询:

select p from Plan as p where p.location = :location order by p.name

问题在于，如果有以下三个计划: 苹果 蝙蝠 原子 黄油

The problem is that if there are three plans as follows: Apple bat atom Butter

返回以下内容: 苹果 黄油 原子 蝙蝠

The following is returned: Apple Butter atom bat

我需要以下条件: 苹果 原子 蝙蝠 黄油

I require the following: Apple atom bat Butter

推荐答案

例如，在Hibernate中，您可以使用LOWER函数在ORDER BY中设置名称:

For example with Hibernate you can use LOWER function to p.name in ORDER BY:

select p from Plan as p where p.location = :location order by LOWER(p.name)

我认为上述内容不能保证能与所有JPA实现一起使用，因为ORDER BY的参数不是以下之一:

I assume above is not guaranteed to work with all JPA implementations, because argument to ORDER BY is not one of the following:

1. 一个state_field_path_expression，其计算结果为实体或实体的可排序状态字段 在SELECT子句中由以下其中一项指定的可嵌入类抽象模式类型:
   •general_identification_variable
   •single_valued_object_path_expression
2. 一个state_field_path_expression，其结果为相同实体或相同实体的相同state字段 在SELECT子句中将可嵌入的抽象模式类型作为state_field_path_expression嵌入
3. 一个result_variable，它引用SELECT子句中的可排序项目，对其相同 已指定result_variable.这可能是aggregate_expression， scalar_expression或SELECT子句中的state_field_path_expression. 例如，以下四个查询是合法的.

1. A state_field_path_expression that evaluates to an orderable state field of an entity or embeddable class abstract schema type designated in the SELECT clause by one of the following:
   • a general_identification_variable
   • a single_valued_object_path_expression
2. A state_field_path_expression that evaluates to the same state field of the same entity or embeddable abstract schema type as a state_field_path_expression in the SELECT clause
3. A result_variable that refers to an orderable item in the SELECT clause for which the same result_variable has been specified. This may be the result of an aggregate_expression, a scalar_expression, or a state_field_path_expression in the SELECT clause. For example, the four queries below are legal.

如果它不适用于您使用的JPA实现，则必须使用以下查询:

If it does not work with JPA implementation you use, you have to use following query:

select p, LOWER(p.name) AS name_order 
from Plan as p 
where p.location = :location order by name_order 

缺点是查询的结果是对象数组的列表，每个列表中的第一个元素是Plan实体的实例，第二个元素将被丢弃.

Drawback is that result of the query is list of object arrays, first element in each list being instance of Plan entity and second element to be discarded.

这篇关于jpa:执行不区分大小写的命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！