对象:用户、用户组
关系:一个用户可以属于多个用户组,一个用户组也可以有多个用户
关联查询:查询指定用户组下的所有用户
方式一:
select User from User User join User.groups g where g.id = 1
hibernate解析成以下SQL
Hibernate: select user0_.id as id1_7_, user0_.createTime as createTi2_7_, user0_.description as descript3_7_, user0_.email as email4_7_, user0_.lastSetPasswordTime as lastSetP5_7_, user0_.loginName as loginNam6_7_, user0_.name as name7_7_, user0_.password as password8_7_, user0_.phone as phone9_7_, user0_.photo as photo10_7_, user0_.valid as valid11_7_ from user user0_ inner join user_usergroup groups1_ on user0_.id=groups1_.userId inner join usergroup usergroup2_ on groups1_.userGroupId=usergroup2_.id where usergroup2_.id=1
方式二:
select elements(UserGroup.users) from UserGroup UserGroup where UserGroup.id = 1
hibernate解析成以下SQL
Hibernate: select users1_.userId as col_0_0_ from usergroup usergroup0_ cross join user_usergroup users1_ where usergroup0_.id=users1_.userGroupId and usergroup0_.id=1
方式一是直接用join查出用户,方式二是先查出用户id,需要详细数据的时候,再根据id一条条查询每个用户。
无关联查询:查询非指定用户组下的所有用户
方式一:
from User User where not exists (select 1 from User u join u.groups g where g.id = 1 and User.id = u.id)
hibernate解析成以下SQL
Hibernate: select user0_.id as id1_7_, user0_.createTime as createTi2_7_, user0_.description as descript3_7_, user0_.email as email4_7_, user0_.lastSetPasswordTime as lastSetP5_7_, user0_.loginName as loginNam6_7_, user0_.name as name7_7_, user0_.password as password8_7_, user0_.phone as phone9_7_, user0_.photo as photo10_7_, user0_.valid as valid11_7_ from user user0_ where not (exists (select 1 from user user1_ inner join user_usergroup groups2_ on user1_.id=groups2_.userId inner join usergroup usergroup3_ on groups2_.userGroupId=usergroup3_.id where usergroup3_.id=1 and user0_.id=user1_.id))
方式二:
from User User where not exists (from UserGroup UserGroup where UserGroup.id = 1 and UserGroup in elements(User.groups))
hibernate解析成以下SQL
Hibernate: select user0_.id as id1_7_, user0_.createTime as createTi2_7_, user0_.description as descript3_7_, user0_.email as email4_7_, user0_.lastSetPasswordTime as lastSetP5_7_, user0_.loginName as loginNam6_7_, user0_.name as name7_7_, user0_.password as password8_7_, user0_.phone as phone9_7_, user0_.photo as photo10_7_, user0_.valid as valid11_7_ from user user0_ where not (exists (select usergroup1_.id from usergroup usergroup1_ where usergroup1_.id=1 and (usergroup1_.id in (select groups2_.userGroupId from user_usergroup groups2_ where user0_.id=groups2_.userId))))
方式二是在exists的子查询中再嵌套in子查询,效率上不如方式一。