Hibernate_查询_HQL详解（二）_聚集函数、分组、连接查询、查询时使用参数的HQL语法

1，聚集函数
  2，分组
  3，连接查询 / HQL是面向对象的查询
    数据库中查询：
      select e.id_, e.name_, d.name_ from employee e inner join department d on e.departmentId=d.id_;(内连接)
      select e.id_, e.name_, d.name_ from employee e left outer join department d on e.departmentId=d.id_;(左外连接)
      select e.id_, e.name_, d.name_ from employee e right outer join department d on e.departmentId=d.id_;(右外连接)
  4，查询时使用参数
    >> 方式一：使用'?'占位
    >> 方式二：使用变量名

// 使用HQL查询
  @Test
  public void testHql2() throws Exception {
    Session session = sessionFactory.openSession();
    Transaction tx = null;
    try {
      tx = session.beginTransaction();
      // ------------------------------------
      String hql = null;//

      // 1，聚集函数：count(),max(),min(),avg(),sum()
      // 总数、最大、最小、平均、求和
      // hql = "select count(e) from Employee e";// 返回的结果是Long型的
      // hql = "select min(e.id) from Employee e";// 返回的结果是id的属性的类型
      // Long result = (Long) session.createQuery(hql).uniqueResult();
      // Number result = (Number) session.createQuery(hql).uniqueResult();
      // System.out.println("总记录数：" + result.intValue());

      // 2，分组：Group By ... Having
      // hql = "select e.name,count(*) from Employee e group by e.name";
      // hql = "select e.name,count(*) from Employee e group by e.name Having count(e.id)>1";
      // hql = "select e.name,count(*) from Employee e where e.id<9 group by e.name  Having count(e.id)>1";
      // hql = "select e.name,count(*) " + //
      // ---
      // "from Employee e " + //
      // "where e.id<9 " + //
      // "group by e.name  " + //
      // "Having count(e.id)>1 " + //
      // "order by count(e.id) asc";
      // ---
      // hql = "select e.name,count(*) as c " + //
      // "from Employee e " + //
      // "where e.id<9 " + //
      // "group by e.name  " + //
      // "Having count(e.id)>1 " + // 在Having子句中不能使用列别名
      // "Order by c asc";// 在Order by中可以使用列别名

      // 3，连接查询 / HQL是面向对象的查询
      // >> 内连接(inner关键字可以省略)
      // hql = "select e.id,e.name,d.name from Employee e join e.department d";
      // hql = "select e.id,e.name,d.name from Employee e inner join e.department d";
      // >> 左外连接(outer关键可以省略)
      // hql = "select e.id,e.name,d.name from Employee e left outer join e.department d";
      // >> 右外连接(outer关键可以省略)
      // hql = "select e.id,e.name,d.name from Employee e right outer join e.department d";

      // 可以使过更方便的方法
      // hql = "select e.id,e.name,e.department.name from Employee e";

      // 4，查询时使用参数
      // >> 方式一：使用'?'占位
      // hql = "from Employee e where e.id between ? And ?";
      // List<Object> list = session.createQuery(hql)//
      // .setParameter(0, 5)// 设置参数,第1个参数的索引为0。
      // .setParameter(1, 15)//
      // .list();

      // >> 方式二：使用变量名
      // hql = "from Employee e where e.id between :idMin And :idMax";
      // List<Object> list = session.createQuery(hql)//
      // .setParameter("idMin", 5)//
      // .setParameter("idMax", 15)//
      // .list();

      // >> 方式三：当参数是集合时,一定要使用ParameterList()设置参数值
      hql = "from Employee e where id in (:ids)";
      List<Object> list = session.createQuery(hql)//
          .setParameterList("ids", new Object[] { 1, 2, 3, 5, 8, 100 })//
          .list();

      // ------------------------------------
      // ------ 查询结果并 显示结果
      // List<Object> list = session.createQuery(hql).list();

      for (Object obj : list) {
        if (obj.getClass().isArray()) {
          System.out.println(Arrays.toString((Object[]) obj));
        } else {
          System.out.println(obj);
        }
      }

      tx.commit();
    } catch (RuntimeException e) {
      tx.rollback();
      throw e;
    } finally {
      session.close();
    }
  }