CODEWITHSUNDEEP
springjpaspring-datahqlspring-data-jpa
Molay
Molay

Reputation: 1404

IllegalArgumentException: Parameter with that position [1] did not exist -- LOWER & CONTAINING usage

I am new to Spring Data JPA. I have this Query. LOWER function and CONTAINING(case-insensitive) are mandatory.

It's working fine, if i use : 

findByCityContainsAndAreaContainsAndType(String city,String area, String type);

The reason for using @Query is i wanted to use joins. But ('%?1%') seems to be having an issue. Not sure how to implement it.

@Query(value=" select * from Agreement where LOWER(city) like LOWER('%?1%') and LOWER(AREA) like LOWER('%?2%') and type=?3", nativeQuery = true)
public List<Agreement> getAgreementDetailsByCityAreaAndType(String city,String area, String type);

Getting error:

Caused by: java.lang.IllegalArgumentException: Parameter with that position [1] did not exist

Also tried JPA:

@Query(value=" select sa from Agreement sa where LOWER(sa.city) like LOWER('%?1%') and LOWER(sa.area) like LOWER('%?2%') and sa.type=?3")
    public List<Agreement> getAgreementDetailsByCityAreaAndType(String city,String area, String type);

Same issue:

Caused by: java.lang.IllegalArgumentException: Parameter with that position [1] did not exist

Also tried to use:

LOWER(city) like '%' + LOWER(?1) + '%'

from : https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example.execution

But it's throwing query syntax errors at run time.

Any suggestions ?

Thanks

Upvotes: 1

Views: 2443

Answers (1)

Maciej Kowalski
Maciej Kowalski

Reputation: 26502

Ok, if you are using JPQL instead of native then i would suggest using the concat function:

@Query(value=" select sa from Agreement sa 
        where LOWER(sa.city) like LOWER(CONCAT('%', ?1, '%'))
          and LOWER(sa.area) like LOWER(CONCAT('%', ?2, '%')) and sa.type=?3")
    public List<Agreement> getAgreementDetailsByCityAreaAndType(String city,String area, String type);

I remember having some issues with the numbered parameters and had to use the named parameters. It was on an older version though so give it a try like above.

Upvotes: 1

Related Questions