How do you handle with bulk deleting by an array of IDs in Spring Data JPA?
Solution 1
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.
Solution 2
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.
Solution 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);
Solution 4
If your repository interface extends CrudRepository
, you can simply use its deleteAll(Iterable<? extends T> var1)
method to delete a collection of entities:
@Repository
public interface UserRepository extends CrudRepository<User, Long> {
void deleteAll(List<User> usersToDelete);
}
Solution 5
I have use this function for delete list of elements in JPA repository
void deleteInBatch(List<Integer> list);
Related videos on Youtube
JSO
Updated on July 09, 2022Comments
-
JSO almost 2 years
Now I have a class User, I get a request data of an
array
from thejsp
orhtml
.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_
-
Chait about 7 yearsHello, please add some explanation to the code - for instance, what this does, etc. While providing links to documentation is useful, including that information is beneficial to everyone.
-
PaulNUK almost 7 yearsJust a word of warning on this solution, due to a bug in Eclipselink this won't work (it generates malformed queries) so you have to fall back to adding a query to the method name if you are using EclipseLink.
-
LazyProphet almost 7 yearsIts working perfectlty with Hibernate so far. Another (and far more flexible) way is to use QueryDSL (baeldung.com/rest-api-search-language-spring-data-querydsl)
-
Yuriy Kravets about 6 yearsthis does not work in version 2.0.5.RELEASE. deleteByIdIn generates as many delete queries as the number of the ids you provide.
-
Hein Blöd almost 6 yearsUpdated link to the Spring documentation about self invocation: docs.spring.io/spring/docs/current/spring-framework-reference/…
-
Hein Blöd almost 6 yearsNote that this will not automatically trigger cascaded deletes on entities in a relationship with the deleted entities, cf. stackoverflow.com/questions/23443188/…
-
Woland over 5 yearsAlso note, that there are sql in clause limit. For example, in PostgreSQL stackoverflow.com/questions/1009706/…
-
User1291 over 4 yearsNote taht this throws an exception if
ids
is empty. -
User1291 over 4 yearsNote that this throws an exception when
ids
is empty. -
Yuming Cao about 4 yearsTried deleteByIdIn() and looked into Hibernate debug log, looks like the method translated into multiple delete queries instead of the single query mentioned above. I ended up writing custom query as outlined in the accepted answer.
-
G_V almost 4 yearsYou should post actual code and mark it as such since the answer is correct. There's a button on top of the editor which creates a code block out of your highlighted text.
-
Dev Fh almost 3 yearsThis worked for me! However shouldn't Spring data JPA provide this functionality through method
repository.deleteAll(entinties) ,repository.deleteAllById(ids)
-
payne over 2 yearsHe's working with Primary Keys, though. This answer assumes that he's working with entities, or will construct some entities using the PKs.
-
payne over 2 yearsCould we get an example for people that use composite keys? (We use
@IdClass
on our entities) -
payne over 2 yearsHow would this work for composite keys declared via
@IdClass
? (talking about something similar to@Modifying @Query("delete from User u where u.prop1 and u.prop2 in ?1") void deleteUsersWithIds(List<MyCompositeKey> ids);
) -
payne over 2 yearsI created a specific question for that: stackoverflow.com/q/69747958/9768291
-
Rishabh Ryber about 2 yearsIt's not recommended to use RequestBody with DELETE method.
-
syydi about 2 yearsAuthor wanted to avoid separate Hibernate queries.
deleteAll
anddeleteAllById
seem to not solve this as per my test they still make separate queries. The solution from the accepted answer performed as described.