Java: Hibernate - Query to delete from multiple tables

Question

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. I decided to do the delete with a join just as I would in mysql, so I changed it to:

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

and I got a syntax error. I have tried several combination with or without join and nothing works; any ideas?

I am using mysql for the database and java for the language.

David Rabinowitz · Accepted Answer · 2009-08-20 07:36:45Z

4

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 ...

answered Aug 20, 2009 at 7:36

David Rabinowitz

30.5k16 gold badges95 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gandalf · Accepted Answer · 2009-08-19 16:32:57Z

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.

answered Aug 19, 2009 at 16:32

Gandalf

9,9158 gold badges58 silver badges91 bronze badges

2 Comments

DoviG Over a year ago

i have that allerady but i want to do a massive deletion and not a one by one deletion.

Gandalf Over a year ago

What do you mean? Every row that is deleted in Table A will subsequently cause a cascade delete on Table B for any rows that share the foreign key - hence you won't get the constraint error, instead all the foreign key mapped rows in Table B will also be deleted.

Collectives™ on Stack Overflow

Java: Hibernate - Query to delete from multiple tables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related