hibernate3學習筆記(九) Query及HQL

示例代碼如下：

1.package com.hb3.pack_01;
2.
3.import java.util.Iterator;
4.import java.util.List;
5.
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;
11.
12.import com.hb3.pack_01.model.User;
13.import com.hb3.pack_01.model.UserBak;
14.
15.public class BusinessService {
16.
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();
59.
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"?>
2.
3.<!DOCTYPE hibernate-mapping PUBLIC
4.    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
5.    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
6.
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;
2.
3.public class UserBak {
4.
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是官方推薦的查詢方式。