How to batch delete using bulkUpdate

Question

I have a common User / Role setup, with a user_role join table. I'm trying to use Spring's HibernateTemplate to mass delete all locked users like this:

getHibernateTemplate().bulkUpdate("delete from User where locked=?", true);

If the user being deleted does not have any roles (no record in the user_role table), then everything goes fine; however if the user does have a role record, I'm getting the following error:

integrity constraint violated - child record found

Roles are defined in User.java like this:

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "user_role", joinColumns = { @JoinColumn(name = "user_id") }, inverseJoinColumns = @JoinColumn(name = "role_id"))
private Set<Role> roles = new HashSet<Role>();

So how can I batch delete users even if a user has child records? Thanks!

Answer 1

Bulk delete operations are not cascaded to related entities as per the JPA specification:

4.10 Bulk Update and Delete Operations

Bulk update and delete operations apply to entities of a single entity class (together with its subclasses, if any). Only one entity abstract schema type may be specified in the FROM or UPDATE clause.

...

A delete operation only applies to entities of the specified class and its subclasses. It does not cascade to related entities.

However, I'd expect the JPA provider to deal with join tables. Sadly, Hibernate doesn't and this is logged in HHH-1917. I'm afraid you'll have to fall back on native SQL to clean up the join table yourself or to use cascading foreign keys in the schema.