子查询应用在HQL语句的where子句中，子查询语句需要放在()里面。
使用子查询关键字进行查询结果量化
- all 子查询语句返回的所有结果
- any 子查询语句返回任意一条记录
- some 子查询语句返回任意一条记录
- in 与=any意思相同
- exists 子查询语句至少返回一条记录
查询所有员工工资都小于5000的部门
List<Department> departments=this.getCurrentSession().createQuery("from Department d where 5000> all (select e.salary from d.emps e) and d.emps.size>0").list();
Hibernate:
select
department0_.deptNo as deptNo0_,
department0_.deptName as deptName0_,
department0_.location as location0_
from
project.Department department0_
where
5000>all (
select
emps1_.salary
from
project.Emp emps1_
where
department0_.deptNo=emps1_.deptNo
)
and (
select
count(emps2_.deptNo)
from
project.Emp emps2_
where
department0_.deptNo=emps2_.deptNo
)>0
查询至少有一位的员工的工资低于5000元的部门
List<Department> departments=this.getCurrentSession().createQuery("from Department d where 5000> any (select e.salary from d.emps e) and d.emps.size>0").list();
Hibernate:
select
department0_.deptNo as deptNo0_,
department0_.deptName as deptName0_,
department0_.location as location0_
from
project.Department department0_
where
5000>any (
select
emps1_.salary
from
project.Emp emps1_
where
department0_.deptNo=emps1_.deptNo
)
and (
select
count(emps2_.deptNo)
from
project.Emp emps2_
where
department0_.deptNo=emps2_.deptNo
)>0
查询有员工工资正好是5000元的员工
List<Department> departments=this.getCurrentSession().createQuery("from Department d where 5000 in (select e.salary from d.emps e) and d.emps.size>0").list();
Hibernate:
select
department0_.deptNo as deptNo0_,
department0_.deptName as deptName0_,
department0_.location as location0_
from
project.Department department0_
where
(
5000 in (
select
emps1_.salary
from
project.Emp emps1_
where
department0_.deptNo=emps1_.deptNo
)
)
and (
select
count(emps2_.deptNo)
from
project.Emp emps2_
where
department0_.deptNo=emps2_.deptNo
)>0
查询至少有一位员工的部门
List<Department> departments=this.getCurrentSession().createQuery("from Department d where d.emps.size>0").list();
List<Department> departments=this.getCurrentSession().createQuery("from Department d where exists (from d.emps)").list();
Hibernate:
select
department0_.deptNo as deptNo0_,
department0_.deptName as deptName0_,
department0_.location as location0_
from
project.Department department0_
where
exists (
select
emps1_.empNo
from
project.Emp emps1_
where
department0_.deptNo=emps1_.deptNo
)
操作集合的函数和属性
- size()或size 获取集合中元素的数目
- minIndex()或minIndex 对于建立了索引的集合，获得最小索引
- maxIndex()或maxIndex对于建立了索引的集合，获得最大索引
- minElement()或minElement对于包含基本类型元素的集合，获得集合中取值最小的元素
- maxElement()或maxElement对于包含基本类型元素的集合,获得集合中取值最大的元素
- elemet() 获取集合中的所有元素
查询指定员工所在的部门
List<Department> departments=this.getCurrentSession().createQuery("from Department d where ? in elements(d.emps) ")
.setParameter(0,emp)
.list();
Hibernate:
select
department0_.deptNo as deptNo0_,
department0_.deptName as deptName0_,
department0_.location as location0_
from
project.Department department0_
where
? in (
select
emps1_.empNo
from
project.Emp emps1_
where
department0_.deptNo=emps1_.deptNo
)
查询员工数目大于5的部门
List<Department> departments=this.getCurrentSession().createQuery("from Department d where d.emps.size>5 ").list();
Hibernate:
select
department0_.deptNo as deptNo0_,
department0_.deptName as deptName0_,
department0_.location as location0_
from
project.Department department0_
where
(
select
count(emps1_.deptNo)
from
project.Emp emps1_
where
department0_.deptNo=emps1_.deptNo
)>5
