Spring Data delete function not deleting records

Question

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 the UserRoleUser table are not being deleted. What could be the issue? I am using Spring Data and QueryDsl.

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();
    }
}

Answer 1

If you need to use the given methods provided by CrudRepository, use the JpaRepository.deleteInBatch(). This solves the problem.

Answer 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);

Answer 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:

1. The cache takes note of the requested child exclusion
2. The cache however does not verify any changes in Set<UserRoleUser>
3. 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:

1. 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 as userRoleUserRepository.deleteByIdIn(userRoleUserList.stream().map(UserRoleUser::getId).collect(Collectors.toList())) would have to be employed
2. 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. As repo.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:

1. Does not cascade to other entities
2. 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.