教案查詢分析

1、Hibernate查詢Hibernate查詢概述：數(shù)據(jù)查詢與檢索是Hibernate中的一個(gè)亮點(diǎn)。相對(duì)其他ORM實(shí)現(xiàn)而言，Hibernate提供了靈活多樣的查詢機(jī)制。標(biāo)準(zhǔn)化對(duì)象查詢(Criteria Query)：以對(duì)象的方式進(jìn)行查詢，將查詢語(yǔ)句封裝為對(duì)象操作。優(yōu)點(diǎn)：可讀性好，符合Java 程序員的編碼習(xí)慣。缺點(diǎn)：不夠成熟，不支持投影（projection）或統(tǒng)計(jì)函數(shù)（aggregation）Hibernate語(yǔ)言查詢（Hibernate Query Language，HQL）：它是完全面向?qū)ο蟮牟樵冋Z(yǔ)句，查詢功能非常強(qiáng)大，具備多態(tài)、關(guān)聯(lián)等特性 。Hibernate官方推薦使用HQL進(jìn)行查詢。

2、Native SQL Queries（原生SQL查詢）：直接使用標(biāo)準(zhǔn)SQL語(yǔ)言或跟特定數(shù)據(jù)庫(kù)相關(guān)的SQL進(jìn)行查詢。Hibernate查詢語(yǔ)言（HQL）HQL用面向?qū)ο蟮姆绞缴蒘QL以類和屬性來(lái)代替表和數(shù)據(jù)列支持多態(tài)支持各種關(guān)聯(lián)減少了SQL的冗余HQL支持所有的關(guān)系數(shù)據(jù)庫(kù)操作連接（joins,包括Inner/outer/full joins），笛卡爾積(cartesian products)投影（projection）聚合（Aggregation，max, avg）和分組（group）排序（Ordering）子查詢（Subqueries）SQL函數(shù)（SQL function calls）例子：

3、 Hibernate查詢語(yǔ)言（HQL）簡(jiǎn)單例子：查詢用戶名以“J”開(kāi)頭的所有用戶。Query query = session.createQuery(from User user where like J%); List users = query.list();復(fù)雜例子：從User和Group中查找屬于“admin”組的所有用戶。Query query = session.createQuery(“from User user where =admin”);如果用傳統(tǒng)的SQL則查詢語(yǔ)句如下：select user.userId as user

4、Id, as name, user.groupId as groupId, user.idCardId as idCardId from TBL_USER user, TBL_GROUP group where (group.groupName=admin and user.groupId=group.groupId)HQL查詢步驟獲取Hibernate的Session對(duì)象編寫(xiě)HQL語(yǔ)句session.createQuery(HQL)創(chuàng)建Query對(duì)象query.setXXX設(shè)置查詢參數(shù)query.list()得到查詢結(jié)果（持久化實(shí)體集）HQL最終轉(zhuǎn)換成SQL到數(shù)據(jù)庫(kù)執(zhí)行在

5、hibernate.cfg.xml中設(shè)置show_sql屬性為true輸出對(duì)應(yīng)的sql：trueHQL查詢參數(shù)？session.createQuery(“from User where id=?”). setInteger(0,1):參數(shù)名session.createQuery(“from User where id=:userid”). setInteger(“userid”,1)分頁(yè)控制setFirstResult(int firstResult);setMaxResults(int maxResults);HQL子句from子句from as 對(duì)象 where .結(jié)果為持久化類的對(duì)象集合

6、關(guān)聯(lián)和連接隱式連接：from User u where u.group.id=2；返回User對(duì)象的集合顯式連接：from User u inner join u.group g with u.group.id=g.id where g.id=2返回以User對(duì)象和Group 對(duì)象組成的數(shù)據(jù)為元素的集合select子句選擇指定屬性或某個(gè)實(shí)體select from User as uselect u.group from User as u返回Listselect new list(u.id,) from User as u;返回java對(duì)象seleect new My

7、Class(u.id,) from User as u返回Mapselect new map(u.id,) from User as u聚合函數(shù)同SQL的聚合函數(shù)where子句篩選結(jié)果，一般使用隱式連接查詢from User as u where u.group.region.city=hz支持SQL的條件表達(dá)式（P485）子查詢HQL子查詢只可以出現(xiàn)在select子句或where子句中命名查詢?cè)谟成湮募信渲迷卦诔绦蛑型ㄟ^(guò)session.getNamedQuery(“queryName”)調(diào)用。在hibernate.cfg.xml的hibernate-mapping

8、元素中添加 在代碼中調(diào)用命名查詢Query query = session.getNamedQuery(getUserByName); query.setParameter(name, name); query.list();標(biāo)準(zhǔn)化對(duì)象查詢(Criteria Query)標(biāo)準(zhǔn)化查詢的三個(gè)基礎(chǔ)類Criteria：代表一次查詢Criterion：代表一個(gè)查詢條件Restrictions：產(chǎn)生查詢條件的工具類更具面向?qū)ο筇厣獵riteria查詢步驟獲取Hibernate的session對(duì)象session.createCriteria創(chuàng)建Criteria對(duì)象使用Restrictions的靜態(tài)方法創(chuàng)建C

9、riterion查詢條件向Criteria查詢中添加Criterion查詢條件criteria.list()方法返回結(jié)果集例子：標(biāo)準(zhǔn)化對(duì)象查詢(Criteria Query)簡(jiǎn)單例子：查詢用戶名以“J”開(kāi)頭的所有用戶。Criteria criteria = session.createCriteria(User.class); criteria.add(Restrictions.ilike(“name, “J%); List users = criteria.list();原生SQL語(yǔ)句查詢可以利用數(shù)據(jù)庫(kù)本身特性原JDBC業(yè)務(wù)遷移 邏輯復(fù)雜的SQL調(diào)用存儲(chǔ)過(guò)程 通過(guò)SQLQuery接口完成 執(zhí)

10、行SQL語(yǔ)句查詢的步驟獲取Hibernate的session對(duì)象寫(xiě)SQL語(yǔ)句session.createSQLQuery創(chuàng)建查詢對(duì)象query.setXXX為SQL參數(shù)賦值query.addScalar()將查詢結(jié)果與標(biāo)量值相關(guān)聯(lián)；query.addEntity()將查詢結(jié)果與實(shí)體對(duì)象相關(guān)聯(lián)query.list()返回實(shí)際結(jié)果集標(biāo)量查詢：返回Object或Object為元素的Listsession.createSQLQuery(“select * from user_table”).addScalar(“name”,StandardBasicTypes.STRING). addScalar(“

11、id”,StandardBasicTypes.INTEGER).list()實(shí)體查詢：返回指定實(shí)體對(duì)象為元素的Listsession.createSQLQuery(“select * from user_table where age:age and gender=:gender”).addEntity(User.class).setInteger(“age”,18).setString(“gender”,”female”).list()轉(zhuǎn)換成ValueObject通過(guò)Transformer對(duì)象將結(jié)果集的記錄轉(zhuǎn)換成為javabean對(duì)象（ValueObject）session.createSQLQuery(“select ca.id, as teacherName, as courseName, as roomName ca.time as courseTime,ca.num from course_arrange ca,course c,teacher t,room r where ca.course_id=c.id and ca.teacher_id=t.id and ca.room_id=r.id”).setResultTransformer(Transformers.aliasToBean(CourseArrangeBean