Hibernate中的HQL基本语法

  HQL是个中立的语言,即它跟任何数据库都没关系，你如果使用HQL的话通过数据库方言都会翻译成相应的数据库的SQL语句,包括[排序,子查询,调用SQL中的函数].
  HQL中关键字是不区分大小写的，但是属性和类型是区分大小写的。
   1>.简单属性查询
      1>).单一属性查询,返回结果集属性列表,元素类型和实体类中相应的属性类型一致。如：
          session = HibernateUtils.getSession();
          session.beginTransaction();
           
          //返回结果集属性列表，元素类型和实体类中相应的属性类型一致
           List students = session.createQuery("select name from Student").list();
           for (Iterator iter=students.iterator(); iter.hasNext();) {
                  String name = (String)iter.next();
                  System.out.println(name);
           }
      session.getTransaction().commit();
      2>).多个属性查询,返回的集合元素是对象数组，数组元素的类型和对
          应的属性在实体类中的类型一致，数组的长度取决于select中属性的个数。如：
          List students = session.createQuery("select id, name from Student").list();
         for (Iterator iter=students.iterator(); iter.hasNext();) {
                Object[] obj = (Object[])iter.next();
                System.out.println(obj[0] + "," + obj[1]);
          }
      3>).如果认为返回数组不够对象化，可以采用HQL动态实例化Student对象，
          但是Strudent类中必须提供相应的构造函数。如：
          List students = session.createQuery("select new Student(id, name) from Student").list();
          for (Iterator iter=students.iterator(); iter.hasNext();) {
                Student student = (Student)iter.next();
                System.out.println(student.getId() + "," + student.getName());
          }
    2>.实体对象查询
       1>).返回Student对象的集合,可以忽略select,表可以使用别名,别名可以用as也可以不用。如：
          List students = session.createQuery("from Student s").list();
          //List students = session.createQuery("from Student as s").list();
         for (Iterator iter=students.iterator(); iter.hasNext();) {
                 Student student = (Student)iter.next();
                 System.out.println(student.getName());
           }
       2>).返回Student对象的集合,使用select查询实体对象，必须采用别名。如：
          List students = session.createQuery("select s from Student as s").list();
          for (Iterator iter=students.iterator(); iter.hasNext();) {
                Student student = (Student)iter.next();
                 System.out.println(student.getName());
          }
       3>).不支持select * from .....这样的查询语句。如：下面的语句是错误的
           List students = session.createQuery("select * from Student").list();
       4>).list和iterator的区别?
           我们知道,session.createQuery()得到个Query,而从Hibernate API中我们可
           以发现Query有两个方法list()和iterator()用于返回结果集。它们的区别在
           于：
           *list每次都会发出sql语句，list会向缓存中放入数据，而不利用缓存中的数据
           *iterator在默认情况下利用缓存数据,但如果缓存中不存在数据有可能出现N+1问题
        5>).N+1问题
            在默认情况下,使用query.iterator()查询，有可能出现N+1问题
            1: 首先发出一条查询对象id列表的sql语句
            N: 根据id列表到缓存中查询,如果缓存中不存在与之匹配的数据,那么会根据
               id发出相应的sql语句
            如：
            Iterator iter = session.createQuery("from Student").iterate();
        while(iter.hasNext()) {
            Student student = (Student)iter.next();
            System.out.println(student.getName());
        }
    3>.条件查询
       1>).可以采用拼字符串的方式传递参数。如：
               List students = session.createQuery("select s.id, s.name from Student s where s.name like '%1%'").list();
               for (Iterator iter=students.iterator(); iter.hasNext();) {
                      Object[] obj = (Object[])iter.next();
                       System.out.println(obj[0] + "," + obj[1]);
                }
       2>).可以使用？方式传递参数,参数的索引从0开始;传递的参数值如果是字符型的，不用
           单引号引起来;可以使用方法链编程。如：
            List students = session.createQuery("select s.id, s.name from Student s where s.name like ?")
                                                .setParameter(0, "%1%")
                                                .list();
           for (Iterator iter=students.iterator(); iter.hasNext();) {
                       Object[] obj = (Object[])iter.next();
                       System.out.println(obj[0] + "," + obj[1]);
           }
       3>).可以使用 ：参数名称 的方式传递参数值。如：
              List students = session.createQuery("select s.id, s.name from Student s where s.name like :myname")
                                                   .setParameter("myname", "%1%")
                                                   .list();
              for (Iterator iter=students.iterator(); iter.hasNext();) {
                         Object[] obj = (Object[])iter.next();
                         System.out.println(obj[0] + "," + obj[1]);
             }
       4>).如果传递多个参数，可以使用setParameterList方法。如：
           List students = session.createQuery("select s.id, s.name from Student s where s.id in(:myids)")
                                                .setParameterList("myids", new Object[]{1, 2, 3, 4, 5})
                                               .list();
           for (Iterator iter=students.iterator(); iter.hasNext();) {
                   Object[] obj = (Object[])iter.next();
                   System.out.println(obj[0] + "," + obj[1]);
           }
       5>).在HQL中可以使用数据库的函数,像mysql中的date_format。如：
             List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=?")
                                                  .setParameter(0, "2008-02")
                                                  .list();
              for (Iterator iter=students.iterator(); iter.hasNext();) {
                     Object[] obj = (Object[])iter.next();
                      System.out.println(obj[0] + "," + obj[1]);
               }
       6>).查询某个日期段的数据。如：
           SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
           //查询2008-01-10到2008-02-15创建的学生
           List students = session.createQuery("select s.id, s.name from Student s where s.createTime between ? and ?")
                                               .setParameter(0, sdf.parse("2008-01-10 00:00:00"))
                                               .setParameter(1, sdf.parse("2008-02-15 23:59:59"))
                                               .list();
              for (Iterator iter=students.iterator(); iter.hasNext();) {
                     Object[] obj = (Object[])iter.next();
                      System.out.println(obj[0] + "," + obj[1]);
                }

    4>.Hibernate也支持直接使用sql查询。如：
          List students = session.createSQLQuery("select * from t_student").list();
          for (Iterator iter=students.iterator(); iter.hasNext();) {
                Object[] obj = (Object[])iter.next();
                System.out.println(obj[0] + "," + obj[1]);
          }
    5>.外置命名查询,主要完成hql语句和程序的解耦合.
       1>).在映射文件中采用<query>标签来定义HQL语句。如：
            <query name="searchStudents">
                <![CDATA[
                      SELECT s FROM Student s where s.id<?
                 ]]>
            </query>
       2>).在程序中采用session.getNamedQuery()方法得到HQL查询串。如：
              List students = session.getNamedQuery("searchStudents")
                                                   .setParameter(0, 10)
                                                   .list();
              for (Iterator iter=students.iterator(); iter.hasNext();) {
                     Student student = (Student)iter.next();
                     System.out.println(student.getName());
               }
     6>.查询过滤器
       1>).在影射文件中定义过滤器参数。如：
        <filter-def name="filtertest">
                <filter-param name="myid" type="integer"/>
        </filter-def>   
       2>).在类的影射中使用这些参数。如：
         <filter name="filtertest" condition="id &lt; :myid"/><!--&lt;转义字符"<"-->
       3>).在程序中启用过滤器。如：
         session.enableFilter("filtertest")
                     .setParameter("myid", 10);   
         List students = session.createQuery("from Student").list();//会在该session中所有的查询后自动加上过滤器中的条件
         for (Iterator iter=students.iterator(); iter.hasNext();) {
               Student student = (Student)iter.next();
               System.out.println(student.getName());
         }
       参见:Student.hbm.xml,FilterQueryTest.java
     7>.分页查询
        1>).setFirstResult()从0开始
        2>).setMaxResults每页显示多少条数据。如：
              List students = session.createQuery("from Student")
                                                   .setFirstResult(1)
                                                   .setMaxResults(2)
                                                   .list();
              for (Iterator iter=students.iterator(); iter.hasNext();) {
                     Student student = (Student)iter.next();
                     System.out.println(student.getName());
             }
     8>.对象导航查询
        Hibernate支持对象的，因此可以用对象导航查询。如：
        Student类总有Classes类型的属性classes，而Classes类有name属性。我们通过classes的name来查询student:
        List students = session.createQuery("select s.name from Student s where s.classes.name like '%1%'").list();
        for (Iterator iter=students.iterator(); iter.hasNext();) {
             String name = (String)iter.next();
             System.out.println(name);
       }
     9>.连接查询
        1>).内连接:只是把两边都有的记录查出来,可以省略inner，并且只要影射文件中已经设好了表之间的关系，无须用on表示查询条件。如：
           //List students = session.createQuery("select c.name, s.name from Student s join s.classes c").list();
           List students = session.createQuery("select c.name, s.name from Student s inner join s.classes c").list();
           for (Iterator iter=students.iterator(); iter.hasNext();) {
                Object[] obj = (Object[])iter.next();
                System.out.println(obj[0] + "," + obj[1]);
           }
        2>).外左连接:把左边表的所有记录都显示出来。如：
             List students = session.createQuery("select c.name, s.name from Classes c left join c.students s").list();
             for (Iterator iter=students.iterator(); iter.hasNext();) {
                  Object[] obj = (Object[])iter.next();
                  System.out.println(obj[0] + "," + obj[1]);
             }
        3>).外右连接:把右边表的所有记录都显示出来。如：
             List students = session.createQuery("select c.name, s.name from Classes c right join c.students s").list();
              for (Iterator iter=students.iterator(); iter.hasNext();) {
                   Object[] obj = (Object[])iter.next();
                   System.out.println(obj[0] + "," + obj[1]);
             }
     10>.统计查询
        1>).count。如：
            Long count = (Long)session.createQuery("select count(*) from Student").uniqueResult();//返回的是单条记录
            System.out.println(count);
        2>).group by。如：
            List students =session.createQuery("select c.name, count(s) from Student s join s.classes c " +
                    "group by c.name order by c.name").list();
           for (Iterator iter=students.iterator(); iter.hasNext();) {
                Object[] obj = (Object[])iter.next();
                System.out.println(obj[0] + ", " + obj[1]);
           }
     11>.DML风格的操作(尽量少用,因为缓存不同步)
        update和delete只会更新数据库中的数据,而缓存中不会改，此时如果取得该数据还是缓存中的,而不会是更新后的，因此缓
        存中的数据就成了脏数据了。如果批量更新的话，还会给内存带来很大压力。如：
        session.createQuery("update Student s set s.name=? where s.id < ?")
                    .setParameter(0, "李四")
                    .setParameter(1, 5)
                    .executeUpdate();