Hibernate----HQL查询

一、Hibernate获取Session对象:

//读取配置文件，创建Configuration
Configuration congiguration = new Configuration().configure();
//解析映射文件，创建SessionFactory
SessionFactory sf = configuration.buildSessionFactory();
//打开Session
Session session = sf.openSession();
//开启事务
Transaction tran = session.beginTransaction();
//接下来进行持久化操作

二、数据库选择的是Oracle,账户为Scott中的Emp与Dept表：
下面开始进行HQL查询

//内连接查询
String hql = "from Emp e inner join e.dept";
List<Object[]> list = session.createQuery(hql).list();
for (Object[] obj:list) {
    Emp emp = (Emp)obj[0];
    Dept dept = (Dept)obj[1];
    System.out.println(emp.getEname()+" "+dept.getDname());
}
//查询部门名称为‘SALES’的所有员工姓名
String hql = "from Emp e where e.dept.dname = 'SALES'";
List<Emp> list = session.createQuery(hql).list();
for(Emp emp : list){
    System.out.println(emp.getEname());
} 
//统计部门个数
String hql = "select count(d.deptno) from Dept d";
Long count = (Long)session.createQuery(hql).uniqueResult();
System.out.println(count);
//统计最低工资、最高工资以及平均工资
String hql = "select min(e.sal),max(e.sal),trunc(avg(e.sal),2) from Emp e";
Object[] obj = (Object[]) session.createQuery(hql).uniqueResult();
System.out.println(obj[0]+" "+obj[1]+" "+obj[2]);
//统计各个部门平均工资高于2000元的部门名称，打印部门名称、部门平均工资
String hql = "select round(avg(e.sal),2),e.dept.dname from Emp e group by e.dept.dname  having avg(e.sal)>2000";
List<Object[]> list = session.createQuery(hql).list();
for(Object[] obj:list){
    System.out.println(obj[0]+" "+obj[1]);
}
//查询工资高于平均工资的员工
String hql = "from Emp e where e.sal>(select round(avg(e1.sal),2) from Emp e1)";
List<Emp> list = session.createQuery(hql).list();
for(Emp emp : list){
    System.out.println(emp.getEname());
}
//查询所有员工工资都小于5000的部门
String hql = "from Dept d where 5000>all(select sal from d.emps)";
List<Dept> list = session.createQuery(hql).list();
for(Dept dept : list){
    System.out.println(dept.getDname());
}
//查询至少有一位员工工资低于5000的部门
String hql = "from Dept d where 5000>any(select sal from d.emps)";
List<Dept> list = session.createQuery(hql).list();
for(Dept dept : list){
    System.out.println(dept.getDname());
}

//查询至少有一位员工的部门
String hql = "from Dept d where d.emps.size>0";
//或者另一种形式
//String hql = "from Dept d where exists(from d.emps)";
List<Dept> list = session.createQuery(hql).list();
    for(Dept dept : list){
    System.out.println(dept.getDname());
}
//查询指定员工所在部门
Emp emp = new Emp(7369);
String hql = "from Dept d where ? in(from d.emps)";
//另一种写法
//String hql = "from Dept d where ? in elements(d.emps)";
List<Dept> list = session.createQuery(hql).setParameter(0, emp).list();
    for(Dept dept : list){
    System.out.println(dept.getDname());
}