HQL的常用语句

2019独角兽企业重金招聘Python工程师标准>>>

Session session = HibernateSessionFactory.getSession();
  /##
   # 通过HQL语句查询表中的sex字段，distinct是不允许有重复值
   # ---给表中增加SEX字段后必须在xml映射文件以及实体类种增加属性
   # SQL="select distinct sex from admin";
   #/
    /#String hql = "select distinct a.sex from Admin as a ";
    Query query = session.createQuery(hql);
    List list = query.list();
    Iterator iter = list.iterator();
    while(iter.hasNext()){
    //它不是一个对象，所以只许迭代打印出集合的数据
      System.out.println(iter.next());
    }#/
    /##
     # Count查询全部的记录数据
     # SQL="select count(#) from admin";
     #
     #/
    /##
     #  avg查询全部年龄的平均数,注意只能求int型的参数
     # SQL="select avg(age) from admin";
     # HQL="select avg(a.age) from Admin as a";
     #/
    /#String hql = "select count(#) from Admin as a";
    Query query = session.createQuery(hql);
    List list = query.list();
    Iterator iter = list.iterator();
    while(iter.hasNext()){
      System.out.println(iter.next());
    }#/
    /##
     # 带where 条件的HQL
     # 更新删除 HQL
     # update Admin set name="李四" where aid="6"
     # delete Admin where name="李四";
     #  1.HQL="from Admin a where(a.age/10=3)";
     #  2.HQL="from Admin a where(a.age>20) and (a.aname='赵六')"
     #  3.is null或者is not null判断数据是否为空
     #   HQL="from Admin a where a.name is not null";
     #  4.between and 测试字段是否在指定的范围内
     #   HQL="from Admin a where a.aid between 5 and 9";
     #  5.in或者not in来测试字段是否在指定的集合中
     #   HQL="from Admin a where a.name in('张三','李四')"
     #  6.like或者not like进行模糊条件查询
     #   HQL="from Admin a where a.name like'张%'";
     #  7.order by 进行排序
     #   HQL="from Admin a order by a.age";
     #  8.desc 反排序
     #   HQL = "from Admin a order by a.age desc";
     #  9.同时指定两个以上的排列方式，先按age反排序，如age相同再按name顺序排列
     #   HQL="from Admin a order by a.age desc,a.aname";
     #  10.按指定的字段依据相同的内容分组,取出性别为男或者女的平均年龄
     #   HQL="select a.sex,avg(a.age) from Admin a group by a.sex";
     #  11.只将平均大于20的数据分组显示出来
     #   HQL="select a.sex,avg(a.age) from Admin a group by a.sex" having avg(a.age)>20;
     #
     #/
    String hql = "from Admin as a where a.aname=?";
    Query query = session.createQuery(hql);
    query.setString(0, "钱七");
    List list = query.list();
    Iterator iter = list.iterator();
    while(iter.hasNext()){
      Admin admin = (Admin) iter.next();
      System.out.println(admin.getAname());
    }
  /##
   # 分页操作
   #setFirstResult从第几行开始取数据
   #setMaxResults取多少行数据
   #/
    String sql = "from Admin as a";
    Query q = session.createQuery(sql);
    q.setFirstResult(0);
    q.setMaxResults(3);
    List l = q.list();
    Iterator i = l.iterator();
    while(i.hasNext()){
      Admin a = (Admin)i.next();
      System.out.println(a.getAname()+"--"+a.getSex()+"--"+a.getAge());
    }
  }

转载于:https://my.oschina.net/u/1012289/blog/118146