CODEWITHSUNDEEP
javahibernatespringorm
SamS
SamS

Reputation: 1581

How to batch delete using bulkUpdate

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!

Upvotes: 8

Views: 11444

Answers (4)

Cyril N.
Cyril N.

Reputation: 39909

Since you want to bulk delete something that have a ManyToMany related items, you first have to delete the relation (in the join table), or do a loop and for each item, delete manually (insane and too much heavy).

So, since JPQL does not allow to do it, a possible way is to make a native SQL query for deleting the id you want in the related table, and then, do the bulk delete.

Upvotes: 0

Pascal Thivent
Pascal Thivent

Reputation: 570615

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.

Upvotes: 8

Miguel Ping
Miguel Ping

Reputation: 18347

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.

Upvotes: 2

Greg Noe
Greg Noe

Reputation: 1156

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)

Upvotes: 0

Related Questions