jpa: perform case insensitive order by

Question

I have the following query:

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

Answer 1

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:

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.

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.