使用SQLQuery在Hibernate中使用sql語句

1、session.createSQLQuery.轉(zhuǎn)載對原生SQL查詢執(zhí)行的控制是通過SQLQuery接口進行的，通過執(zhí)行Session.createSQLQuery()獲取這個接口。下面來描述如何使用這個API進行查詢。1.標量查詢（Scalar queries）最基本的SQL查詢就是獲得一個標量（數(shù)值）的列表。sess.createSQLQuery("SELECT * FROM CATS").list();sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();它們都將返回一個

2、Object數(shù)組(Object)組成的List，數(shù)組每個元素都是CATS表的一個字段值。Hibernate會使用ResultSetMetadata來判定返回的標量值的實際順序和類型。如果要避免過多的使用ResultSetMetadata,或者只是為了更加明確的指名返回值，可以使用addScalar()。sess.createSQLQuery("SELECT * FROM CATS").addScalar("ID", Hibernate.LONG).addScalar("NAME", Hibernate.STRING).addScala

3、r("BIRTHDATE", Hibernate.DATE)這個查詢指定了:· SQL查詢字符串· 要返回的字段和類型它仍然會返回Object數(shù)組,但是此時不再使用ResultSetMetdata,而是明確的將ID,NAME和BIRTHDATE按照Long,String和Short類型從resultset中取出。同時，也指明了就算query是使用*來查詢的，可能獲得超過列出的這三個字段，也僅僅會返回這三個字段。對全部或者部分的標量值不設(shè)置類型信息也是可以的。sess.createSQLQuery("SELECT * FROM CATS"

4、;) .addScalar("ID", Hibernate.LONG) .addScalar("NAME") .addScalar("BIRTHDATE")基本上這和前面一個查詢相同,只是此時使用ResultSetMetaData來決定NAME和BIRTHDATE的類型，而ID的類型是明確指出的。關(guān)于從ResultSetMetaData返回的java.sql.Types是如何映射到Hibernate類型，是由方言(Dialect)控制的。假若某個指定的類型沒有被映射，或者不是你所預(yù)期的類型，你可以通過Dialet的registerHi

5、bernateType調(diào)用自行定義。2.實體查詢(Entity queries)上面的查詢都是返回標量值的，也就是從resultset中返回的“裸”數(shù)據(jù)。下面展示如何通過addEntity()讓原生查詢返回實體對象。sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class); sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);這個查詢指定：· SQL查詢字符串

6、· 要返回的實體假設(shè)Cat被映射為擁有ID,NAME和BIRTHDATE三個字段的類，以上的兩個查詢都返回一個List，每個元素都是一個Cat實體。假若實體在映射時有一個many-to-one的關(guān)聯(lián)指向另外一個實體，在查詢時必須也返回那個實體，否則會導致發(fā)生一個"column not found"的數(shù)據(jù)庫錯誤。這些附加的字段可以使用*標注來自動返回，但我們希望還是明確指明，看下面這個具有指向Dog的many-to-one的例子：sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CAT

7、S").addEntity(Cat.class);這樣cat.getDog()就能正常運作。 下面的暫時沒需求16.1.3. 處理關(guān)聯(lián)和集合類(Handling associations and collections)通過提前抓取將Dog連接獲得，而避免初始化proxy帶來的額外開銷也是可能的。這是通過addJoin()方法進行的，這個方法可以讓你將關(guān)聯(lián)或集合連接進來。sess.createSQLQuery("SELECT c.ID, NAME, BIRTHDATE, DOG_ID, D_ID, D_NAME FROM CATS c, DOGS d WHERE

8、c.DOG_ID = d.D_ID").addEntity("cat", Cat.class).addJoin("cat.dog");上面這個例子中，返回的Cat對象，其dog屬性被完全初始化了，不再需要數(shù)據(jù)庫的額外操作。注意，我們加了一個別名("cat")，以便指明join的目標屬性路徑。通過同樣的提前連接也可以作用于集合類，例如，假若Cat有一個指向Dog的一對多關(guān)聯(lián)。sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, D_ID, D_NAME, CAT_ID FR

9、OM CATS c, DOGS d WHERE c.ID = d.CAT_ID") .addEntity("cat", Cat.class) .addJoin("cat.dogs");到此為止，我們碰到了天花板：若不對SQL查詢進行增強，這些已經(jīng)是在Hibernate中使用原生SQL查詢所能做到的最大可能了。下面的問題即將出現(xiàn)：返回多個同樣類型的實體怎么辦？或者默認的別名/字段不夠又怎么辦？16.1.4. 返回多個實體(Returning multiple entities)到目前為止,結(jié)果集字段名被假定為和映射文件中指定的的字段名是一致的。假

10、若SQL查詢連接了多個表，同一個字段名可能在多個表中出現(xiàn)多次，這就會造成問題。下面的查詢中需要使用字段別名注射（這個例子本身會失?。簊ess.createSQLQuery("SELECT c.*, m.* FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID") .addEntity("cat", Cat.class) .addEntity("mother", Cat.class)這個查詢的本意是希望每行返回兩個Cat實例，一個是cat,另一個是它的媽媽。但 是因為它們的字段名被映射為相同的，而且

11、在某些數(shù)據(jù)庫中，返回的字段別名是“c.ID”,"c.NAME"這樣的形式，而它們和在映射文件中的名字 （"ID"和"NAME"）不匹配，這就會造成失敗。下面的形式可以解決字段名重復(fù)：sess.createSQLQuery("SELECT cat.*, mother.* FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID") .addEntity("cat", Cat.class) .addEntity("mother", Cat.clas

12、s)這個查詢指明：· SQL查詢語句，其中包含占位附來讓Hibernate注射字段別名· 查詢返回的實體上面使用的cat.*和mother.*標記是作為“所有屬性”的簡寫形式 出現(xiàn)的。當然你也可以明確地羅列出字段名，但在這個例子里面我們讓Hibernate來為每個屬性注射SQL字段別名。字段別名的占位符是屬性名加上表別 名的前綴。在下面的例子中，我們從另外一個表（cat_log）中通過映射元數(shù)據(jù)中的指定獲取Cat和它的媽媽。注意，要是我們愿意，我們甚至可以在 where子句中使用屬性別名。String sql = "SELECT ID as c.id, NAME

13、as , " + "BIRTHDATE as c.birthDate, MOTHER_ID as c.mother, mother.* " + "FROM CAT_LOG c, CAT_LOG m WHERE c.mother = c.ID" List loggedCats = sess.createSQLQuery(sql) .addEntity("cat", Cat.class) .addEntity("mother", Cat.class).list(). 別名和屬性引用

14、(Alias and property references)大多數(shù)情況下，都需要上面的屬性注射，但在使用更加復(fù)雜的映射，比如復(fù)合屬性、通過標識符構(gòu)造繼承樹，以及集合類等等情況下，也有一些特別的別名，來允許Hibernate注射合適的別名。下表列出了使用別名注射參數(shù)的不同可能性。注意：下面結(jié)果中的別名只是示例，實用時每個別名需要唯一并且不同的名字。表 16.1. 別名注射(alias injection names)描述語法示例簡單屬性pertynameA_NAME as 復(fù)合屬性pertynam

15、eCURRENCY as item.amount.currency, VALUE as item.amount.value實體辨別器(Discriminator of an entity)aliasname.classDISC as item.class實體的所有屬性aliasname.*item.*集合鍵(collection key)aliasname.keyORGID as coll.key集合idaliasname.idEMPID as coll.id集合元素aliasname.elementXID as coll.element 集合元素的屬性aliasname.eleme

16、pertynameNAME as  集合元素的所有屬性aliasname.element.*coll.element.* 集合的所有屬性aliasname.*coll.* 16.1.5. 返回非受管實體(Returning non-managed entities)可以對原生sql 查詢使用ResultTransformer。這會返回不受Hibernate管理的實體。sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS") .setResultT

17、ransformer(Transformers.aliasToBean(CatDTO.class)這個查詢指定：· SQL查詢字符串· 結(jié)果轉(zhuǎn)換器(result transformer)上面的查詢將會返回CatDTO的列表,它將被實例化并且將NAME和BIRTHDAY的值注射入對應(yīng)的屬性或者字段。16.1.6. 處理繼承（Handling inheritance）原生SQL查詢假若其查詢結(jié)果實體是繼承樹中的一部分，它必須包含基類和所有子類的所有屬性。16.1.7. 參數(shù)（Parameters）原生查詢支持位置參數(shù)和命名參數(shù)：Query query = sess.create

18、SQLQuery("SELECT * FROM CATS WHERE NAME like ?").addEntity(Cat.class); List pusList = query.setString(0, "Pus%").list(); query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like :name").addEntity(Cat.class); List pusList = query.setString("name", "

19、Pus%").list();16.2. 命名SQL查詢可以在映射文檔中定義查詢的名字,然后就可以象調(diào)用一個命名的HQL查詢一樣直接調(diào)用命名SQL查詢.在這種情況下,我們addEntity()方法.<sql-query > <return alias="person" class="eg.Person"/> SELECT person.NAME AS , person.AGE AS person.age, person.SEX AS person.sex FROM PERSON person WHER

20、E person.NAME LIKE :namePattern </sql-query>List people = sess.getNamedQuery("persons") .setString("namePattern", namePattern) .setMaxResults(50) .list();<return-join>和 <load-collection> 元素是用來連接關(guān)聯(lián)以及將查詢定義為預(yù)先初始化各個集合的。<sql-query > <return alias=&

21、quot;person" class="eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> SELECT person.NAME AS , person.AGE AS person.age, person.SEX AS person.sex, adddress.STREET AS address.street, adddress.CITY AS address.city, adddre

22、ss.STATE AS address.state, adddress.ZIP AS address.zip FROM PERSON person JOIN ADDRESS adddress ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query>一個命名查詢可能會返回一個標量值.你必須使用<return-scalar>元素來指定字段的別名和 Hibernate類型<sql-query &g

23、t; <return-scalar column="name" type="string"/> <return-scalar column="age" type="long"/> SELECT p.NAME AS name, p.AGE AS age, FROM PERSON p WHERE p.NAME LIKE 'Hiber%' </sql-query>你可以把結(jié)果集映射的信息放在外部的<resultset>元素中，這樣就可以在多個命名查詢間，或者

24、通過setResultSetMapping()API來訪問。(此處原文即存疑。原文為：You can externalize the resultset mapping informations in a <resultset>element to either reuse them accross several named queries or through the setResultSetMapping() API.)<resultset > <return alias="person" class=&qu

25、ot;eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> </resultset> <sql-query name="personsWith" resultset-ref="personAddress"> SELECT person.NAME AS , person.AGE AS person.age, person.SEX AS perso

26、n.sex, adddress.STREET AS address.street, adddress.CITY AS address.city, adddress.STATE AS address.state, adddress.ZIP AS address.zip FROM PERSON person JOIN ADDRESS adddress ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query>另外,

27、你可以在java代碼中直接使用hbm文件中的結(jié)果集定義信息。List cats = sess.createSQLQuery( "select cat.*, kitten.* from cats cat, cats kitten where kitten.mother = cat.id" ) .setResultSetMapping("catAndKitten") .list();16.2.1. 使用return-property來明確地指定字段/別名使用<return-property>你可以明確的告訴Hibernate使用哪些字段別名,這取

28、代了使用-語法 來讓Hibernate注入它自己的別名.<sql-query > <return alias="person" class="eg.Person"> <return-property name="name" column="myName"/> <return-property name="age" column="myAge"/> <return-property name="sex"

29、column="mySex"/> </return> SELECT person.NAME AS myName, person.AGE AS myAge, person.SEX AS mySex, FROM PERSON person WHERE person.NAME LIKE :name </sql-query><return-property>也可用于多個字段,它解決了使用-語法不能細粒度控制多個字段的限制 <sql-query > <return alias="emp" class=&q

30、uot;Employment"> <return-property name="salary"> <return-column name="VALUE"/> <return-column name="CURRENCY"/> </return-property> <return-property name="endDate" column="myEndDate"/> </return> SELECT EMPLO

31、YEE AS emp.employee, EMPLOYER AS emp.employer, STARTDATE AS emp.startDate, ENDDATE AS emp.endDate, REGIONCODE as emp.regionCode, EID AS emp.id, VALUE, CURRENCY FROM EMPLOYMENT WHERE EMPLOYER = :id AND ENDDATE IS NULL ORDER BY STARTDATE ASC </sql-query>注意在這個例子中,我們使用了<return-property>結(jié)合的注入

32、語法. 允許用戶來選擇如何引用字段以及屬性.如果你映射一個識別器(discriminator),你必須使用<return-discriminator> 來指定識別器字段16.2.2. 使用存儲過程來查詢Hibernate 3引入了對存儲過程查詢(stored procedure)和函數(shù)(function)的支持.以下的說明中，這二者一般都適用。 存儲過程/函數(shù)必須返回一個結(jié)果集,作為Hibernate能夠使用的第一個外部參數(shù). 下面是一個Oracle9和更高版本的存儲過程例子.CREATE OR REPLACE FUNCTION selectAllEmployments

33、RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR; BEGIN OPEN st_cursor FOR SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE, REGIONCODE, EID, VALUE, CURRENCY FROM EMPLOYMENT; RETURN st_cursor; END;在Hibernate里要要使用這個查詢,你需要通過命名查詢來映射它.<sql-query callable="true"> <return alias="emp&qu

34、ot; class="Employment"> <return-property name="employee" column="EMPLOYEE"/> <return-property name="employer" column="EMPLOYER"/> <return-property name="startDate" column="STARTDATE"/> <return-property name

35、="endDate" column="ENDDATE"/> <return-property name="regionCode" column="REGIONCODE"/> <return-property name="id" column="EID"/> <return-property name="salary"> <return-column name="VALUE"/> &l

36、t;return-column name="CURRENCY"/> </return-property> </return> ? = call selectAllEmployments() </sql-query>注意存儲過程當前僅僅返回標量和實體.現(xiàn)在不支持<return-join>和<load-collection>. 使用存儲過程的規(guī)則和限制為了在Hibernate中使用存儲過程,你必須遵循一些規(guī)則.不遵循這些規(guī)則的存儲過程將不可用.如果你仍然想要使用他們, 你必須通過session.c

37、onnection()來執(zhí)行他們.這些規(guī)則針對于不同的數(shù)據(jù)庫.因為數(shù)據(jù)庫 提供商有各種不同的存儲過程語法和語義.對存儲過程進行的查詢無法使用setFirstResult()/setMaxResults()進行分頁。建議采用的調(diào)用方式是標準SQL92:  ? = call functionName(<parameters>)  或者  ? = call procedureName(<parameters>.原生調(diào)用語法不被支持。對于Oracle有如下規(guī)則:· 函數(shù)必須返回一個結(jié)果集。存儲過程的第一個參數(shù)必須是OUT，它返回一個結(jié)果集

38、。這是通過Oracle 9或10的SYS_REFCURSOR類型來完成的。在Oracle中你需要定義一個REF CURSOR類型，參見Oracle的手冊。對于Sybase或者MS SQL server有如下規(guī)則:· 存儲過程必須返回一個結(jié)果集。.注意這些servers可能返回多個結(jié)果集以及更新的數(shù)目.Hibernate將取出第一條結(jié)果集作為它的返回值， 其他將被丟棄。· 如果你能夠在存儲過程里設(shè)定SET NOCOUNT ON，這可能會效率更高，但這不是必需的。16.3. 定制SQL用來create，update和deleteHibernate3能夠使用定制的SQL語句來執(zhí)行

39、create,update和delete操作。在Hibernate中，持久化的類和集合已經(jīng) 包含了一套配置期產(chǎn)生的語句(insertsql, deletesql, updatesql等等)，這些映射標記 <sql-insert>, <sql-delete>, and <sql-update>重載了 這些語句。<class > <id name="id"> <generator class="increment"/> </id> <pro

40、perty name="name" not-null="true"/> <sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert> <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update> <sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete> <

41、/class>這些SQL直接在你的數(shù)據(jù)庫里執(zhí)行，所以你可以自由的使用你喜歡的任意語法。但如果你使用數(shù)據(jù)庫特定的語法， 這當然會降低你映射的可移植性。如果設(shè)定callable，則能夠支持存儲過程了。<class > <id name="id"> <generator class="increment"/> </id> <property name="name" not-null="true"/> <sql-insert callable=&quo

42、t;true">call createPerson (?, ?)</sql-insert> <sql-delete callable="true">? = call deletePerson (?)</sql-delete> <sql-update callable="true">? = call updatePerson (?, ?)</sql-update> </class>參數(shù)的位置順序是非常重要的，他們必須和Hibernate所期待的順序相同。你能夠通過設(shè)定日志調(diào)試級別為org.hiberante.persister.entity, 來查看Hibernate所期待的順序。在這個級別下， Hibernate將會打印出create,update和delete實體的靜態(tài)SQL。(如果想看到預(yù)計的順序。記得不要將定制SQL包含在映射文件 里， 因為他們會重載Hibernate生成的靜態(tài)SQL。)在大多數(shù)情況下(最好這么做)，存儲過程需要返回插入/更新/刪除的行數(shù)，因為Hibernate對語句的成功執(zhí)行有些運行時的檢查。 Hibernate常會把進行CUD操作的語句的第一個參數(shù)注冊為一