Spring Data JPA repositories: IN-clause in derived query not working

Question

I have a repository that looks like this:

public interface UserRepository extends JpaRepository<User, Long>
{
    User findByEmailIgnoreCase(String email);

    @Query("select u from User u where u.id in (:ids)")
    Set<User> getByIdInSet(@Param("ids") Set<Long> ids);
}

When I call getByIdInSet I get the following error:

Caused by: java.lang.IllegalArgumentException: You have attempted to set a value of type class org.eclipse.persistence.indirection.IndirectSet for parameter ids with expected type of class java.lang.Long from query string select u from User u where u.id in (:ids).
    at org.eclipse.persistence.internal.jpa.QueryImpl.setParameterInternal(QueryImpl.java:933)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.setParameter(EJBQueryImpl.java:593)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)

Apparently I don't know what means because I have tried changing all sorts of data types and still get the same error. The set of ids I am passing contains just one id. Please point me in the right direction.

score 16 · Accepted Answer · answered Oct 31 '14 at 14:47

16

Try it without the brackets

@Query("select u from User u where u.id in :ids")

answered Oct 31 '14 at 14:47

Predrag Maric

23,938
5
52
68

1

Looks like that worked, thanks. Why do some places require parens around the collection? I have several other repositories that require the parens and the only difference I can see is the parens are on the entity's collection and not the param. Is that the distinction? – jensengar Oct 31 '14 at 14:57
1

My reference was [this post](http://stackoverflow.com/a/4379008/4074715), where it says that you don't need parens with a collection-valued parameter. Apparently, Hibernate behaves differently in different versions, don't know about EclipseLink. Couldn't find any official JPA reference about this, I will update my answer if I do find something. – Predrag Maric Oct 31 '14 at 15:01
2

[Here](http://stackoverflow.com/a/2793690/4074715) is another post about this, with reference to Hibernate bug with required parens around the parameter. – Predrag Maric Oct 31 '14 at 15:10

attacomsian · Answer 2 · 2019-06-06T09:25:42.513

14

Latest version of Spring Data JPA supports IN keyword which can be used to create the query like the following:

Set<User> findByIdIn(Set<Long> ids);

edited Jun 06 '19 at 09:25

answered Jan 06 '18 at 16:02

attacomsian

2,667
23
24

2

Also, accepts varargs though Set is the best alternative when it comes to IN clause (to enforce the unicity); `Set findByIdIn(Long... ids);` – Youness Dec 27 '18 at 21:08

Spring Data JPA repositories: IN-clause in derived query not working

2 Answers2

Linked