CODEWITHSUNDEEP
springoracle-databasespring-bootjpaspring-data-jpa
Nafaz M N M
Nafaz M N M

Reputation: 1688

Spring JPA, SpEL parsing a valid expression, there is still more data in expression, 'comma(,)'

I have to retrieve some specific data from oracle 12c using spring JPA which are separated by comma (,).

Ex - Branch

  ID                    Location
---------              ----------
  101                    NL,MX,USA
  102                    UK,IND,BLG
  103                    UK,NL,AUS



@Query(value = "select * from branch m where lower(m.location) like %:loc% offset :offset rows fetch next :limit rows only",nativeQuery = true)
        List<Location> getBranchListByLocation(@Param("loc") String loc,@Param("offset") int offset,@Param("limit") limit)

But when i pass loc value as "NL" then the following error has occurred.

   spelparseexception: el1041e: after parsing a valid expression, there is still more data in the expression: 'comma(,)'

Is there an alternative way to solve the issue?

Upvotes: 0

Views: 383

Answers (1)

MT0
MT0

Reputation: 167832

The % characters need to be contained in string literals and concatenated with the :loc bind variable:

select *
from   branch m
where  lower(m.location) like '%' || :loc || '%'
offset :offset rows
fetch next :limit rows only

Alternatively, you can prepend and append % to the loc variable in Java and then just use :loc without the wrapping %s in the SQL query.

(Note: you are using lower so you want to pass in nl and not NL.)

However, you will find if you pass the :loc value in then it will match in as well as ind and ink. If you want to match the full delimited item then you want to include the surrounding commas in the match:

select *
from   branch m
where  ',' || lower(m.location) || ',' like '%,' || :loc || ',%'
offset :offset rows
fetch next :limit rows only

Upvotes: 1

Related Questions