高分求一个 HQL 的多对多映射

[猪小烧]

create table employee(
    empId    varchar(20),
    userId   varchar(20)
)
create table user(
    userId   varchar(20),
    depId   varchar(20)
)
create table dep(
    depId    varchar(20),
    depName  var(20)
)
create table pop(
    popId    varchar(20),
    popName  varchar(20)
)

create table T_dep_pop(
    depId   varchar(20),
    popId   varchar(20)
)
insert into dep values('1','办公室');

insert into pop values('1','青秀区');
insert into pop values('2','罗湖区');
insert into pop values('3','秀峰区');

insert into T_dep_pop values('1','1);
insert into T_dep_pop values('1','2');

insert into user values('1','1');

insert into employee values('1','1');

<!--部门映射-->
<set name="popedoms" table="T_DepartmentPopedom" lazy="true" outer-join="false" cascade="all">
			<key column="departmentId"/>
			<many-to-many class="entity.basic.Popedom" column="popedomId" />
		</set>

<!--辖区映射-->
<set name="departments" table="T_DepartmentPopedom" lazy="true">
			<key column="popedomId"/>
			<many-to-many class="entity.basic.Department" column="departmentId" />
		</set>

<!-- 人员映射-->
<many-to-one name="userInfo" class="entity.system.UserInfo"
			fetch="select" lazy="proxy" cascade="none">
			<column name="userInfoId" length="32" not-null="true" />
		</many-to-one>
<!-- 用户映射-->
<many-to-one
			name="department"
			column="departmentId"
			class="entity.basic.Department"
			insert="false"
			lazy="proxy"
		/>

上次已经写出 SQL 了 但是 对HQL 的hibernate 映射 写了半天 没写出来.....
晕乎乎

select * from 人员表 a,用户表 b,(
select * from 部门辖区中间表 
where 辖区ID=(select 辖区ID from 部门辖区中间表 
                where 部门ID=(select 用户所属部门ID from 用户表 
                                where 用户ID=@用户ID)) c
where a.用户ID=b.用户ID and b.部门ID=c.部门ID

我写的 HQL....

 select p from Proposer as p, UserInfo as u,  
 (select T from T_DepartmentPopedom as T where popedomId in 
 (select popedomId from T_DepartmentPopedom where departmentId =  
 (select departmentId from UserInfo as us where us.id = '1'))) as c 
 where p.userInfo.id = u.id 
 and u.department.id = c.departmentId  
 and c.popedomId = p.Popedom.id

我想查询 用户ID = 1, 所在 的部门id = 1,下面所包含的辖区 = 1,2, 所 包含的 人员信息 userInfo

[猪小烧]

老是出

in expected: select [
select count (*) from entity.apply.Proposer as p,
entity.system.UserInfo as u,
(select T from T_DepartmentPopedom as T where popedomId in
(select popedomId from T_DepartmentPopedom where departmentId =
(select departmentId from entity.system.UserInfo as us where us.id = '1'))) as c
where p.userInfo.id = u.id
and u.department.id = c.departmentId
and c.popedomId = p.Popedom.id];


异常..........

奇怪的是 好像第一次 写好的时候能用????????

不知道是我没有 添加方法还是如何 眼花了

[东那个升]

不懂帮顶

[猪小烧]

我 看到网上可以使用 这样的语句进行查询 但是我 的老是出错....

类似与:
select distinct u from User u,Field f where u.gender='m' and u in elements(f.users) and f.id like '520%'

带有 in elements()
的语句

是我的hibernate 版本不对?

[feixianxxx]

不懂 帮顶 。。。。

[ws_hgo]

不会...............