轉：Hibernate使用SQLQuery

標籤：

原文：http://hi.baidu.com/luo_qing_long/blog/item/783a15eceb75abdd2f2e21b0.html

對原生SQL查詢執行的控制是通過SQLQuery 介面進行的，通過執行Session.createSQLQuery() 擷取這個介面。下面來描述如何使用這個API進行查詢。

標量查詢（Scalar queries）
最基本的SQL查詢就是獲得一個標量（數值）的列表。

sess.createSQLQuery("SELECT * FROM CATS").list(); sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();它們都將返回一個Object數組(Object[])組成的List，數組每個元素都是CATS表的一個欄位值。Hibernate會使用ResultSetMetadata來判定返回的標量值的實際順序和類型。

如果要避免過多的使用ResultSetMetadata ,或者只是為了更加明確的指名傳回值，可以使用addScalar() 。

sess.createSQLQuery("SELECT * FROM CATS") .addScalar("ID", Hibernate.LONG) .addScalar("NAME", Hibernate.STRING) .addScalar("BIRTHDATE", Hibernate.DATE)這個查詢指定了:

SQL查詢字串

要返回的欄位和類型

它仍然會返回Object數組,但是此時不再使用ResultSetMetdata ,而是明確的將ID,NAME和 BIRTHDATE按照Long,String和Short類型從resultset中取出。同時，也指明了就算query是使用* 來查詢的，可能獲得超過列出的這三個欄位，也僅僅會返回這三個欄位。

對全部或者部分的標量值不設定類型資訊也是可以的。

sess.createSQLQuery("SELECT * FROM CATS") .addScalar("ID", Hibernate.LONG) .addScalar("NAME") .addScalar("BIRTHDATE")基本上這和前面一個查詢相同,只是此時使用ResultSetMetaData 來決定NAME和BIRTHDATE的類型，而ID的類型是明確指出的。

關於從ResultSetMetaData返回的java.sql.Types是如何映射到 Hibernate類型，是由方言(Dialect)控制的。假若某個指定的類型沒有被映射，或者不是你所預期的類型，你可以通過Dialet的registerHibernateType 調用自行定義。

16.1.2. 實體查詢(Entity queries)
上面的查詢都是返回標量值的，也就是從resultset中返回的“裸”資料。下面展示如何通過addEntity() 讓原生查詢返回實體物件。

sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class); sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);這個查詢指定：

SQL查詢字串

要返回的實體

假設Cat被映射為擁有ID,NAME和BIRTHDATE三個欄位的類，以上的兩個查詢都返回一個 List，每個元素都是一個Cat實體。

假若實體在映射時有一個many-to-one 的關聯指向另外一個實體，在查詢時必須也返回那個實體，否則會導致發生一個"column not found"的資料庫錯誤。這些附加的欄位可以使用*標註來自動返回，但我們希望還是明確指明，看下面這個具有指向Dog 的many-to-one 的例子：

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);這樣cat.getDog()就能正常運作。

16.1.3. 處理關聯和集合類(Handling associations and collections)
通過提前抓取將Dog 串連獲得，而避免初始化proxy帶來的額外開銷也是可能的。這是通過addJoin() 方法進行的，這個方法可以讓你將關聯或集合串連進來。

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

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, D_ID, D_NAME, CAT_ID FROM CATS c, DOGS d WHERE c.ID = d.CAT_ID") .addEntity("cat", Cat.class) .addJoin("cat.dogs");<p> 到此為止，我們碰到了天花板：若不對SQL查詢進行增強，這些已經是在Hibernate中使用原生SQL查詢所能做到的最大可能了。下面的問題即將出現：返回多個同樣類型的實體怎麼辦？或者預設的別名/欄位不夠又怎麼辦？ </p>
16.1.4. 返回多個實體(Returning multiple entities)
到目前為止,結果集欄位名被假定為和對應檔中指定的的欄位名是一致的。假若SQL查詢串連了多個表，同一個欄位名可能在多個表中出現多次，這就會造成問題。

下面的查詢中需要使用欄位別名注射（這個例子本身會失敗）：

sess.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,另一個是它的媽媽。但是因為它們的欄位名被映射為相同的，而且在某些資料庫中，返回的欄位別名是“c.ID”,"c.NAME"這樣的形式，而它們和在對應檔中的名字（"ID" 和"NAME"）不匹配，這就會造成失敗。

下面的形式可以解決欄位名重複：

sess.createSQLQuery("SELECT {cat.*}, {mother.*} FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID") .addEntity("cat", Cat.class) .addEntity("mother", Cat.class)這個查詢指明：

SQL查詢語句，其中包含佔位附來讓Hibernate注射欄位別名

查詢返回的實體

上面使用的{cat.*}和{mother.*}標記是作為“所有屬性”的簡寫形式出現的。當然你也可以明確地羅列出欄位名，但在這個例子裡面我們讓Hibernate來為每個屬性注射SQL欄位別名。欄位別名的預留位置是屬性名稱加上表別名的首碼。在下面的例子中，我們從另外一個表（cat_log）中通過映射中繼資料中的指定擷取Cat和它的媽媽。注意，要是我們願意，我們甚至可以在where子句中使用屬性別名。

String sql = "SELECT ID as {c.id}, NAME as {c.name}, " + "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()16.1.4.1. 別名和屬性引用(Alias and property references)
大多數情況下，都需要上面的屬性注射，但在使用更加複雜的映射，比如複合屬性、通過標識符構造繼承樹，以及集合類等等情況下，也有一些特別的別名，來允許Hibernate注射合適的別名。

下表列出了使用別名注射參數的不同可能性。注意：下面結果中的別名只是樣本，實用時每個別名需要唯一併且不同的名字。

表 16.1. 別名注射(alias injection names)

描述  文法  樣本 
簡單屬性  {[aliasname].[propertyname]  A_NAME as {item.name} 
複合屬性  {[aliasname].[componentname].[propertyname]}  CURRENCY as {item.amount.currency}, VALUE as {item.amount.value} 
實體辨別器(Discriminator of an entity)  {[aliasname].class}  DISC as {item.class} 
實體的所有屬性  {[aliasname].*}  {item.*} 
集合鍵(collection key)  {[aliasname].key}  ORGID as {coll.key} 
集合id  {[aliasname].id}  EMPID as {coll.id} 
集合元素  {[aliasname].element}  XID as {coll.element}  
集合元素的屬性  {[aliasname].element.[propertyname]}  NAME as {coll.element.name}  
集合元素的所有屬性  {[aliasname].element.*}  {coll.element.*}  
集合的所有屬性  {[aliasname].*}  {coll.*}  

16.1.5. 返回非受管實體(Returning non-managed entities)
可以對原生SQL 查詢使用ResultTransformer。這會返回不受Hibernate管理的實體。

sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS") .setResultTransformer
(Transformers.aliasToBean(CatDTO.class))
這個查詢指定：

SQL查詢字串

結果轉換器(result transformer)

上面的查詢將會返回CatDTO 的列表,它將被執行個體化並且將NAME和BIRTHDAY的值注射入對應的屬性或者欄位。

16.1.6. 處理繼承（Handling inheritance）
原生SQL查詢假若其查詢結果實體是繼承樹中的一部分，它必須包含基類和所有子類的所有屬性。

16.1.7. 參數（Parameters）
原生查詢支援位置參數和具名引數：

Query query = sess.createSQLQuery("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", "Pus%").list();16.2. 命名SQL查詢
可以在映射文檔中定義查詢的名字,然後就可以象調用一個命名的HQL查詢一樣直接調用命名SQL查詢.在這種情況下,我們addEntity() 方法.

<sql-query > <return alias="person" class="eg.Person"/> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.sex} FROM PERSON person WHERE person.NAME LIKE :namePattern </sql-query>List people = sess.getNamedQuery("persons") .setString("namePattern", namePattern) .setMaxResults(50) .list();<return-join> 和 <load-collection> 元素是用來串連關聯以及將查詢定義為預先初始化各個集合的。

<sql-query > <return alias="person" class="eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.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>一個命名查詢可能會返回一個標量值.你必須使用<return-scalar> 元素來指定欄位的別名和 Hibernate類型

<sql-query > <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>你可以把結果集映射的資訊放在外部的<resultset> 元素中，這樣就可以在多個命名查詢間，或者通過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="eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> </resultset> <sql-query name="personsWith" resultset-ref="personAddress"> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.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>另外,你可以在java代碼中直接使用hbm檔案中的結果集定義資訊。

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使用哪些欄位別名,這取代了使用{} - 文法 來讓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" 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="Employment"> <return-property name="salary"> <return-column name="VALUE"/> <return-column name="CURRENCY"/> </return-property> <return-property name="endDate" column="myEndDate"/> </return> SELECT EMPLOYEE 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> 結合{} 的注入文法. 允許使用者來選擇如何引用欄位以及屬性.

如果你映射一個辨識器(discriminator),你必須使用<return-discriminator> 來指定辨識器欄位

16.2.2. 使用預存程序來查詢
Hibernate 3引入了對預存程序查詢(stored procedure)和函數(function)的支援.以下的說明中，這二者一般都適用。 預存程序/函數必須返回一個結果集,作為Hibernate能夠使用的第一個外部參數. 下面是一個Oracle9和更高版本的預存程序例子.

CREATE OR REPLACE FUNCTION selectAllEmployments 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" class="Employment"> <return-property name="employee" column="EMPLOYEE"/> <return-property name="employer" column="EMPLOYER"/> <return-property name="startDate" column="STARTDATE"/> <return-property name="endDate" column="ENDDATE"/> <return-property name="regionCode" column="REGIONCODE"/> <return-property name="id" column="EID"/> <return-property name="salary"> <return-column name="VALUE"/> <return-column name="CURRENCY"/> </return-property> </return> { ? = call selectAllEmployments() } </sql-query>注意預存程序當前僅僅返回標量和實體.現在不支援<return-join> 和<load-collection>

16.2.2.1. 使用預存程序的規則和限制
為了在Hibernate中使用預存程序,你必須遵循一些規則.不遵循這些規則的預存程序將不可用. 如果你仍然想要使用他們, 你必須通過session.connection() 來執行他們.這些規則針對於不同的資料庫.因為資料庫 供應商有各種不同的預存程序文法和語義.

對預存程序進行的查詢無法使用setFirstResult()/setMaxResults() 進行分頁。

建議採用的調用方式是標準SQL92: { ? = call functionName(<parameters>) } 或者 { ? = call procedureName(<parameters>} .原生調用文法不被支援。

對於Oracle有如下規則:

函數必須返回一個結果集。預存程序的第一個參數必須是OUT ，它返回一個結果集。這是通過Oracle 9或10的SYS_REFCURSOR 類型來完成的。在 Oracle中你需要定義一個REF CURSOR 類型，參見Oracle的手冊。

對於Sybase或者MS SQL server有如下規則:

預存程序必須返回一個結果集。.注意這些servers可能返回多個結果集以及更新的數目.Hibernate將取出第一條結果集作為它的傳回值， 其他將被丟棄。

如果你能夠在預存程序裡設定SET NOCOUNT ON ，這可能會效率更高，但這不是必需的。

16.3. 定製SQL用來create，update和delete
Hibernate3能夠使用定製的SQL語句來執行create,update和delete操作。在Hibernate中，持久化的類和集合已經 包含了一套配置期產生的語句(insertsql, deletesql, updatesql等等)，這些映射標記 <sql-insert> , <sql-delete> , and <sql-update> 重載了 這些語句。

<class > <id name="id"> <generator class="increment"/> </id> <property 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> </class>這些SQL直接在你的資料庫裡執行，所以你可以自由的使用你喜歡的任意文法。但如果你使用資料庫特定的文法， 這當然會降低你映射的可移植性。

如果設定callable ，則能夠支援預存程序了。

<class > <id name="id"> <generator class="increment"/> </id> <property name="name" not-null="true"/> <sql-insert callable="true">{call createPerson (?, ?)}</sql-insert> <sql-delete callable="true">{? = call deletePerson (?)}</sql-delete> <sql-update callable="true">{? = call updatePerson (?, ?)}</sql-update> </class>參數的位置順序是非常重要的，他們必須和Hibernate所期待的順序相同。

你能夠通過設定日誌調試層級為org.hiberante.persister.entity ,來查看 Hibernate所期待的順序。在這個層級下， Hibernate將會列印出create,update和delete實體的靜態SQL。(如果想看到預計的順序。記得不要將定製SQL包含在對應檔裡， 因為他們會重載Hibernate產生的靜態SQL。)

在大多數情況下(最好這麼做)，預存程序需要返回插入/更新/刪除的行數，因為Hibernate對語句的成功執行有些運行時的檢查。 Hibernate常會把進行CUD操作的語句的第一個參數註冊為一個數值型輸出參數。

CREATE OR REPLACE FUNCTION updatePerson (uid IN NUMBER, uname IN VARCHAR2) RETURN NUMBER IS BEGIN update PERSON set NAME = uname, where ID = uid; return SQL%ROWCOUNT; END updatePerson;16.4. 定製裝載SQL
你可能需要聲明你自己的SQL(或HQL)來裝載實體

<sql-query > <return alias="pers" class="Person" lock-mode="upgrade"/> SELECT NAME AS {pers.name}, ID AS {pers.id} FROM PERSON WHERE ID=? FOR UPDATE </sql-query>這隻是一個前面討論過的命名查詢聲明，你可以在類映射裡引用這個命名查詢。

<class > <id name="id"> <generator class="increment"/> </id> <property name="name" not-null="true"/> <loader query-ref="person"/> </class>這也可以用於預存程序

你甚至可以定一個用於集合裝載的查詢:

<set inverse="true"> <key/> <one-to-many class="Employment"/> <loader query-ref="employments"/> </set><sql-query > <load-collection alias="emp" role="Person.employments"/> SELECT {emp.*} FROM EMPLOYMENT emp WHERE EMPLOYER = :id ORDER BY STARTDATE ASC, EMPLOYEE ASC </sql-query>你甚至還可以定義一個實體裝載器，它通過串連抓取裝載一個集合:

<sql-query > <return alias="pers" class="Person"/> <return-join alias="emp" property="pers.employments"/> SELECT NAME AS {pers

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/dknypxt/archive/2010/07/18/5744389.aspx

轉：Hibernate使用SQLQuery