How to provide and use varargs or Iterable parameter for custom @Query in Spring Data JPA?

Question

I'm using Spring Data JPA (currently 1.11.3.RELEASE) with Hibernate (currently 5.2.10.Final) as JPA implementation

I've got the following scenario as (simplified) example, first a simple User entity class:

@Entity
public class User {

@Id
private Long id;

private boolean active;

// getters/setters (not shown)

}

with a CRUD-based User repository. This user repository should provide methods to deactivate a user identified by its id:

public interface UserRepository extends CrudRepository<User, Long> {

// deactivate single user
@Modifying
@Query("UPDATE User u SET active = false WHERE u.id = ?1")
void deactivate(Long id);

// TODO how to provide a deactivateAll method
}

This solution is well described in the Spring Data JPA documentation using @Query in its modifying form.

What I missed in the docs is how to provide a list of ids as varags or Iterable parameter to allow deactivation of multiple users at once. How can I do that?

Answer 1

There is a comparable example of a User repository doing this in the Spring Data JPA source code repository: UserRepository.java e.g. using @Query like this:

@Query("select u from User u where u.id in :ids")
Collection<User> findByIdsCustomWithNamedVarArgs(@Param("ids") Integer... ids);

So you can provide a list of ids either using varargs or Iterable<> as following (in this case using the named parameters variant):

@Modifying
@Query("UPDATE User u SET active = false WHERE u.id IN :ids")
void deactivateAll(@Param("ids") Long... ids);

@Modifying
@Query("UPDATE User u SET active = false WHERE u.id IN :ids")
void deactivateAll(@Param("ids") Iterable<Long> ids);