HQL查询及语法

    HQL:Hibernate Query Language
    HQL是完全面向对象的查询语言，因此可以支持继承和多态等特征。
    HQL查询依赖于Query类，每个Query实例对应一个查询对象，使用HQL查询按
    如下步骤进行：
    (1)获取Hibernate Session对象；
    (2)编写HQL语句；
    (3)以HQL语句作为参数，调用Session的createQuery方法创建查询对象；
    (4)如果HQL语句包含参数，调用Query的setXxx方法为参数赋值；
    (5)调用Query对象的list等方法遍历查询结果。
    查询示例：

       1. public class HqlQuery   
       2.   {   
       3.       public static void main(String[] args) throws Exception {   
       4.          HqlQuery mgr = new HqlQuery();   
       5.          //调用查询方法   
       6.          mgr.findPersons();   
       7.          //调用第二个查询方法   
       8.          mgr.findPersonByHappenDate();   
       9.          HibernateUtil.sessionFactory.close();   
      10.      }   
      11.      //第一个查询方法   
      12.       private void findPersons() {   
      13.          //获得Hibernate Session   
      14.          Session sess = HibernateUtil.currentSession();   
      15.          //开始事务   
      16.          Transaction tx = sess.beginTransaction();   
      17.          //以HQL语句创建Query对象   
      18.          //执行setString方法为HQL语句的参数赋值   
      19.          //Query调用list方法访问查询的全部实例   
      20.          List p1 = sess.createQuery("from Person p where o.myEvents.title = :   
      21.              eventTitle").setString("eventTitle", "很普通事情").list();   
      22.          //遍历查询的全部结果   
      23.          for (Iterator pit = p1.iterator(); pit.haxNext(); )   
      24.           {   
      25.              Person p = (Person)pit.next();   
      26.              System.out.println(p.getName());   
      27.          }   
      28.          //提交事务   
      29.          tx.commit();   
      30.          HibernateUtil.closeSession();   
      31.      }   
      32.      //第二个查询方法   
      33.       private void findPersonByHappenDate() throws Exception {   
      34.          Session sess = HibernateUtil.currentSession();   
      35.          Transaction tx = sess.beginTransaction();   
      36.          //解析出Date对象   
      37.          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");   
      38.          Date start = sdf.parse("2007-11-27");   
      39.          System.out.println("系统开始通过日期查找人" + start);   
      40.          //通过Session的createQuery方法创建Query对象   
      41.          //设置参数   
      42.          //返回结果集   
      43.          List pl = sess.createQuery(   
      44.              "from Person p where p.myEvents.happenDate between :firstDate   
      45.              and :endDate")   
      46.                          .setDate("firstDate", start)   
      47.                          .setDate("endDate", new Date())   
      48.                          .list();   
      49.          //遍历结果集   
      50.          for (Iterator pit = pl.iterator(); pit.hasNext(); )   
      51.           {   
      52.              Person p = (Person)pit.next();   
      53.              System.out.println(p.getName());   
      54.          }   
      55.          tx.commit();   
      56.          HibernateUtil.closeSession();   
      57.      }   
      58. }  



    $下面介绍HQL语句的语法
    1.from子句

       1. from Person  

    表明从Person持久化类中选出全部的实例。
    推荐：from Person as p

    2.select子句

       1. select p.name from Person as p   
       2. select p.name.firstName from Person as p   
       3. select new list(p.name, p.address) from Person as p   
       4. select new ClassTest(p.name, p.address) from Person as p (有前提)   
       5. select p.name as personName from Person as p   
       6. select new map(p.name as personName) from Person as p (与new map()结合更普遍)  



    3.聚集函数

       1. avg,count,max,min,sum   
       2. select count(*) from Person   
       3. select max(p.age) from Person as p   
       4. select p.name || "" || p.address from Person as p  



    4.多态查询

       1. from Person as p   
       2. from java.lang.Object o   
       3. from Named as n  



    5.where子句

       1. from Person where name like "tom%"   
       2. from Person as p where p.name like "tom%"   
       3. from Cat cat where cat.mate.name like "kit%"   
       4.      select * from cat_table as table1 cat_table as table2 where table1.mate =   
       5.      table2.id and table1.name like "kit%"   
       6. from Foo foo where foo.bar.baz.customer.address.city like "fuzhou%"   
       7. from Cat cat, Cat rival where cat.mate = rival.mate   
       8. select cat, mate   
       9. from Cat cat, Cat mate   
      10. where cat.mate = mate   
      11. from Cat as cat where cat.id = 123   
      12. from Cat as cat where cat.mate.id = 69   
      13. from Person as person   
      14. where person.id.country = 'AU'   
      15.      and person.id.medicareNumber = 123456   
      16. from Account as account   
      17. where account.owner.id.country = 'AU'   
      18.      and account.owner.id.medicareNumber = 123456   
      19. from Cat cat where cat.class = DomesticCat   
      20. from Account as a where a.person.name.firstName like "dd%" // 正确   
      21. from Account as a where a.person.name like "dd%" // 错误  



    6.表达式

       1. from DomesticCat cat where cat.name between 'A' and 'B'   
       2. from DomesticCat cat where cat.name in ('Foo', 'Bar', 'Baz')   
       3. from DomesticCat cat where cat.name not between 'A' and 'B'   
       4. from DomesticCat cat where cat.name not in ('Foo', 'Bar', 'Baz')   
       5. from DomesticCat cat where cat.name is null   
       6. from Person as p where p.address is not null   
       7. <property name="hibernate.query.substitutions">true 1, false 0</property>   
       8. from Cat cat where cat.alive = true   
       9. from Cat cat where cat.kittens.size > 0   
      10. from Cat cat where size(cat.kittens) > 0   
      11. from Calendar cal where maxelement(cal.holidays) > current date   
      12. from Order order where maxindex(order.items) > 100   
      13. from Order order where minelement(order.items) > 10000   
      14. //操作集合元素   
      15. select mother from Cat as mother, Cat as kit   
      16. where kit in elements(foo.kittens)   
      17. //p的name属性等于集合中某个元素的name属性   
      18. select p from NameList list, Person p   
      19. where p.name = some elements(list.names)   
      20. //操作集合元素   
      21. from Cat cat where exists elements(cat.kittens)   
      22. from Player p where 3 > all elements(p.scores)   
      23. from Show show where 'fizard' in indices(show.acts)   
      24. //items是有序集合属性，items[0]代表第一个元素   
      25. from Order order where order.items[0].id = 1234   
      26. //holidays是map集合属性，holidays[national day]是代表其中第一个元素   
      27. select person from Person person, Calendar calendar   
      28. where calendar.holidays['national day'] = person.birthDay   
      29.      and person.nationality.calendar = calendar   
      30. //下面同时使用list集合和map集合属性   
      31. select item from Item item, Order order   
      32. where order.items[order.deliveredItemIndices[0]] = item and order.id = 11   
      33. select item from Item item, Order order   
      34. where order.items[maxindex(order.items)] = item and order.id = 11   
      35.  
      36. select item from Item item, Order order   
      37. where order.items[size(order.items) - 1] = item   
      38.  
      39. select cust   
      40. from Product prod,   
      41.      Store store   
      42.      inner join store.customers cust   
      43. where prod.name = 'widget'   
      44.      and store.location.name in ['Melbourne', 'Sydney']   
      45.      and prod = all elements(cust.currentOrder.lineItems)   
      46.  
      47. SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order   
      48. FROM customers cust,   
      49.      stores store,   
      50.      locations loc,   
      51.      store_customers sc,   
      52.      product prod   
      53. WHERE prod.name = 'widget'   
      54.      AND store.loc_id = loc.id   
      55.      AND loc.name IN ('Melbourne', 'Sydney')   
      56.      AND sc.store_id = store.id   
      57.      AND sc.cust_id = cust.id   
      58.      AND prod.id = ALL(   
      59.          SELECT item.prod_id   
      60.          FROM line_items item, orders o   
      61.          WHERE item.order_id = o.id   
      62.              AND cust.current_order = o.id   
      63.      )  



    7.order by子句

       1. from Person as p   
       2. order by p.name, p.age   
       3. from Person as p   
       4. order by p.name asc, p.age desc  



    8.group by子句

       1. select cat.color, sum(cat.weight), count(cat)   
       2. from Cat cat   
       3. group by cat.color   
       4. //select后出现的id处出现在group by之后，而name属性则出现在聚集函数中   
       5. select foo.id, avg(name), max(name)   
       6. from Foo foo join foo.names name   
       7. group by foo.id   
       8.  
       9. select cat.color, sum(cat.weight), count(cat)   
      10. from Cat cat   
      11. group by cat.color   
      12. having cat.color in (eg.Color.TABBY, eg.Color.BLACK)   
      13.  
      14. select cat   
      15. from Cat cat   
      16. join cat.kittens kitten   
      17. group by cat   
      18. having avg(kitten.weight) > 100   
      19. order by count(kitten) asc, sum(kitten.weight) desc  



    9.子查询

       1. from Cat as fatcat   
       2. where fatcat.weight > (select avg(cat.weight) from DomesticCat cat)   
       3.  
       4. from Cat as cat   
       5. where not (cat.name, cat.color) in (   
       6.      select cat.name, cat.color from DomesticCat cat   
       7. )  



    10.fetch关键字

       1. from Person as p join p.scores   
       2. from Document fetch all properties order by name   
       3. from Document doc fetch all properties where lower(doc.name) like '%cat%'  


    11.命名查询

       1. <!--定义命名查询-->   
       2. <query name="myNamedQuery">   
       3.      <!--此处确定命名查询的HQL语句-->   
       4.      from Person as p where p.age > ?   
       5. </query>  



    调用命名查询的示例代码如下：

       1.   private void findByNamedQuery() throws Exception {   
       2.      Session session = HibernateUtil.currentSession();   
       3.      Transaction tx = session.beginTransaction();   
       4.      System.out.println("执行命名查询");   
       5.      //调用命名查询   
       6.      List pl = sess.getNamedQuery("myNamedQuery")   
       7.                                      //为参数赋值   
       8.                                      .setInteger(0, 20)   
       9.                                      //返回全部结果   
      10.                                      .list();   
      11.      //遍历结果集   
      12.      for (Integer pit = pl.iterator(); pit.hasNext(); )   
      13.       {   
      14.          Person p = (Person)pit.next();   
      15.          System.out.println(p.getName());   
      16.      }   
      17.      tx.commit();   
      18.      HibernateUtil.closeSession();   
      19. }