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

发布于 2024-07-25 12:45:25 字数 845 浏览 10 评论 0原文

我有一个 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 don't 不确定如何编写 JPQL 查询来从角色对象的 privs 字段中删除条目。例如，我已经尝试过这个，但它不起作用。它抱怨 Role.privs 未映射。

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

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

是否可以编写 JPQL 来从这样的连接表中删除条目？如果没有，我可以加载所有角色对象并从每个角色的 privs 集合中删除条目，然后保留每个角色。但如果一个简单的 JPQL 查询就能一次性完成所有工作，那么这样做似乎很愚蠢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

窗影残 2024-08-01 12:45:28

Java 是一种面向对象的语言，ORM 的全部意义在于向您隐藏连接表等的详细信息。因此，即使考虑从连接表中删除而不考虑后果也会很奇怪。

不，你不能使用 JPQL 来做到这一点，这是针对实体的。

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

回复收藏 0 原文

茶花眉 2024-08-01 12:45:27

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

不管怎样，为了实现这一需求，我一直在使用实体 bean 中实现的集合，即映射多对多关系的集合。

例如，如果我有一个与 Courses 具有多对多关系的 Student 类：

@ManyToMany
private Collection <Courses> collCourses;

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

然而我主要担心的是性能。 ORM 应该保留所有对象的巨大缓存（如果我没记错的话），但即使使用它来更快地工作，我想知道从集合中检索所有元素是否真的有效......

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

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

回复收藏 0 原文

ま昔日黯然 2024-08-01 12:45:26

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

根据您的 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

不幸的是，我对 JPA API 的了解不够，无法确定到底支持什么。

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.

回复收藏 0 原文

~没有更多了~