Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
286 views
in Technique[技术] by (71.8m points)

jpa: perform case insensitive order by

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...