CODEWITHSUNDEEP
spring-datajpql
Tracy Xia
Tracy Xia

Reputation: 390

Spring Data JPQL query: lower case for List param

I use Spring Data REST in my project and I have a @Query tag, as such: 

@Query("from Customer c where lower(c.customerId) = lower(:customerId) and lower(c.department.businessUnit.name) = lower(:businessUnit)") 
List<Customer> findByCustomerIdAndBusinessUnit(@Param('customerId') String customerId, @Param('businessUnit') String businessUnit)

This works perfectly fine. But, I am not sure how to convert to lowercase when a List is passed in, such as: 

@Query("SELECT c FROM Customer c WHERE LOWER(c.customerId) IN LOWER(:customerIds)")
Page<Customer> findByCustomerIdIn(@Param("customerIds") List<String> customerIds, Pageable pageable)

That gives me the following error: 

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 94 [SELECT c FROM com.myapp.Customer c WHERE LOWER(c.customerId) IN LOWER(:customerIds)]

I understand this happening because Spring Data REST cannot cast a whole list of Strings to lower case (that is not possible in straight up TSQL / PLSQL either!). So, are my options limited to implementing my own repository and build a controller on top of it...? Any better ideas? :)

Upvotes: 2

Views: 6562

Answers (1)

Narendra N
Narendra N

Reputation: 1270

I believe, you have to transform the list.

Even if you plan to use Spring JPA findByQuery like 

List<User> findByCustomerIdInIgnoreCase(List<String> customerIds);

You have to write some code to transform the list into a list of lowercase strings.

If you are looking for converting the list in place, it might not be possible as Strings in Java are immutable

However, if the intention is to do in a single line, with as much less code as possible.

You can do it in Java 8 as below.

List<String> customerIdsLC = customerIds.stream().map(String::toLowerCase).collect(Collectors.toList());

In case, you have a different transformation to do - just provide the right info to map() in the form, Class::method

For e.g.

map(String::toUpperCase) - to convert to Uppercase

map(Long::valueOf) - to convert to Long.

Upvotes: 2

Related Questions