Hibernate 函数 ，子查询 和原生SQL查询

一. 函数

    聚合函数：count(),avg(),sum(),min(),max()

例：（1）查询Dept表中的所有的记录条数。

     String hql=" select count(*) from Dept ";

     Long count=(Long)session.createQuery(hql).uniqueResult();

当不确定返回的是什么类型的时候可以根据：变量名.getClass()方法得到类型

例如：count.getClass()  返回的是：java.lang.Long

     （2）查询所有员工的工资总和。

     String hql="select sum(salary) from Emp";

     Double salary=(Double)session.createQuery(hql).uniqueResult();

     （3）查询员工最低的工资。

     String hql="select min(salary) from Emp";

     Double salarymin=(Double)session.createQuery(hql).uniqueResult();

     （4）查询员工最高的工资

      String hql="select max(salary) from Emp";

      Double salarymax=(Double)session.createQuery(hql).uniqueResult();

     （5）查询员工的平均工资

      String hql="select avg(salary) from Emp";

      Double salaryavg=(Double)session.createQuery(hql).uniqueResult();

     （6）查询员工最低工资，最高工资和平均工资

      String hql="select min(salary),max(salary),avg(salary) from Emp";

      Object[] obj=(Object[])session.createQuery(hql).uniqueResult();

      System.out.println(obj[0]+"\t"+obj[1]+"\t"+obj[2]);

二.分组查询

    （1）按职位统计员工个数

     String hql="select  job,count(e)  from  Emp e group by job";

     List(Object[]) list=session.createQuery(hql).list();

     for(Object[] obj:list){

         System.out.println(obj[0]+"\t"+obj[1]);

      }

     Query的list()方法返回的集合包含2个Object[]对象数组类型的元素，每个对象数组对应查询结果中的一条记录，数组的第一个元素是

     job职位名称，第二个元素是count(e)员工个数。

    查询结果：

    ENGINEER   9

    SALES   12

    （2）查询各个部门的平均工资。

     String hql="select e.dept.deptName,avg(e.salary) from Emp e group by e.dept.deptName";

     Iterator<Object[]> list=session.createQuery(hql).list().iterator();

     while(list.hasNext()){

        Object[] obj=list.next();

        System.out.println(obj[0]+"\t"+obj[1]);

     }

     Query的list()方法返回的集合中包含3个Object[]对象数组类型的元素，每个对象数组对应查询结果中的一条记录，数组中的第一个元

     素是e.dept.deptName部门名称，第二个元素是avg(e.salary)员工平均工资 

     查询结果：

     研发部   3250.0

     市场部   5000.0

     财务部   3000.0

    （3）查询各个职位的最低工资和最高工资

     String hql="select job,min(e.salary),max(e.salary) from Emp e group by job";

     Iterator<Object[]> list=session.createQuery(hql).list().iterator();

      while(list.hasNext()){

      Object[] obj=list.next();

      System.out.println(obj[0]+"\t"+obj[1]+"\t"+obj[2]);

      }

      查询结果：

      ENGINEER   4900.0  5100.0

      SALES    2900.0   3100.0

      Query的list()方法返回的集合包含2个Object[]对象数组类型的元素，每个对象数组对应查询结果中的一条记录，数组的第一个元素是

      job职位名称，第二个元素是min(e.salary)员工最低工资，第三个元素是max(e.salary)员工最高工资。

    （4）查询各个部门平均工资高于4000元的部门名称，输出部门名称和部门平均工资

   String hql="select e.dept.deptName,avg(e.salary) from Emp e group by e.dept.deptName having avg(e.salary)>4000";

    List<Object[]> list=session.createQuery(hql).list();

     for(Object[] obj:list){

         System.out.println(obj[0]+"\t"+obj[1]);

     }

     查询结果：

     市场部   4987.5

 三.子查询（any()：返回任意一条记录    all()：返回的所有记录   some：和“any”意思相同  in：与“=any”意思相同  

      exists：至少返回一条记录）

    （1）查询所有员工工资小于5000的部门

     String hql="from Dept d where 5000>all(select e.salary from d.emps e)";

     List<Dept> list=session.createQuery(hql).list();

     for(Dept dept:list){

        System.out.println(dept.getDeptName());

     }

     在这里不明白用什么类型的来接收，就用上面提到的方法：变量名.getClass()方法得到

     根据部门表查询出工资低于5000的部门，根据导航属性可以得到员工的集合，在单独比较工资，即可得到数据。

     最不好理解的地方就是where条件后面的语句，只要仔细分析就可以明白。

     查询结果：

     财务部

     （2）查询至少有一位员工的工资低于5000的部门

      String hql="from Dept d where 5000>any(select e.salary from d.emps e)";

      List<Dept> list=session.createQuery(hql).list();

      for(Dept dept:list){

            System.out.println(dept.getDeptName());

      }

      得到结果：

      研发部

      财务部

      市场部

      （3）查询有员工工资正好是5000元的部门

       String hql="from Dept d where 5000=any(select e.salary from d.emps e)";

       List<Dept> list=session.createQuery(hql).list();

       for(Dept dept:list){

            System.out.println(dept.getDeptName());

        }

       其中hql语句也可以这样写：

       String hql="from Dept d where 5000=some(select e.salary from d.emps e)";

       或者：

       String hql="from Dept d where 5000  in  (select e.salary from d.emps e)";

       得到结果：

       市场部

      （4）查询至少有一位员工的部门     

        String hql="from Dept d where  exists  (from d.emps e)";

       List<Dept> list=session.createQuery(hql).list();

       for(Dept dept:list){

            System.out.println(dept.getDeptName());

        }

        查询结果：

        市场部

        研发部

        财务部

四.操作集合的函数和属性

    { 

       size()和size：获取集合中元素的数目

       minElement()和minElement：对于包含基本类型元素的集合，获得集合中取值最小的元素

       minIndex()和minIndex：对于建立了索引的集合，获得最小的索引

       maxIndex()和maxIndex：对于建立了索引的集合，获得最大的索引

       maxElement和maxElement：对于包含基本类型元素的集合，获得集合中取值最大的元素

       elements()：获取集合中的所有元素

     }

     （1）查询指定员工所在的部门

      Emp emp=new Emp();

      emp.setEmpNo(1);

      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.getDeptName());

      }

      查询结果：

      市场部

     （2）查询员工个数大于5的部门

      String hql="from Dept d where d.emps.size>5";

      List<Dept> list=session.createQuery(hql).list();

      for(Dept dept:list){

          System.out.println(dept.getDeptName());

      }

      查询结果：

       研发部

       市场部

五.原生SQL查询和命名查询

    原生SQL查询：

    （1）查询员工姓名中带有e的，并且职位为ENGINEER的员工

      String sql="select * from  EMP where ENAME like :ename and JOB：job";

      List<Object[]> list=session.createSQLQuery(sql).setString("ename","%e%").setString("job","ENGINEER").list();

      for(Object[] obj:list){

           System.out.println(obj[0]+"\t"+obj[1]);

       }

     查询结果：

     0   emp0

     1   emp1

     2   emp2

     原生SQL查询和HQL查询都使用了Query接口，对于原生SQL 查询方式，使用Session的createSQLQuery()方法来创建SQLQuery对

     象，createSQLQuery()方法的参数是本地底层数据库的SQL语句，Hibernate支持SQL语句使用命名参数和占位符“？”，SQLQuery

     接口继承了Query接口。

     SQLQuery接口的list()方法返回的List集合中存放的是关系数据，这些数据分别装载在3个Object[]对象数组中，每个数组对应查询结

     果集中的一行，行中的每一列都是Object类型。其中SQLQuery接口提供了addEntity()方法把查询结果集中的关系数据映射为对象。

   （2）使用addEntity()方法把关系数据映射为对象

     String sql="select * from EMP where ENAME LIKE ：ename and JOB：job";

     List<Emp>  list=session.createSQLQuery(sql).addEntity(Emp.class).setString("ename","%e%")

      .setString("job","ENGINEER").list(); 

       for(Emp emp:list){

              System.out.println(emp.getEmpName()); 

      }

      查询结果：

      0   emp0

      1   emp1 

      2   emp2

    （3）查询指定职位的员工，并输出员工姓名和部门名称

      String sql="select {e.*},{d.*} from EMP e join DEPT d on d.DEPTNO=e.DEPTNO where e.JOB=：job";

      List<Object[]> list=session.createSQLQuery(sql).addEntity("e",Emp.class).addJoin("d","e.dept")

       .setString("job","ENGINEER").list();

       for(Object[] obj:list){

           System.out.println(obj[0].getClass()+"\t"+obj[1].getClass());

       }

      使用SQLQuery接口的addJoin()方法，建立了Emp对象和Dept对象之间的关联，list()方法放回的List集合中存放的是Object[]

      数组对象，数组的第一个元素是Emp对象，第二个元素是Dept对象。

六.命名查询

     Hibernate支持在映射文件中定义字符串形式的查询语句，这样的查询语句称为命名查询语句。

     命名查询语句可以是原生SQL语句查询，也可以是HQL语句查询

    （1）查询指定职位的员工

       Emp.hbm.xml配置一个和<class>元素并列的<Query>元素

        <query name="findEmp">

              from Emp e where e.job:job

        </query>

        List<Emp> list=session.getNamedQuery("findEmp").setString("job","ENGINEER").list();

        for(Emp emp:list){

               System.out.println(emp.getEmpName());

         }

        查询结果：

         emp0

         emp1

         emp2   

        （2）原生SQL查询语句的命名查询

            Emp.hbm.xml 

          <sql-query name="findEmp">

                <return alias="e" class="Emp">

                select {e.*} from EMP e where e.job=：job

          </sql-query>

          List<Emp> list=session.getNamedQuery("findEmp").setString("job","ENGINEER").list();

          for(Emp emp:list){

               System.out.println(emp.getEmpName());

           }

          查询结果：

          emp0

          emp1

          emp2