Home > OS >  Why do the % operators in the query not work for strings containing numbers? (JPA)
Why do the % operators in the query not work for strings containing numbers? (JPA)

Time:11-11

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:

  1. externalId
  2. 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:

See Also:

  • Related