hql 一对多判断子表是否为空_hibernate中HQL多对多的查询

现有三张表 TLXPURCHASE.采购事项审批表，TLXPURCHASEACTIVITY.采购招标活动对应表，TLXACTIVITY.招标活动表，采购事项审批表和采购活动表

是多对多关系。java中定义了两个实体类，ActivityPO和PurchasesPO，在ActivityPO类中定义了

private Set purchases;// 采购事项

字段。映射文件中配置了：

outer-join="auto" column="PURCHASEID" />

现要查询采购事项审批信息，条件是该采购事项审批信息未分配采购活动即未出现在采购招标活动对应表(TLXPURCHASEACTIVITY)中或分配了

采购活动且该采购活动的status为4.

HQL语句：

StringBuffer hql = new StringBuffer();

hql.append(" select new com.wisdom.lxgz.purchases.model.vo.PurchasesVO(");

hql.append(" po.id , sys.name, po.name, po.procurement, po.createDept, po.userName)");

hql.append(" from PurchasesPO po , SystemParamPO sys");

hql.append(" where po.status='4'");

hql.append(" and sys.id=CAST(po.type as bigint)");

hql.append(" and po.id not in (select pur.id from ActivityPO activity join activity.purchases pur where activity.status in ('0','1','2'))");

hql.append(" order by po.createDate desc");

hibernate执行上面的hql(其中的 hql.append(" and po.id not in (select pur.id from ActivityPO activity join activity.purchases pur where activity.status in ('0','1','2'))");)生成如下sql(部分):

.......and (purchasesp0_.id not in(select purchasesp6_.id from TLXACTIVITY activitypo4_, TLXPURCHASEACTIVITY purchases5_, TLXPURCHASE purchasesp6_ where activitypo4_.ID=purchases5_.ACTIVITYID and purchases5_.PURCHASEID=purchasesp6_.id and ((activitypo4_.STATUS in('0' , '1' , '2'))))).......

多对多关系中还有另外一种写法如下：

SELECT po.* FROM PurchasesPO po,ActivityPO activity where po.id in elements(activity.purchases)