How do I use JPQL to delete entries from a join table?

Answer 1

The JPQL update and delete statements need to refer to an Entity name, not a table name, so I think you're out of luck with the approach you've suggested.

Depending on your JPA provider, you could delete the entries from the JoinTable using a simple raw SQL statement (should be 100% portable) and then programmatically interact with your cache provider API to evict the data. For instance in Hibernate you can call evict() to expire all "Role.privs" collections from the 2nd level cache:

sessionFactory.evictCollection("Role.privs", roleId); //evict a particular collection of privs
sessionFactory.evictCollection("Role.privs"); //evict all privs collections

Unfortunately I don't work with the JPA APIs enough to know for sure what exactly is supported.

Answer 2

Java is an object-oriented language and the whole point of ORM is to hide details of join tables and such from you. Consequently even contemplating deletion from a join table without considering the consequences would be strange.

No you can't do it with JPQL, that is for entities.

Retrieve the entities at either end and clear out their collections. This will remove the join tables entries. Object-oriented.

--Andy (DataNucleus)

Answer 3

I also was looking for a JPQL approach to delete a many-to-many relation (contained in a joining table). Obviously, there's no concept of tables in an ORM, so we can't use DELETE operations... But I wish there were a special operator for these cases. Something like LINK and UNLINK. Maybe a future feature?

Anyway, what I've been doing to achieve this need has been to work with the collections implemented in the entity beans, the ones which map the many-to-many relations.

For example, if I got a class Student which has a many-to-many relation with Courses:

@ManyToMany
private Collection <Courses> collCourses;

I build in the entity a getter and setter for that collection. Then, for example from an EJB, I retrieve the collection using the getter, add or remove the desired Course, and finally, use the setter to assign the new collection. And it's done. It works perfectly.

However my main worry is the performance. An ORM is supposed to keep a huge cache of all objects (if I'm not mistaken), but even using it to work faster, I wonder if retrieving all and every element from a collection is really effective...

Because for me it's as much inefficient as retrieving the registries from a table and post-filter them using pure Java instead of a query language which works direct or undirectly with the internal DB engine (SQL, JPQL...).