hibernate3学习笔记(九)｜Query及HQL

 接着前面的例子继续：http://blog.csdn.net/kunshan_shenbin/archive/2008/09/02/2864883.aspx

示例代码如下：

1. package com.hb3.pack_01;
3. import java.util.Iterator;
4. import java.util.List;
6. import org.hibernate.Query;
7. import org.hibernate.Session;
8. import org.hibernate.SessionFactory;
9. import org.hibernate.Transaction;
10. import org.hibernate.cfg.Configuration;
12. import com.hb3.pack_01.model.User;
13. import com.hb3.pack_01.model.UserBak;
15. public class BusinessService {
17.     public static void main(String[] args) {
18.         
19.         Configuration config = new Configuration().configure();
20.         SessionFactory sessionFactory = config.buildSessionFactory();      
21.         Session session = sessionFactory.openSession();
22.         
23.         Query query = session.createQuery("from com.hb3.pack_01.model.User user order by user.age desc, user.name");//[== from User]
24.         printUserInfo(query.list());
25.         
26.         query = session.createQuery("select new com.hb3.pack_01.model.UserBak(user.name, user.age) from User as user");
27.         Iterator<?> iterator = query.list().iterator();
28.         System.out.println("class_name /t/t name/age");
29.         while (iterator.hasNext()) {
30.             UserBak userbak = (UserBak) iterator.next();
31.             System.out.println(userbak.getClz() + " /t " + userbak.getName() + "/" + userbak.getAge());
32.         }
33.         
34.         query = session.createQuery("select upper(user.name) from User as user where (user.age/2 >= ?) and (user.age is not null)"); 
35.         query.setInteger(0, 13);
36.         List<?> names = query.list();
37.         iterator =  names.iterator();
38.         while(iterator.hasNext()) {
39.             System.out.println(iterator.next());
40.         }
41.         
42.         query = session.createQuery("select user.name, user.age from User as user where user.age > :minAge"); 
43.         query.setInteger("minAge", 25);
44.         names = query.list();
45.         iterator =  names.iterator();
46.         while(iterator.hasNext()) {
47.             Object[] obj = (Object[]) iterator.next();
48.             System.out.println(obj[0] + "/t" + obj[1]);
49.         }
50.         
51.         query = session.getNamedQuery("com.hb3.pack_01.model.User.QueryUser"); 
52.         query.setInteger("minAge", 25);
53.         printUserInfo(query.list());
54.         
55.         Transaction tx= session.beginTransaction();
56.         query = session.createQuery("update User set age=24 where name='chenyan'");
57.         query.executeUpdate();
58.         tx.commit();
60.         tx= session.beginTransaction();
61.         query = session.createQuery("delete User where name='yaobin'");
62.         query.executeUpdate();
63.         tx.commit();
64.         
65.         session.close();
66.         sessionFactory.close();
67.     }
68.     
69.     public static void printUserInfo(List<?> users){
70.         
71.         Iterator<?> iterator = users.iterator();
72.         System.out.println("id /t name/age");
73.         while (iterator.hasNext()) {
74.             User user = (User) iterator.next();
75.             System.out.println(user.getId() + " /t " + user.getName() + "/" + user.getAge());
76.         }
77.     }
78. }

修改User.hbm.xml文件;

1. <?xml version="1.0" encoding="UTF-8"?>
3. <!DOCTYPE hibernate-mapping PUBLIC
4.     "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
5.     "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
7. <hibernate-mapping>
8.     <class name="com.hb3.pack_01.model.User" table="user" 
9.         dynamic-insert="true" 
10.         dynamic-update="true"   
11.     >
12.         <id name="id" column="id" type="java.lang.Integer" unsaved-value="null">
13.             <generator class="native" />
14.         </id>
15.         
16.         <property name="name" column="name" type="java.lang.String" />
17.         
18.         <property name="age" column="age" type="java.lang.Integer" />
19.     </class>
20.     
21.     <query name="com.hb3.pack_01.model.User.QueryUser">
22.         <![CDATA[             
23.             from User as user where user.age >  :minAge        
24.         ]]>
25.     </query>
26.     
27. </hibernate-mapping>

新建UserBak类

1. package com.hb3.pack_01.model;
3. public class UserBak {
5.     private String clz = "UserBak";
6.     private String name;
7.     private Integer age;
8.     
9.     public UserBak() {
10.     }
11.     
12.     public UserBak(String name, Integer age) {
13.         this.name = name;
14.         this.age = age;
15.     }
16.     
17.     public String getName() {
18.         return name;
19.     }
20.     public void setName(String name) {
21.         this.name = name;
22.     }
23.     public Integer getAge() {
24.         return age;
25.     }
26.     public void setAge(Integer age) {
27.         this.age = age;
28.     }
29.     public String getClz() {
30.         return clz;
31.     }
32.     public void setClz(String clz) {
33.         this.clz = clz;
34.     }
35. }

运行结果：

16:53:02,015  WARN ConfigurationFactory:127 - No configuration found. Configuring ehcache from ehcache-failsafe.xml  found in the classpath: jar:file:/D:/Java/MyEclipse%206.0/workspace/hb3demo/ehcache-1.2.3.jar!/ehcache-failsafe.xml
16:53:02,437  WARN EhCacheProvider:93 - Could not find configuration [org.hibernate.cache.UpdateTimestampsCache]; using defaults.
16:53:02,468  WARN EhCacheProvider:93 - Could not find configuration [org.hibernate.cache.StandardQueryCache]; using defaults.
Hibernate: select user0_.id as id0_, user0_.name as name0_, user0_.age as age0_ from user user0_ order by user0_.age desc, user0_.name
id   name/age
1   shenbin/28
2   chenyan/26
9   shenyi/23
10   yangye/23
11   chenyong/22
13   chendong/null
12   yaobin/null
Hibernate: select user0_.name as col_0_0_, user0_.age as col_1_0_ from user user0_
class_name    name/age
UserBak   shenbin/28
UserBak   chenyan/26
UserBak   shenyi/23
UserBak   yangye/23
UserBak   chenyong/22
UserBak   yaobin/null
UserBak   chendong/null
Hibernate: select upper(user0_.name) as col_0_0_ from user user0_ where user0_.age/2>=? and (user0_.age is not null)
SHENBIN
CHENYAN
Hibernate: select user0_.name as col_0_0_, user0_.age as col_1_0_ from user user0_ where user0_.age>?
shenbin 28
chenyan 26
Hibernate: select user0_.id as id0_, user0_.name as name0_, user0_.age as age0_ from user user0_ where user0_.age>?
id   name/age
1   shenbin/28
2   chenyan/26
Hibernate: update user set age=24 where name='chenyan'
Hibernate: delete from user where name='yaobin'

 

注意点：

1.在Hibernate3中，可以直接使用HQL指定更新或刪除。

2.使用HQL可以自定义返回的类型。（如:UserBak）

3.使用HQL可以结合各种查询条件，使用where子句限定查询条件，除了 = 运算之外，还有 >、>=、<、<=、!= 或 <>等比较运算：

Query query = session.createQuery("from User user where user.age between 20 and 30");

Query query = session.createQuery("from User user where user.name in('caterpillar', 'momor')");

Query query = session.createQuery("from User user where user.name like 'cater%'");

Query query = session.createQuery("select avg(user.age) from User as user");

Query query = session.createQuery("select count(*) from User as user");

也可以配合GROUP BY子句，假设在数据库表中追加一个Sex字段。

Query query = session.createQuery("select user.sex, avg(user.age) from User user group by user.sex");

运行结果可能是：

+-------------------------------+ 
|    sex       |    avg(age)    | 
+-------------------------------+ 
|    male      |    30          | 
+-------------------------------+ 
|    female    |    25          | 
+-------------------------------+

可以結合having子句,例如只将平均年龄大于20的资料分组显示出来：
Query query = session.createQuery("select user.sex, avg(user.age) from User user group by user.sex having avg(user.age) > 20");

使用HQL是官方推荐的查询方式。