hibernatespring-mvcspring-dataspring-data-jpa

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


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_

Solution

  • 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.