jpa:执行不区分大小写的命令

本文介绍了jpa:执行不区分大小写的命令的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下查询:

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:

一个state_field_path_expression，其计算结果为实体或实体的可排序状态字段 在SELECT子句中由以下其中一项指定的可嵌入类抽象模式类型: •general_identification_variable •single_valued_object_path_expression

一个state_field_path_expression，其结果为相同实体或相同实体的相同state字段 在SELECT子句中将可嵌入的抽象模式类型作为state_field_path_expression嵌入

一个result_variable，它引用SELECT子句中的可排序项目，对其相同 已指定result_variable.这可能是aggregate_expression， scalar_expression或SELECT子句中的state_field_path_expression. 例如，以下四个查询是合法的.

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

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

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.