Spring Data IN clause adds additional brackets

I have a ‘Role’ table with a ‘name’ column. I need to get all roles where names are either ‘role1’ or ‘role2’. Role repository method looks like this:

Set<Role> findByNameIsIn(Set<String> roleNames);

My database contains only ‘role1’. The request that is generated looks like this:

SELECT ID, NAME FROM ROLE WHERE (NAME IN ((?,?)))
    bind => [role1, role2]

Please notice the double brackets around the parameters. Result set is empty. When I try this query manually through the h2 console – no results as well. The following query works:

SELECT ID, NAME FROM ROLE WHERE (NAME IN ('role1', 'role2'))

My set contains two elements exactly. Sets should be supported as a parameter type. See:https://dzone.com/refcardz/core-spring-data

And finally the question: What am I missing?

Answer

As Oliver Gierke mentioned – there is a bug opened for this issue in EclipseLink (this is what I’m using as a persistence provider) issue tracker. Since 2011!.. Here is the workaround:

@Query("select r from Role r  where r.name in ?1")
Set<Role> findByNameIsIn(Set<String> roleNames);

And here is the valid generated query:

SELECT ID, NAME FROM ROLE WHERE (NAME IN (?,?))
    bind => [role1, role2] 

Leave a Reply

Your email address will not be published. Required fields are marked *