How do you handle with bulk deleting by an array of IDs in Spring Data JPA?

Question

Now I have a class User, I get a request data of an array from the jsp or html.

list this Integer[] arr=[5,6,9,10,62,52,21]

and then I use two methods to finish bulking deleting action.

@Transactional
@Override
public void deleteUser(Integer id) {

    oneRepository.delete(id);
}


@Transactional
@Override
public void deleteSomeUser(Integer[] ids) {

    for (Integer id : ids) {

        deleteUser(id);

    }

}

I want to know that if it's a more efficient method to finish this action.

you can see my logs: it seems not so good!

[94, 95, 91, 92, 93]
Hibernate: 
    delete 
    from
        sshh_user 
    where
        ID=?


Hibernate: 
    delete 
    from
        sshh_user 
    where
        ID=?



Hibernate: 
    delete 
    from
        sshh_user 
    where
        ID=?



Hibernate: 
    delete 
    from
        sshh_user 
    where
        ID=?



Hibernate: 
    delete 
    from
        sshh_user 
    where
        ID=?



Hibernate: 
    select
        count(practice0_.ID) as col_0_0_ 
    from
        sshh_user practice0_

Answer 1

Just add the following to your user repository interface

void deleteByIdIn(List<Integer> ids);

Spring will automatically generate the appropriate query via method name derivation.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods

EDIT: A litte more detail on this

Using Springs Repository interfaces like CrudRepository, JpaRespository brings the basic set of database operations, like create, read, update, delete, paging, sorting and so on.

To manually add some simple queries like searching for a users name or mail address spring provides a fine mechnanism without annotating any string based HQL queries or similar.

Spring just analyses your method names, searching for keywords. Read the documentation link above which keywords are provided.

Example methods for a CrudRepository<User>:

Iterable<User> findByNameLike(String search) resolves to select * from user where name like '<search>'

void deleteByIdIn(List<Integer> ids) resolves to delete from user where id in ([ids])

UPDATE:

This will only work as a real bulk delete in Spring Boot Version < 2.0 !

Since Spring Boot 2.0 it will result in single delete queries to honour JPA Entity Lifecycle Events like preRemove and postRemove.

If you want to really bulk delete, please use the accepted answer.

Answer 2

Suppose you have a UserRepository like:

public interface UserRepository extends JpaRepository<User, Integer> {}

Then you can add a modifying query method like following into your UserRepository:

/**
 * Delete all user with ids specified in {@code ids} parameter
 * 
 * @param ids List of user ids
 */
@Modifying
@Query("delete from User u where u.id in ?1")
void deleteUsersWithIds(List<Integer> ids);

Finally you can change your bulk deletion service like following:

@Transactional
@Override
public void deleteSomeUser(Integer[] ids) {
    oneRepository.deleteUsersWithIds(Arrays.asList(ids));
}

This will generate a delete query like:

Hibernate: delete from users where id in (? , ? , ?)

Also be aware of Self Invocation issues when you calling one public advised method from another one.

Answer 3

You can use different method for deleteAll item list without call repository twice. When deleteAll method call, CrudRepository or JpaRepository look just objects id.

List<User> userList = userIdList.stream().map(id -> {
            User user = new User();
            user.setId(id);
            return user;
        }).collect(Collectors.toList());

userRepository.deleteAll(userList);