如何使用JPQL从连接表中删除条目？

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

我有一个JPA对象,它具有这样的多对多关系:

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

RolePrivilege没有JPA对象,所以我不确定如何编写JPQL查询来从角色对象的privs字段中删除条目。例如,我试过这个,但它不起作用。它抱怨没有映射Role.privs。

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.

我不知道还有什么可以尝试的。我当然可以编写一个从连接表RolePrivilege中删除的本机查询。但是,我担心这样做会与本地缓存的对象发生严重的交互,本地缓存的对象不会被本机查询更新。

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.

是否甚至可以编写JPQL来删除连接表中的条目?如果不是,我可以只加载所有Role对象并从每个对象的privs集合中删除条目,然后保留每个角色。但是,如果一个简单的JPQL查询可以同时完成所有操作,那么这似乎很愚蠢。

3 个解决方案

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

JPQL更新和删除语句需要引用实体名称,而不是表名,所以我认为你对你建议的方法运气不好。

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:

根据您的JPA提供程序,您可以使用简单的原始SQL语句(应该100%可移植)从JoinTable中删除条目,然后以编程方式与缓存提供程序API交互以驱逐数据。例如,在Hibernate中,您可以调用evict()来使第二级缓存中的所有“Role.privs”集合到期:

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.

不幸的是,我没有使用JPA API足以确定支持的确切内容。

#2

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?

我也在寻找一种JPQL方法来删除多对多关系(包含在连接表中)。显然,ORM中没有表的概念,所以我们不能使用DELETE操作......但我希望这些情况有一个特殊的运算符。像LINK和UNLINK之类的东西。也许是未来的特色?

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.

无论如何,我为实现这一需求而一直在做的是使用实体bean中实现的集合,这些集合映射了多对多关系。

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.

我在实体中构建了该集合的getter和setter。然后,例如,从EJB,我使用getter检索集合,添加或删除所需的课程,最后,使用setter分配新集合。它已经完成了。它完美地运作。

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

不过我主要担心的是表现。 ORM应该保留所有对象的大量缓存(如果我没有记错的话),但即使使用它来更快地工作,我想知道从集合中检索所有元素是否真的有效...

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

因为对我来说,从表中检索注册表并使用纯Java而不是直接或间接与内部数据库引擎(SQL,JPQL ...)一起使用的查询语言对它们进行后过滤是非常低效的。

#3

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.

Java是一种面向对象的语言,ORM的重点是隐藏连接表的细节等等。因此,即使考虑从连接表中删除而不考虑后果也会很奇怪。

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

不,你不能用JPQL,即实体。

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

检索两端的实体并清除其集合。这将删除连接表条目。面向对象。

#1