How to delete a ManyToMany item using JPQL?

Question

I have two models, say :

BlogPost(title, message)
Tags(name)

Both have a ManyToMany relationship defined.

Using JPQL, I delete a list of BlogPost with this query:

DELETE FROM BlogPost b WHERE b IN :list

(:list is a List from a previous SELECT requet).

Doing so, I have a ConstraintViolationException because of the relation between BlogPost and Tags.

Is there a way to delete the relation without deleting the Tags using JPQL?

Thanks for your help!

Possible duplicate : http://stackoverflow.com/questions/1082095/how-to-remove-entity-with-manytomany-relationship-in-jpa-and-corresponding-join — Cyril Gandon, Jun 28 '11 at 15:09

score 0 · Answer 1 · answered Jun 28 '11 at 15:07

0

You have to remove the association first, before you delete the entity.

answered Jun 28 '11 at 15:07

Puce

37,247
13
80
152

And how can I do that using jpql? – Cyril N. Jun 28 '11 at 15:15
You don't do this with JPQL. You do this by manipulating the entity objects. If they are in attached and mainpulated in the same transaction, then it should be fine. – Puce Jun 28 '11 at 15:34
Could you show me how to manipulate the entity objects? What I want to do is delete many BlogPost without having to do a loop and calling `->delete()` for each entry. Just doing it in one query. – Cyril N. Jun 29 '11 at 07:52

Cyril Gandon · Answer 2 · 2011-06-28T15:20:34.147

0

JPA create a table BlogPost_Tags which stores ID of BlogPost and Tags.

So when you try to delete a BlogPost, the constraint on the BlogPost_Tags failed.

You need to delete the relation before delete the Post, and there is no easy way in JPQL, you have to use the EntityManager.

edited Jun 28 '11 at 15:20

answered Jun 28 '11 at 15:08

Cyril Gandon

16,830
14
78
122

score 0 · Accepted Answer · answered Jun 29 '11 at 10:56

I'll answer myself with the solution I came up. I'm not sure it's the best one, but at least, it works.

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.

How to delete a ManyToMany item using JPQL?

3 Answers3