使用 Hibernate Criteria API 查询实体不具有特定值的多对多关联

发布于 2024-12-01 21:09:13 字数 749 浏览 2 评论 0原文

我有两个类“用户”和“角色”，它们使用多对多关联相互映射。现在我尝试使用 Hibernate Criteria API 查询不具有特定角色的所有用户。但我有点卡住了。

为了查询具有特定角色的用户，我使用以下查询，效果很好。

Session session = getSessionFactory().getCurrentSession();          
Criteria mainCrit = session.createCriteria(boClass);
return mainCrit.createAlias("roles", "r").add( Restrictions.eq("r.name", roleName)).list();

现在我有点困惑如何反转查询并获取所有不具有特定角色的用户。如果可能的话，我想显式排除某个角色，并且不要查询用 OR 链接它们的所有角色，因为稍后可能会动态添加更多角色。

更新

为了更好地理解我的场景，您可以在另一个问题。

此外，我还要补充一点，Role 类的 name 属性是一个 Enum，不知道这是否重要或改变查询数据库的方式。

原文

I got two classes User and Role which are mapped to each other using a many to many associations. Now I'm trying to query all users which doesn't have a certain role using the Hibernate Criteria API. But I'm a little bit stuck.

To query users with a certain role I use the following query, which works just fine.

Session session = getSessionFactory().getCurrentSession();          
Criteria mainCrit = session.createCriteria(boClass);
return mainCrit.createAlias("roles", "r").add( Restrictions.eq("r.name", roleName)).list();

Now I'm a little bit confused how to reverse the query and get all user that don't have a certain role. If possible I want to explicit exclude a certain role and don't query for all roles chaining them with OR as there may be more roles added later dynamically.

UPDATE

To get a better understanding of my scenario you can see the association I'm trying to query in another question.

Furthermore I would also add that the name property of the Role class is an Enum, don't know if this is important or changes the way to query the database.

分享到QQ

分享到微博

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

发布评论

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

感情废物 2024-12-08 21:09:13

也许有一种更优雅的方法，但我发现的唯一方法是查询具有给定角色名称的用户角色中不存在任何角色的所有用户。

这样做是这样的：

Criteria c = session.createCriteria(User.class, "user");

DetachedCriteria roleOfUserWithName = DetachedCriteria.forClass(Role.class, "role");
roleOfUserWithName.createAlias("role.users", "userOfTheRole");
roleOfUserWithName.add(Restrictions.eqProperty("userOfTheRole.id", "user.id"));
roleOfUserWithName.add(Restrictions.eq("role.name", roleName);
roleOfUserWithName.setProjection(Projections.id());

c.add(Subqueries.notExists(roleOfUserWithName));

它相当于以下 HQL 查询：

select user from User user where not exists (
    select role.id from Role role inner join role.users userOfTheRole
    where userOfTheRole.id = user.id
    and role.name = :roleName);

There's perhaps a more elegant way, but the only one I've found is to query for all the users for which there doesn't exist any role in the user's roles which has the given role name.

This is done like this:

Criteria c = session.createCriteria(User.class, "user");

DetachedCriteria roleOfUserWithName = DetachedCriteria.forClass(Role.class, "role");
roleOfUserWithName.createAlias("role.users", "userOfTheRole");
roleOfUserWithName.add(Restrictions.eqProperty("userOfTheRole.id", "user.id"));
roleOfUserWithName.add(Restrictions.eq("role.name", roleName);
roleOfUserWithName.setProjection(Projections.id());

c.add(Subqueries.notExists(roleOfUserWithName));

It's equivalent to the following HQL query:

select user from User user where not exists (
    select role.id from Role role inner join role.users userOfTheRole
    where userOfTheRole.id = user.id
    and role.name = :roleName);

回复收藏 0 原文

~没有更多了~