1、

import java.util.List;import org.hibernate.*;import org.junit.Test;import cn.jbit.hibernatedemo.dao.HibernateUtil;import cn.jbit.hibernatedemo.entity.Dept;import cn.jbit.hibernatedemo.entity.Emp;public class Eg {    /**     * 查询工资高于平均工资的员工。     */    @Test    public void egEmp() {        Session session = null;        try {            // 获取session            session = HibernateUtil.currentSession();            List
     
       list = session.createQuery(                    "from Emp e where e.salary>(select avg(salary) from Emp)")                    .list();            for (Emp emp : list) {                System.out.println(emp.getEmpName() + "," + emp.getSalary());            }        } catch (Exception e) {            e.printStackTrace();        } finally {            // 关闭session            HibernateUtil.closeSession();        }    }    /**     * 查询所有员工工资都小于5000的部门。     */    @Test    public void eg5() {        Session session = null;        try {            // 获取session            session = HibernateUtil.currentSession();            List
      
        list = session                    .createQuery(                            "from Dept d where 5000>all(select e.salary from d.emps e) and d.emps.size>0")                    .list();            for (Dept dept : list) {                System.out.println(dept.getDeptName());            }        } catch (Exception e) {            e.printStackTrace();        } finally {            // 关闭session            if (session != null)                session.close();        }    }    /**     * 查询至少有一位员工工资低于5000的部门。     */    @Test    public void eg6() {        Session session = null;        try {            // 获取session            session = HibernateUtil.currentSession();            List
       
         list = session                    .createQuery(                            "from Dept d where 5000>any(select e.salary from d.emps e)")                    .list();            for (Dept dept : list) {                System.out.println(dept.getDeptName());            }        } catch (Exception e) {            e.printStackTrace();        } finally {            // 关闭session            if (session != null)                session.close();        }    }    /**     * ，查询员工工资正好是5000元的部门     */    @Test    public void eg7() {        Session session = null;        try {            // 获取session            session = HibernateUtil.currentSession();            List
        
          list = session                    .createQuery(                            "from Dept d where 5000=any(select e.salary from d.emps e)")                    .list();            for (Dept dept : list) {                System.out.println(dept.getDeptName());            }        } catch (Exception e) {            e.printStackTrace();        } finally {            // 关闭session            if (session != null)                session.close();        }    }    /**     * 查询员工工资正好是5000元的部门     */    @Test    public void eg7_1() {        Session session = null;        try {            // 获取session            session = HibernateUtil.currentSession();            List
         
           list = session                    .createQuery(                            "from Dept d where 5000=some(select e.salary from d.emps e)")                    .list();            for (Dept dept : list) {                System.out.println(dept.getDeptName());            }        } catch (Exception e) {            e.printStackTrace();        } finally {            // 关闭session            if (session != null)                session.close();        }    }    /**     * 查询员工工资正好是5000元的部门     */    @Test    public void eg7_2() {        Session session = null;        try {            // 获取session            session = HibernateUtil.currentSession();            List
          
            list = session .createQuery( "from Dept d where 5000 in (select e.salary from d.emps e)") .list(); for (Dept dept : list) { System.out.println(dept.getDeptName()); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭session HibernateUtil.closeSession(); } } /** * 查询至少有一位员工的部门 */ @Test public void eg8() { Session session = null; try { // 获取session session = HibernateUtil.currentSession(); List
           
             list = session.createQuery( "from Dept d where exists (from d.emps)").list(); for (Dept dept : list) { System.out.println(dept.getDeptName()); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭session HibernateUtil.closeSession(); } } /** * 查询指定员工所在部门 */ @Test public void eg9() { Session session = null; try { // 获取session session = HibernateUtil.currentSession(); Emp emp = new Emp(); emp.setEmpNo(1); List
            
              list = session .createQuery("from Dept d where ? in elements (d.emps)") .setParameter(0, emp).list(); for (Dept dept : list) { System.out.println(dept.getDeptName()); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭session HibernateUtil.closeSession(); } } /** * 查询指定员工所在部门 */ @Test public void eg9_1() { Session session = null; try { // 获取session session = HibernateUtil.currentSession(); Emp emp = new Emp(); emp.setEmpNo(1); List
             
               list = session .createQuery("from Dept d where ? in (from d.emps)") .setParameter(0, emp).list(); for (Dept dept : list) { System.out.println(dept.getDeptName()); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭session HibernateUtil.closeSession(); } } /** * 查询员工个数大于5的部门 */ @Test public void eg10() { Session session = null; try { // 获取session session = HibernateUtil.currentSession(); List
              
                list = session.createQuery( "from Dept d where d.emps.size>5").list(); for (Dept dept : list) { System.out.println(dept.getDeptName()); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭session HibernateUtil.closeSession(); } } /** * 查询员工个数大于5的部门 */ @Test public void eg10_1() { Session session = null; try { // 获取session session = HibernateUtil.currentSession(); List
               
                 list = session.createQuery( "from Dept d where size(d.emps)>5").list(); for (Dept dept : list) { System.out.println(dept.getDeptName()); } } catch (Exception e) { e.printStackTrace(); } finally { // 关闭session HibernateUtil.closeSession(); } }}