pa.equipmentaccount.id=?pa.equipmentaccount.idpa.equipmentaccount.idHQL
例如對(duì)于TUser類
1.實(shí)體查詢
String hql = " from User";
執(zhí)行這條語(yǔ)句會(huì)
返回User以及User子類的
紀(jì)錄。
注: 如果 TUser 類具有外鍵, 查詢結(jié)果不會(huì)報(bào)錯(cuò),但結(jié)果中的外鍵為空值,訪問(wèn)就報(bào)空指針錯(cuò)誤!
解決方法: select 別名.屬性 from 類 as 別名. 沒(méi)有別名.屬性仍然報(bào)錯(cuò)! //此方法不一定能解決,但不會(huì)錯(cuò).
hql = "from java.lang.Object"
會(huì)返回?cái)?shù)據(jù)庫(kù)中所有庫(kù)表的紀(jì)錄。
where 語(yǔ)句，as可以省略
hql = "from User as user where user.name='yyy'"; //user.name為類的屬性
hql = "from User user where user.name='yyy'";
where子句中，我們可以通過(guò)比較運(yùn)算符設(shè)定條件，如：
=, <>, >, <, >=, <=, between XX and XX, not between, in (xx,xx), not in, is, like %XX% 等。
2.屬性查詢
(1)List list = session.createQuery("select user.name, user.age from User user").list();
還可以在HQL中動(dòng)態(tài)構(gòu)造對(duì)象實(shí)例的方法，將數(shù)據(jù)封裝。
(2)List list = session.createQuery("select new User(user.name, user.age) from TUser as user").list();
Iterator it = list.iterator();
while(it.hasNext() ) {
User user = (User)it.next();
System.out.println(user.getName());
}
但是要注意這里的User對(duì)象只是對(duì)name和age屬性的封裝，其他狀態(tài)均未賦值，所以不能用它來(lái)進(jìn)行更新操作。
也可以在HQL的Select子句中使用統(tǒng)計(jì)函數(shù)
"select count(*) ,min(user.age) from User as user"
也可以使用distinct關(guān)鍵字來(lái)刪除重復(fù)紀(jì)錄。
select distinct user.name from User as user;
3.實(shí)體的更新與刪除
hibernate 3中，提供了更靈活的方式(bulk(集體) delete/update)
更新：
Query query = session.createQuery("update User set age=18 where id=1"); //age==User.age
query.executeUpdate();
刪除：
session.createQuery("delete User where age>=18");
query.executeUpdate();
注:不支持聯(lián)表更新,即此處的User對(duì)象不能外鍵關(guān)聯(lián).因?yàn)楦虏僮鞑辉试S多表聯(lián)接更新,只能更新一張表(SQL規(guī)定).
如:update TbPartaccount pa set inoutflag=9 where pa.equipmentaccount.id=:id instr(pa.equipmentaccount.id,'Z')=1
pa.equipmentaccount.inoutcode報(bào)錯(cuò),
pa.equipmentaccount.id還是
TbPartaccount 的字段(外鍵關(guān)聯(lián))
Hibernate 不能像我們想像的那樣轉(zhuǎn)換成以下方式,所以還是需要手工寫(xiě)子查詢:
UPDATE TB_PARTACCOUNT pa set PA.INOUTFLAG=9 where pa.equipmentaccount.id=? and pa.eaccount_code=(
SELECT ea.id from TB_EQUIPMENTACCOUNT ea
where ea.id=pa.EACCOUNT_CODE and INSTR(ea.inoutcode, 'Z')=1)
而是轉(zhuǎn)成以下形式并報(bào)異常:沒(méi)有找到SET關(guān)鍵字:
UPDATE TB_PARTACCOUNT pa set PA.INOUTFLAG=9 where
pa.equipmentaccount.id=? and INSTR(pa.eaccount_code, 'Z')=1
4.分組與排序
Order by子句：
from User user order by user.name, user.age desc
Group by子句和Having子句
"select count(user), user.age from User user group by user.age having count(user)>10"
5.參數(shù)邦定
通過(guò)順序占位符?來(lái)填充參數(shù)：
1)hibernate 2 中通過(guò)session.find方法來(lái)填充
session.find("from User user where user.name=?", "Erica", Hibernate.STRING);
多個(gè)參數(shù)的情況：
Object[] args = new Object[] {"Erica", new Integer(20)};
Type[] types = new Type{Hibernate.STRING, Hibernate.INTEGER};
session.find("from TUser user where user.name=? and user.age=?", args, types);
2)通過(guò)Query接口進(jìn)行參數(shù)填充：
Query query = session.createQuery("from User user where user.name=? and user.age>?");
query.setString(0,"Erica");
query.setInteger(1, 20);
通過(guò)引用占位符來(lái)填充參數(shù)：
String hql = "from User where name=:name";
Query query = session.createQuery(hql);
query.setParameter("name","Erica");
甚至可以將查詢條件封裝為一個(gè)JavaBean
class UserQuery {
private String name;
private Integer age;
//getter and setter
}
String hql = "from TUser where name=:name and age=:age";
Query query = session.createQuery(hql);
UserQuery uq = new UserQuery();
uq.setName("Erica");
uq.setAge(new Integer(20));
query.setProperties(uq); //會(huì)調(diào)用里面的getter?
query.iterate();
6.聯(lián)合查詢
(org.hibernate.LazyInitializationException: could not initialize proxy - no Session 異常,可能是HQL語(yǔ)句沒(méi)有進(jìn)行聯(lián)表查詢,產(chǎn)生訪問(wèn)的屬性不存在.)
User表: id, name
Addresses表: user_id, addresses
(1)自然聯(lián)接(內(nèi)聯(lián)接): inner join [fetch]
1.HQL: from User u inner join fetch u.addresses
SQL: select * from User表 u inner join Addresses a on u.id=a.user_id //u.id=a.user_id在XML中已配置,所以可以省略.
fetch: Addresses對(duì)象讀出后,立即填充到User對(duì)象對(duì)應(yīng)的外鍵(集合)中.
2.HQL: from User u inner join u.addresses
不加fetch,則返回的結(jié)果集中的每一條記錄是一個(gè)Object數(shù)組,數(shù)組包括User和Addresses兩個(gè)對(duì)象, 并且User對(duì)象.addresses集合已被Addresses對(duì)象自動(dòng)填充.
(2)左連接: left outer join [fetch]
同(1), 返回的結(jié)果不同而已.
(3)右連接: right out join
fetch無(wú)效,因?yàn)閁ser對(duì)象可能為NULL,無(wú)法進(jìn)行填充,但返回結(jié)果里的記錄是數(shù)組.
(4)笛卡爾交集: full join (使用很少)
同(3), 返回結(jié)果不同而已.
(5)笛卡爾集: 排列組合：form User, Address
聯(lián)合查詢?nèi)缬胁欢? 詳見(jiàn)SQL聯(lián)合語(yǔ)句:
SQL中的left outer join,inner join,right outer join用法: http://m.tkk7.com/algz/articles/228219.html
SQL中的各種JOIN（inner join,full outer join,left join,right join,cross join ）:http://m.tkk7.com/algz/articles/228218.html
sql的left join 命令詳解 : http://m.tkk7.com/algz/articles/228220.html
posted on 2008-04-10 13:33
紫蝶∏飛揚(yáng)↗ 閱讀(12233)
評(píng)論(1) 編輯 收藏 所屬分類:
Hibernate 、
重點(diǎn)