Hibernate-HQL查询（2）子查询、连接查询

package junit.test;

import hibch05.dao.HibernateSessionFactory;
import hibch05.pojo.Dept;
import hibch05.pojo.Emp;

import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

public class Ch05Test3 {

    @Test
    public void test() {

        Session session = HibernateSessionFactory.getSession();
        // 获取Query对象
        Query query = session.createQuery("from Emp");
        query.setFirstResult((3 - 1) * 5);
        query.setMaxResults(5);
        List<Emp> list = query.list();
        for (Emp emp : list) {
            System.out.println(emp.getEname());
        }
    }

    /*
     * left join
     */
    @Test
    public void test2() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Emp e left join fetch e.dept";
        // 获取Query对象
        Query query = session.createQuery(hql);
        List<Emp> list = query.list();
        for (Emp emp : list) {
            System.out.println(emp.getEname() + " " + emp.getDept().getDname());
        }
        // System.out.println(list.size());
    }

    /*
     * 子查询- 查询工资高于平均工资的员工
     */

    @Test
    public void test3() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Emp e where e.sal>(select avg(sal) from Emp)";
        Query query = session.createQuery(hql);
        List<Emp> list = query.list();
        for (Emp emp : list) {
            System.out.println(emp.getEname() + "  " + emp.getSal());
        }

    }

    /*
     * 子查询- 查询所有员工工资都小于5000的部门
     */

    @Test
    public void test4() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Dept d where 5000>all(select e.sal from d.emps e) and d.emps.size>0";
        Query query = session.createQuery(hql);
        List<Dept> list = query.list();
        for (Dept dept : list) {
            System.out.println(dept.getDname() + "  " + dept.getDeptno());
        }

    }

    /*
     * 子查询- 查询至少有一个员工工资低于5000的部门
     */

    @Test
    public void test5() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Dept d where 5000>any(select e.sal from d.emps e)";
        Query query = session.createQuery(hql);
        List<Dept> list = query.list();
        for (Dept dept : list) {
            System.out.println(dept.getDname() + "  " + dept.getDeptno());
        }

    }

    /*
     * 子查询- 查询有员工工资正好等于5000的部门 （方法一）
     */
    @Test
    public void test6() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Dept d where 5000=some(select e.sal from d.emps e)";
        Query query = session.createQuery(hql);
        List<Dept> list = query.list();
        for (Dept dept : list) {
            System.out.println(dept.getDname() + "  " + dept.getDeptno());
        }

    }

    /*
     * 子查询- 查询有员工工资正好等于5000的部门 （方法二）
     */
    @Test
    public void test7() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Dept d where 5000 in(select e.sal from d.emps e)";
        Query query = session.createQuery(hql);
        List<Dept> list = query.list();
        for (Dept dept : list) {
            System.out.println(dept.getDname() + "  " + dept.getDeptno());
        }

    }

    /*
     * 子查询- 查询至少有一位员工的部门
     */
    @Test
    public void test8() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Dept d where exists (from d.emps)";
        Query query = session.createQuery(hql);
        List<Dept> list = query.list();
        for (Dept dept : list) {
            System.out.println(dept.getDname() + "  " + dept.getDeptno());
        }
    }

    /*
     * 子查询- 查询指定员工的部门
     */
    @Test
    public void test9() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Dept d where ? in (from d.emps)";
        Query query = session.createQuery(hql);
        Emp emp = new Emp();
        emp.setEmpno(new Short("7788"));
        query.setParameter(0, emp);
        List<Dept> list = query.list();
        for (Dept dept : list) {
            System.out.println(dept.getDname() + "  " + dept.getDeptno());
        }
    }

    /*
     * 子查询- 查询员工个数大于5的部门
     */
    @Test
    public void test10() {
        Session session = HibernateSessionFactory.getSession();
        String hql = "from Dept d where d.emps.size>5";
        String hql2 = "from Dept d where size(d.emps)>5";
        Query query = session.createQuery(hql);
        List<Dept> list = query.list();
        for (Dept dept : list) {
            System.out.println(dept.getDname() + "  " + dept.getDeptno());
        }
    }
}