JPQL,如何不选择某些内容 [英] JPQL, How to NOT select something
本文介绍了JPQL,如何不选择某些内容的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要执行一个非常简单的SQL.
I have a pretty simple SQL I need to perform.
我有一个ProcessUser
,Role
和一个ProcessUserRole
表.直接多对多
I have a ProcessUser
, Role
and a ProcessUserRole
table. A straight forward many-to-many
我想选择所有也具有管理员角色的ProcessUser
.
I want to select all ProcessUser
's that does also have a Role of admin.
但是我的JPQL失败了,因为我的用户还具有角色官员,因此可以在列表中对其进行检索.
However my JPQL fails because my user also has role officer, so it is retrieved in the list.
这是JPQL:
entityManager.createQuery("SELECT p FROM " + ProcessUser.class.getName()
+ " p join p.roles role WHERE role.name NOT IN ('sysadmin')").getResultList();
生成的SQL是:
select
distinct processuse0_.id as id8_,
processuse0_.position as position8_,
processuse0_.username as username8_,
processuse0_.organization_id as organiza9_8_,
processuse0_.passwordHash as password4_8_,
processuse0_.fromEmail as fromEmail8_,
processuse0_.firstname as firstname8_,
processuse0_.lastname as lastname8_,
processuse0_.processes as processes8_
from
ProcessUser processuse0_
inner join
ProcessUserRoles roles1_
on processuse0_.id=roles1_.userId
inner join
Role role2_
on roles1_.roleId=role2_.id
where
(
role2_.name not in (
'sysadmin'
)
)
推荐答案
使用子查询的正确JPQL语法:
Proper JPQL syntax using subquery:
SELECT p FROM ProcessUser p
WHERE p.id NOT IN (
SELECT p2.id FROM ProcessUser p2
JOIN p2.roles role
WHERE role.name='sysadmin'
)
这篇关于JPQL,如何不选择某些内容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文