现在的位置: 首页 > 综合 > 正文

Hibernate多对多关联和非关联查询

2013年07月18日 ⁄ 综合 ⁄ 共 2338字 ⁄ 字号 评论关闭

对象:用户、用户组

关系:一个用户可以属于多个用户组,一个用户组也可以有多个用户

关联查询:查询指定用户组下的所有用户

方式一:

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子查询,效率上不如方式一。

抱歉!评论已关闭.