How to handle deletion of many JPA entities which has foreign key relationships

Question

Problem: Entities (say Users) are showed in a paginated fashion in the UI. The UI also shows a checkbox against each entity, so that it is possible for the admin to multi-select users and delete them. It is quite natural that this entity is associated with many other entities via a foreign key relationship (say Purchase Orders created by him)

If a Purchase Order is associated with a certain user, user deletion will not be possible because of the foreign key violation. Similarly it is possible that User has relationship with many other such tables.

It would be preferable not to show the checkbox for deletion if the entity cannot be deleted. If such a check needs to be made, while constructing the user list page for each user row you need to query the dependant tables for possible relationships. This could be a very costly affair if there are many users.

What would be the suggested approach to solve this problem in a elegant fashion?

Answer 1

Five ways come to my mind:

1. Declare all dependencies with cascade=CascadeType.DELETE (or ALL). Then all will be deleted, but this is rarely the desired behaviour.

2. Show checkboxes everywhere, delete the users one by one (using session.remove() / entityManager.remove()) and catch any ConstraintViolationException. Something like:

   for (User user : usersToDelete) {
        try {
            entityManager.remove(user); // or dao.remove(), 
                                        // or someService.remove()
        } catch (Exception ex) {
            // verify if the there is `ConstraintViolationException` 
            // in the exception chain, and log a short message about the failure
            // If the exception is elsewhere, then log the whole exception
            // or rethrow
        }
   }
   

   Note that in this case, you might need to set the FlushMode of the EntityManager so that the exceptions are thrown at the right time.

3. Write database-specific code that checks the constraints of the user (or any object under the same scenario). I have done this once, because it allows to show exactly which records depend on the current one. I performed the database-specific checks on delete, so all users had checkboxes again.

4. Do as you said, but store all the results in some sort of cache (either your own cache or Hibernate's cache, whichever is easier for you to control). Thus the costly operations will be done rarely. Also, consider using paging for the users list.

5. Try to achieve ON CASCADE SET NULL. I don't if it is possible, perhaps a cascade=ALL + nullable=true. Check this thread. And if you not generating your database from your entities, then juts set the ON CASCADE SET NULL in the database.

And one related advice: Don't use HQL for deletion, because it disregards Hibernate cascades.