CODEWITHSUNDEEP
jpa
U-L
U-L

Reputation: 2681

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

Upvotes: 8

Views: 14601

Answers (3)

Saravana
Saravana

Reputation: 12817

Case insensitive ordering can be achieved using OrderBy annotation by wrapping ordering column with LOWER or UPPER

@OrderBy("UPPER(firstName) ASC")
private List<User> members = new ArrayList<User>();

Below SQL generated by hibernate

SELECT user0_.id AS id1_0_0_,
       user0_.firstname AS firstname2_0_1_,
       user0_.lastname AS lastname3_0_1_
FROM user user0_
WHERE user0_.user_id=?
ORDER BY upper(user0_.firstname)

Upvotes: 1

Grigoreas P.
Grigoreas P.

Reputation: 2472

Following gets results sorted by 'orderField' case insensitive 

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> cq = criteriaBuilder.createQuery(MyEntity.class);
Root<MyEntity> root = cq.from(MyEntity.class);
cq.select(root).where(...).orderBy(cb.desc(cb.upper(duplicate.get(MyEntity_.orderField))));
TypedQuery<MyEntity> query = entityManager.createQuery(cq);
query.getResultList();

It's like telling the query to convert all characters to uppercase prior sorting

Upvotes: 5

Mikko Maunu
Mikko Maunu

Reputation: 42084

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.

Upvotes: 15

Related Questions