Java: Hibernate - Query to delete from multiple tables.

Question

Hi, i have two tables connected by a foreign key with a one to many relation.

in entity A i have the following :

@org.hibernate.annotations.Cascade( {
 org.hibernate.annotations.CascadeType.ALL,
 org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
@OneToMany(mappedBy="monitoredFlight", fetch = FetchType.LAZY)
@OnDelete(action=OnDeleteAction.CASCADE)
private List<bTable> BTable = new ArrayList<BTable>();

now i try to delete from table A with a bulk delete query:

Query query = em.createQuery("delete from A where originDateTime<:date");

and i get the foreign key constraint error. so i decided to do the delete with a join just as i would in mysql, so i change it to:

Query query = em.createQuery("delete from A join BTable where originDateTime<:date");

and i get a syntex error, i have tried several combination with or without join and nothing works, any ideas?

i am using mysql as a database and java as the language

Answer 1

You can setup a foreign key with the parameter on delete cascade so that when the key it references is deleted all rows that it is a foreign key on are also deleted.

Answer 2

You can use a native query, the following should work in mysql:

delete a , b from a inner join b on a.id=b.a_id where ...