Hibernate Criteria JPA 子查询 exists 代码示例

select
	department0_.ID
from
	ORGAN_DEPARTMENT department0_
where
	exists (
	select
		'X'
	from
		FLOW_APPLY apply1_
	inner join FLOW_WORKFLOW flow2_ on
		apply1_.FK_FLOW = flow2_.ID
	inner join ORGAN_DEPARTMENT department3_ on
		flow2_.FK_DEPARTMENT_ID = department3_.ID
	where
		(exists (
			select
				'X'
			from
				FLOW_ANNEX attachment4_
			where
				attachment4_.TYPE_ = 'ATTACHMENT'
				and attachment4_.SUBMIT_METHOD ='ATTA'
				and attachment4_.FK_APPLY = apply1_.ID))
			and (apply1_.TYPE_ in ('Apply','cancel','proxy','EXTERNAL','EXTERNAL_PROXY'))
				and department3_.ID = department0_.ID)

以上是一个SQL  嵌套exists 的样例，相应的HIbernate Criteria Query 代码如下

通过CriteriaQuery#subquery创建子查询Subquery实例，然后通过CriteriaBuilder#exists加入条件  

"类名_"为Static Metadata Model类，通过Hibernate jpamodelgen模块生成

        CriteriaBuilder builder = session.getCriteriaBuilder();
        CriteriaQuery<Department> departQuery = builder.createQuery(Department.class);
        Root<Department> departmentRoot = departQuery.from(Department.class);
        Subquery<String> applyQuery = departQuery.subquery(String.class);
        Root<Apply> applyRoot = applyQuery.from(Apply.class);
        applyQuery.select(builder.literal("X"));
        Subquery<String> annexSubquery = applyQuery.subquery(String.class);
        Root<AttachmentAnnex> annexRoot = annexSubquery.from(AttachmentAnnex.class);
        annexSubquery.select(builder.literal("X"));
        annexSubquery.where(builder.equal(annexRoot.get(AttachmentAnnex_.submitMethod), AnnexDefinition.SubmitMethod.DEFICIENCY),
                builder.equal(annexRoot.get(AbstractApplyAnnex_.apply),applyRoot));
        Predicate typesPre = applyRoot.get(AbstractApply_.TYPE).in(List.of(ApplyQuery.EXTERNAL_APPLY_TYPES));
        applyQuery.where(builder.exists(annexSubquery), typesPre,
                builder.equal(applyRoot.join(AbstractApply_.flow).join(Flow_.department), departmentRoot));
        departQuery.where(builder.exists(applyQuery));
        List<Department> deptList = session.createQuery(departQuery).setReadOnly(true).setComment("事项所属部门-查询有容缺提交的部门").list();