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)
I assume above is not guaranteed to work with all JPA implementations, because argument to ORDER BY is not one of the following:
- 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.
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
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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…