|
|
Posted on 2005-11-24 10:27 Terry的Blog 閱讀(702) 評(píng)論(0) 編輯 收藏 所屬分類: java語(yǔ)言 、 轉(zhuǎn)載
（一）HQL
HQL：Hibernate Qusery Language，如果你已經(jīng)熟悉它，就會(huì)發(fā)現(xiàn)它跟SQL非常相像。不過(guò) 你不要被表面的假象迷惑，HQL是面向?qū)ο蟮模∣O，用生命的眼光看待每一個(gè)對(duì)象，他們是如此 鮮活）。如果你對(duì)JAVA和SQL語(yǔ)句有一定了解的話，那么HQL對(duì)你簡(jiǎn)直易如反掌，你完全可以利用在公車上的時(shí)間掌握它。
以下從幾個(gè)方面進(jìn)行慢慢深入：
1。大小些敏感
大家知道SQL-92 Query是對(duì)大小寫不敏感的，但是在HQL（前面提到它是OO的）中對(duì)對(duì)象類的名稱和屬性確實(shí)大小寫敏感的（符合java編程語(yǔ)法）。
HQL 子句本身大小寫無(wú)關(guān)，但是其中出現(xiàn)的類名和屬性名必須注意大小寫區(qū)分
如：sElect cat.name from Cat as cat和select cat.name from Cat as cat是一樣的
但是：
sElect cat.name from CAT as cat和select cat.name from Cat as cat確實(shí)不一樣的。
2。from語(yǔ)句
最簡(jiǎn)單的：
from eg.Cat
它只是簡(jiǎn)單的返回所有eg.Cat的實(shí)例,通常我們此時(shí)會(huì)為eg.Cat其個(gè)別名，因?yàn)樵趒uery的其余部分可能會(huì)用到(參看上邊關(guān)于大小寫敏感時(shí)的例子情形)，如：
from eg.Cat as cat 這里as可以省略。
上邊只是單表查詢，多表的情況如下寫法：
from eg.Cat, eg.Dog
from eg.Cat as cat, eg.Dog as dog
3。join相關(guān)
(inner) join
left (outer) join
right (outer) join
full join
HQL同樣對(duì)SQL中的這些特性支持
下面插播一個(gè)小話題，關(guān)于上邊的那些特性，我一直都沒怎么用，今天既然說(shuō)到這里，就想把上邊的幾個(gè)特性的用法說(shuō)一下，也算對(duì)自己的一個(gè)補(bǔ)充：
假設(shè)有兩個(gè)表：部門、員工，下面列舉一些數(shù)據(jù)：
員工(Employee)：
ID Name DepNo
001 Jplateau 01
002 Jony 01
003 Camel 02
部門(Department)：
ID Name
01 研發(fā)部
02 營(yíng)銷部
在Hibernate中我們操縱的都是對(duì)象，所以我們操縱的是部門類和員工類
1).(inner) join
select employee.ID as id1,employee.Name as name1,
department.ID as id2,department.Name as name2 from Employee as employee
join Department as department on employee.DepNo=department.ID (注意到條件語(yǔ)句我用on 沒有用where)
那么執(zhí)行結(jié)果是什么呢？
id1 name1 id2 name2
++++++++++++++++++++++++++++++++++++++
001 Jplateau 01 研發(fā)部
002 Jony 01 研發(fā)部
2).left (outer) join
select employee.ID as id1,employee.Name as name1,department.ID as id2,department.Name
as name2 from Employee as employee left join Department as department on employee.DepNo=
department.ID
那么執(zhí)行結(jié)果又該是什么呢？
id1 name1 id2 name2
++++++++++++++++++++++++++++++++++++++
001 Jplateau 01 研發(fā)部
002 Jony 01 研發(fā)部
003 Camel null null
{就是說(shuō)此時(shí)我要已第一個(gè)表的記錄多少為準(zhǔn)，第二個(gè)表中沒有相應(yīng)紀(jì)錄的時(shí)候填充null}
3). right (outer) join
select employee.ID as id1,employee.Name as name1,department.ID as id2,department.Name
as name2 from Employee as employee right join Department as department on employee.DepNo=
department.ID
那么執(zhí)行結(jié)果又該是什么呢？
id1 name1 id2 name2
++++++++++++++++++++++++++++++++++++++
001 Jplateau 01 研發(fā)部
002 Jony 01 研發(fā)部
null null 02 營(yíng)銷部
{就是說(shuō)此時(shí)我要已第二個(gè)表的記錄多少為準(zhǔn)，第一個(gè)表中沒有相應(yīng)紀(jì)錄的時(shí)候填充null}
4。select語(yǔ)句
就是要確定你要從查詢中返回哪些對(duì)象或者哪些對(duì)象的屬性。寫幾個(gè)例子吧：
select employee form Employee as employee
select employee form Employee as employee where employee.Name like 'J%'
select employee.Name form Employee as employee where employee.Name like 'J%'
select employee.ID as id1,employee.Name as name1,department.ID as id2,department.Name
as name2 from Employee as employee right join Department as department on employee.DepNo=
department.ID
select elements(employee.Name) from Employee as employee
（不明白elements到底是做什么用的？望給于說(shuō)明）
等等
5。數(shù)學(xué)函數(shù)
JDO目前好像還不支持此類特性。
avg(...), sum(...), min(...), max(...)
count(*)
count(...), count(distinct ...), count(all...)
其用法和SQL基本相同
select distinct employee.name from Employee as employee
select count(distinct employee.name),count(employee) from Employee as employee
6。polymorphism (暫時(shí)不知道如何解釋？)
from com.test.Animal as animal
不光得到所有Animal得實(shí)例，而且可以得到所有Animal的子類（如果我們定義了一個(gè)子類Cat）
一個(gè)比較極端的例子
from java.lang.Object as o
可以得到所有持久類的實(shí)例
7。where語(yǔ)句
定義查詢語(yǔ)句的條件，舉幾個(gè)例子吧：
from Employee as employee where employee.Name='Jplateau'
from Employee as employee where employee.Name like 'J%'
from Employee as employee where employee.Name like '%u'
在where語(yǔ)句中“=”不光可以比較對(duì)象的屬性，也可以比較對(duì)象，如：
select animal from com.test.Animal as animal where animal.name=dog
8。表達(dá)式
在SQL語(yǔ)句中大部分的表達(dá)式在HQL中都可以使用：
mathematical operators +, -, *, /
binary comparison operators =, >=, <=, <>, !=, like
logical operations and, or, not
string concatenation ||
SQL scalar functions like upper() and lower()
Parentheses ( ) indicate grouping
in, between, is null
JDBC IN parameters ?
named parameters :name, :start_date,  1 （這種應(yīng)該是另一種"?"的變通解決方法）
SQL literals 'foo', 69, '1970-01-01 10:00:01.0'
Java public static final constants eg.Color.TABBY
其他不必解釋了，在這里我只想對(duì)查詢中的參數(shù)問題說(shuō)明一下：
大家知道在SQL中進(jìn)行傳遞參數(shù)進(jìn)行查詢的時(shí)候，我們通常用PreparedStatement，在語(yǔ)句中寫一大堆的“？”，
在hql中也可以用這種方法，如：
List mates = sess.find(
"select employee.name from Employee as employee " +
"where employee.Name=? ",
name,
Hibernate.STRING
);
(說(shuō)明：上面利用Session里的find方法，在hibernate的api Session中重載了很多find方法，它可以滿足你多種形式的查詢)
上邊是一個(gè)參數(shù)的情形，這種情況下緊接著引入?yún)?shù)和定義參數(shù)的類型，當(dāng)為多個(gè)參數(shù)，調(diào)用另一個(gè)find方法，它的后兩個(gè)
參數(shù)都是數(shù)組的形式。
還有另外一種方法來(lái)解決上邊的問題，JDO也有這樣的方法，不過(guò)和hibernate的表現(xiàn)形式上有差別，但他們兩個(gè)骨子里卻是
一樣的，如：
Query q = sess.createQuery("select employee.name from Employee as employee where employee.Name=:name");
q.setString("name", "Jplateau");
//當(dāng)有多個(gè)參數(shù)的時(shí)候在此逐一定義
Iterator employees = q.iterate();
9。order 語(yǔ)句
和sql語(yǔ)句沒什么差別，如：
select employee.name from Employee as employee where employee.Name like 'J%' order by employee.ID desc (或者asc)
10。group by 語(yǔ)句
同樣和sql語(yǔ)句沒什么差別，如：
select employee.name,employee.DepNo from Employee as employee group by employee.DepNo
select foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) from eg.Foo foo group by foo.id
{Note: You may use the elements and indices constructs inside a select clause, even on databases with no subselects.}
誰(shuí)幫我解釋一下上邊兩句，謝過(guò)！
11。子查詢
hibernate同樣支持子查詢，寫幾個(gè)例子：
from eg.Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from eg.DomesticCat cat )
（二）條件查詢
Session osession = ownerSession.getSession();
Criteria criteria = osession.createCriteria(Owner.class);
criteria.add(Expression.eq("age", new Integer(100)));
criteria.setFirstResult(2); //從返回結(jié)果的第二條記錄開始的5條記錄
criteria.setMaxResults(5);
List lc=criteria.list();
System.out.println("條件查詢");
System.out.println(lc.size());
（三）原生sql語(yǔ)句查詢
Query query=osession.createSQLQuery("select {owner.*} from Owner as owner", "owner", Owner.class);
query.setMaxResults(4);
List l=query.list();
System.out.println("原生sql語(yǔ)句查詢");
System.out.println(l.get(0));
|