Spring Data JPA @Query with spel in a IN query

Question

I work on Spring Data JPA and I have a repository interface which implements JpaRepository.

I have already written this query which works perfectly:

@Query ("FROM Person p " +
        "LEFT JOIN p.relatedContractRoleAttributions rcras " +
        "WHERE rcras.contract.id = :#{#contract.id} " +
        "AND rcras.relatedContractRole.code = :#{#code}")
Person findByContractAndRelatedContractRole(@Param ("contract") Contract contract, @Param ("code") String code);

Now I want to write another query which can find in more than one code so I wrote this query:

@Query ("FROM Person p " +
        "LEFT JOIN p.relatedContractRoleAttributions rcras " +
        "WHERE rcras.contract.id = :#{#contract.id} " +
        "AND rcras.relatedContractRole.code IN (:#{#codes})")
List<Person> findByContractAndRelatedContractRoles(@Param ("contract") Contract contract, @Param ("codes") String... codes);

But when I start my application I have this error:

Caused by: org.hibernate.QueryException: unexpected char: '#' [FROM com.krgcorporate.core.domain.access.Person p LEFT JOIN p.relatedContractRoleAttributions rcras WHERE rcras.contract.id = :#{#contract.id} AND rcras.relatedContractRole.code IN (:__$synthetic$__2)]

Do you have any idea why?

Thanks for your help.

Sergii · Accepted Answer · 2015-06-14T22:26:30.110

I believe that there is an issue in org.springframework.data.jpa.repository.query.StringQuery. spring-data-jpa-1.7.2.RELEASE (lines 250..259)

case IN:
    if (parameterIndex != null) {
        checkAndRegister(new InParameterBinding(parameterIndex, expression), bindings);
    } else {
        checkAndRegister(new InParameterBinding(parameterName, expression), bindings);
    }
    result = query;
    break;

So when StringQuery binds 'in' parameter with SPEL it overrides the result query string with the string in your @Query annotation. And then it replaces 'in' SPEL with the new binding.

If you change

 "WHERE rcras.contract.id = :#{#contract.id} " +
        "AND rcras.relatedContractRole.code in :#{#code}"

into

 "WHERE rcras.relatedContractRole.code in :#{#code}" +
        "AND rcras.contract.id = :#{#contract.id} "

it will fix your problem

UPDATE: SpingDataJpa team has fixed it. https://jira.spring.io/browse/DATAJPA-712

score 1 · Answer 2 · edited May 23 '17 at 12:29

1

For a JPA in clause you must not write brackets. (same question same answer here https://stackoverflow.com/a/4379008/280244)

@Query ("FROM Person p " +
    "LEFT JOIN p.relatedContractRoleAttributions rcras " +
    "WHERE rcras.contract.id = :#{#contract.id} " +
    "AND rcras.relatedContractRole.code IN :#{#codes)")

And to be honest: I never have seen this syntax :#{#PARAM} before, I only know :PARAM. - but when it work in the other query is will work in the next one too

@Franck Yapadesouci Anso: according to your comment: are you sure that you can use the SPEL expressions in this way - try it the JPA way.

@Query ("SELECT p FROM Person p " +        //+SELECT p
    "LEFT JOIN p.relatedContractRoleAttributions rcras " + 
    "WHERE rcras.contract = :contract" +    //without ID and SPEL
    "AND rcras.relatedContractRole.code IN :codes") //without brackets and SPEL

edited May 23 '17 at 12:29

Community

1
1

answered Mar 18 '15 at 20:30

Ralph

118,862
56
287
383

I tried without bracket and I have the same error. For the other part, you can go to the documentation 3.3.6 Using SpEL expressions here : http://docs.spring.io/spring-data/jpa/docs/current/reference/html/ – Franck Anso Mar 18 '15 at 21:31
@Franck Yapadesouci Anso: Do you use JPA? – Ralph Mar 19 '15 at 07:10
1

@Ralph `Spel` expressions are allowed. Check https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions and http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query.spel-expressions – geoand Mar 19 '15 at 08:25

Spring Data JPA @Query with spel in a IN query

2 Answers2

Linked