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

Question

I have a JPA object which has a many-to-many relationship like this:

@Entity
public class Role {
    //...

    @ManyToMany(fetch=FetchType.EAGER)
    @JoinTable(
    name="RolePrivilege",
    joinColumns=
        @JoinColumn(name="role", referencedColumnName="ID"),
    inverseJoinColumns=
        @JoinColumn(name="privilege", referencedColumnName="ID")
    )
    private Set<Privilege> privs;
}

There isn't a JPA object for RolePrivilege, so I'm not sure how to write a JPQL query to delete entries from the privs field of a role object. For instance, I've tried this, but it doesn't work. It complains that Role.privs is not mapped.

DELETE FROM Role.privs p WHERE p.id=:privId

I'm not sure what else to try. I could of course just write a native query which deletes from the join table RolePrivilege. But, I'm worried that doing so would interact badly with locally cached objects which wouldn't be updated by the native query.

Is it even possible to write JPQL to remove entries from a join table like this? If not I can just load all the Role objects and remove entries from the privs collection of each one and then persist each role. But it seems silly to do that if a simple JPQL query will do it all at once.

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.