Spring Data delete function not deleting records
Solution 1
If you need to use the given methods provided by CrudRepository
, use the JpaRepository.deleteInBatch()
. This solves the problem.
Solution 2
The problem is the entities are still attached and will not be deleted until they become detached. If you delete by their id instead of the entity itself, it will delete them.
One thing I noticed is you are deleting the users one at a time which could lead to a database performance hit as the query will be recreated each time. The easiest thing to do is to add all the ids to a set then delete the set of ids. Something like this:
Set<Integer> idList = new HashSet<>();
for (UserRoleUser userRoleUser : findAll) {
idList.add(userRoleUser.getId());
}
if (!idList.isEmpty()) {
userRoleUserRepository.delete(idList);
}
then in your repository add the delete method
@Modifying
@Query("DELETE FROM UserRoleUser uru WHERE uru.id in ?1")
@Transactional
void delete(Set<Integer> id);
Solution 3
The reason why the child objects (UserRoleUser
) are not being deleted upon userRoleUserRepository.delete(userRoleUser)
call is that each UserRoleUser
points to a Users
which in turn holds a @OneToMany reference Set<UserRoleUser> userRoleUser
.
As described in this StackOverflow answer, what your JPA implementation (e.g. Hibernate) effectively does is:
- The cache takes note of the requested child exclusion
- The cache however does not verify any changes in
Set<UserRoleUser>
- As the parent @OneToMany field has not been updated, no changes are made
A solution would go through first removing the child element from Set<UserRoleUser>
and then proceed to userRoleUserRepository.delete(userRoleUser)
or userRepository.save(user)
In order to avoid this complication two answers have been provided:
- Remove element by Id, by calling
userRoleUserRepository.deleteById(userRoleUser.getId())
: in this case the entity structure (and therefore the parent) is not checked before deletion. In the analog case of deleteAll something more convoluted such asuserRoleUserRepository.deleteByIdIn(userRoleUserList.stream().map(UserRoleUser::getId).collect(Collectors.toList()))
would have to be employed - Convert your CrudRepository to a JpaRepository and use its
deleteInBatch(userRoleUserList)
method. As explained in this article and this StackOverflow answer the deleteInBatch method tries to delete all records at once, possibly generating a StackOverflow error in the case the number of records is too large. Asrepo.deleteAll()
removes one record at a time this error it minimizes this risk (unless the call is itself inside a @Transactional method)
According to this StackOverflow answer, extra care should be used when recurring to deleteInBatch
as it:
- Does not cascade to other entities
- Does not update the persistence context, requiring it to be cleared (the method bypasses the cache)
Finally , as far as I know , there is no way this could be done by simply calling userRoleUserRepository.delete(userRoleUser)
without first updating the parent object. Any updates on this (whether by allowing such behaviour through annotations, configuration or any other means) would be a welcome addition to the answer.
Kihats
Updated on June 07, 2022Comments
-
Kihats almost 2 years
I have the following simple application
Users
Entity@Entity public class Users implements Serializable { @Id @GeneratedValue private long id; private String name; @OneToMany(mappedBy = "user", fetch = FetchType.EAGER, cascade = {CascadeType.ALL}) private Set<UserRoleUser> userRoleUser; // GETTERS AND SETTERS }
UserRole
Entity@Entity public class UserRole implements Serializable { @Id @GeneratedValue private long id; private String roleName; @OneToMany(mappedBy = "userrole", fetch = FetchType.LAZY, cascade = CascadeType.ALL) private Set<UserRoleUser> userRoleUser; // GETTERS AND SETTERS }
UserRoleUser
Many to many resolver class@Entity public class UserRoleUser implements Serializable { @Id @GeneratedValue private long id; @ManyToOne @JoinColumn(name = "fk_userId") private Users user; @ManyToOne @JoinColumn(name = "fk_userroleId") private UserRole userrole; // GETTERS AND SETTERS }
UserRoleUserRepository
@Repository @Transactional public interface UserRoleUserRepository extends JpaRepository<UserRoleUser, Long>, QueryDslPredicateExecutor<UserRoleUser>{ }
Main
Application
class@SpringBootApplication @Configuration public class Application { public static void main(String[] args) { ConfigurableApplicationContext context = SpringApplication.run(Application.class, args); UserRoleUserRepository userRoleUserRepository = context.getBean(UserRoleUserRepository.class); Iterable<UserRoleUser> findAll = userRoleUserRepository.findAll(QUserRoleUser.userRoleUser.id.gt(0)); for (UserRoleUser userRoleUser : findAll) { userRoleUserRepository.delete(userRoleUser); } } }
On running the
main
application, the database records in theUserRoleUser
table are not being deleted. What could be the issue? I am usingSpring Data
andQueryDsl
.I have also tried putting the delete functionality on a
Controller
but still doesn't work.@RestController @RequestMapping("/api") public class DeleteController { @Autowired UserRoleUserRepository userRoleUserRepository; @GetMapping("/delete") public String delete() { Iterable<UserRoleUser> findAll = userRoleUserRepository.findAll(QUserRoleUser.userRoleUser.id.gt(0)); for (UserRoleUser userRoleUser : findAll) { userRoleUserRepository.delete(userRoleUser); } return new Date().toString(); } }