I have the following query:
@Query("SELECT d FROM CarportLocationEntityView d WHERE d.externalId LIKE %:filterCriteria% OR lower(d.carportName) LIKE lower(concat('%',:filterCriteria,'%')) ")
List<CarportLocationEntityView> filterCarportList(@Param("filterCriteria") String filterCriteria);
This query searches two columns in the database:
- externalId
- carportName
Both columns are of type string, where in externalId always a number is specified as string. if I apply the query and enter a part of carportName then I get the correct results. However, if I filter for an externalId and enter only a part of the externalId in the parameter then I get no data.
Ex: localhost:8080/filter?filterCriteria=100 Here I get no data back.
But if I filter for the whole externalId localhost:8080/filter?filterCriteria=1000000 then I get the matching data set.
Why do the % operators in the query not work for strings containing numbers?
CodePudding user response:
@Query("SELECT d FROM CarportLocationEntityView d WHERE d.externalId LIKE %:#{#filterCriteria}% OR lower(d.carportName) LIKE %:#{#filterCriteria.toLowerCase()}% ")
List<CarportLocationEntityView> filterCarportList(@Param("filterCriteria") String filterCriteria);
Ref:
- Using SpEL Expressions - Spring Data JPA Reference Documentation
See Also: