使用Hibernate Criteria实现in子查询和exists子查询

使用Hibernate Criteria实现in子查询和exists子查询

使用Criteria实现以下SQL语句的效果(in):

select * from        BK_ProjectCard this_     where        this_.resCompanyId in (            select                this_.id as y0_             from                SYSTEM_DEPARTMENT_INFO this_        )

实现代码:
Criteria criteria = this.getSessionFactory().getCurrentSession().createCriteria(ProjectCard.class);DetachedCriteria inCriteria = DetachedCriteria.forClass(DepartmentInfo.class);inCriteria.setProjection(Property.forName("id"));criteria.add(Property.forName("resCompanyId").in(inCriteria));List<ProjectCard> projectCards = criteria.list();


使用Criteria实现以下SQL语句的效果(exists):

select * from BK_ProjectCard pc where exists in(select this_.id as y0_ from SYSTEM_DEPARTMENT_INFO this_ where this_.id = pc.resCompanyId)


实现代码:
Criteria criteria = this.getSessionFactory().getCurrentSession().createCriteria(ProjectCard.class, "pc");DetachedCriteria existsCriteria = DetachedCriteria.forClass(DepartmentInfo.class, "sdi");existsCriteria.add(Property.forName("sdi.id").eqProperty(Property.forName("pc.resCompanyId")));existsCriteria.add(Restrictions.like("name", "%test%"));criteria.add(Subqueries.exists(existsCriteria.setProjection(Projections.property("sdi.id"))));List<ProjectCard> projectCards = criteria.list();
PS:实体类中并没有建立两个对象的关联关系,仅在ProjectCard对象中创建了一个resCompanyId的属性,用于存储DepartmentInfo表的ID。

推荐阅读