两表通过中间表多对多关联用HQL查询

如有user表，role表，userrole表，3者之间的关系是，user表和role表通过userrole表多对多关联。

 

user类：
public class user implements java.io.Serializable {
private Integer userId;
private String userName;
private Set userrole= new HashSet(0);
//get和set省略
}

 

role类：

public role implements java.io.Serializable {
private Integer roleId;
private String roleName;
private Set userrole= new HashSet(0);
//get和set省略
}

 

userrole类：

public userrole implements java.io.Serializable {
private Integer userroleID;
private role role;
private user user;
//get和set省略
}

 

对应的xml（只捡关联部分）：

user.hbm.xml：
<set name="userrole" inverse="true">
<key>
<column name="userId" not-null="true" />
</key>
<one-to-many class="mo.hibernate.map.userrole" />
</set>

 

role.hbm.xml：
<set name="userrole" inverse="true">
<key>
<column name="roleId" not-null="true" />
</key>
<one-to-many class="mo.hibernate.map.userrole" />
</set>

userrole.hbm.xml：
<many-to-one name="role" class="mo.hibernate.map.role" fetch="select">
<column name="roleId" not-null="true" />
</many-to-one>
<many-to-one name="user" class="mo.hibernate.map.user" fetch="select">
<column name="userId" not-null="true" />
</many-to-one>

 

问题是：如何用hql查找出角色为admin的用户的id和名字？

 

hql：select u.userId,u.userName from user u,userrole ur,role r where u.userID in(ur.user) and r.roleName='admin' and ur.userroleID in elements(r.userrole)

 

本例用到了in和in elements关键字，区别在于in elements用于set，in用于类对象。