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

I'm not entirely sure because it's hard for me to recreate this problem, but I think you might need to add a cascade to your @ManyToMany

@ManyToMany(cascade = CascadeType.ALL)

Answer 2

Application-level cascading (cascading through hibernate annotations or JPA annotations) only work if the actual entity is actually loaded from the db. When you use the hibernate template with HQL, you'll notice that the entities are not loaded, and the HQL is directly converted to SQL to be executed.

If you want to batch delete you have to use an HQL query to delete all relevant tables (ie roles) before deleting the parent table data.

Answer 3

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.