Jpa: execute case insensitive

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: apple bat atom oil

The following returns: apple bat atom oil

I need the following: apple atom bat butter

Answer 1

For example, with Hibernate, you can use the LOWER function for p.name in ORDER BY:

 select p from Plan as p where p.location = :location order by LOWER(p.name) 

I assume that the above is not guaranteed to work with all JPA implementations, because the ORDER BY argument is not one of the following:

• A state_field_path_ expression that evaluates an ordered state field of an object or a nested type of an abstract class scheme specified in a SELECT clause by one of the following:
  • general_identification_variable
  • single_valued_object_path_expression
• A state_field_path_ expression that evaluates to a single state field of the same object or a nested abstract schema type as the state_field_path_ expression in a SELECT expression
• Result_variable, which refers to an ordered element in a SELECT clause for which the result_variable parameter is specified. This may be the result of an aggregate_expression, a scalar_expression, or state_field_path_expression expression in a SELECT clause. For example, the four requests below are legal.

If it does not work with your JPA implementation, you should use the following query:

 select p, LOWER(p.name) AS name_order from Plan as p where p.location = :location order by name_order 

The disadvantage is that the query result is a list of arrays of objects, the first element in each list is an instance of the Plan object and the second element to be discarded.

Answer 2

After receiving 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(); 

This is like asking to convert all characters to uppercase before sorting

Answer 3

Case-insensitive ordering can be achieved using the OrderBy annotation by ordering the ordering column using LOWER or UPPER

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

Below SQL generated by sleeping

 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)