Native SQL查詢

第 17 章 Native SQL查詢 

你也可以使用你的資料庫的Native SQL語言來查詢資料。這對你在要使用資料庫的某些特性的時候(比如說在查詢提示或者Oracle中的 CONNECT關鍵字)，這是非常有用的。這就能夠掃清你把原來直接使用SQL/JDBC 的程式遷移到基於 Hibernate應用的道路上的障礙。

Hibernate3允許你使用手寫的sql來完成所有的create,update,delete,和load操作（包括預存程序）

17.1. 建立一個基於SQL的Query 

SQL查詢是通過SQLQuery介面來控制的，它是通過調用Session.createSQLQuery()方法來獲得

List cats = sess.createSQLQuery("select {cat.*} from cats cat")        .addEntity("cat", Cat.class);        .setMaxResults(50);        .list();

這個查詢指定了:

• SQL查詢語句，它帶一個預留位置，可以讓Hibernate使用欄位的別名.

• 查詢返回的實體，和它的SQL表的別名.

addEntity()方法將SQL表的別名和實體類聯絡起來，並且確定查詢結果集的形態。

addJoin()方法可以被用於載入其他的實體和集合的關聯，TODO:examples!

原生的SQL查詢可能返回一個簡單的標量值或者一個標量和實體的結合體。

Double max = (Double) sess.createSQLQuery("select max(cat.weight) as maxWeight from cats cat")        .addScalar("maxWeight", Hibernate.DOUBLE);        .uniqueResult();

17.2. 別名和屬性引用 

上面使用的{cat.*}標記是 "所有屬性" 的簡寫.你可以顯式地列出需要的欄位,但是你必須讓Hibernate 為每一個屬性注入欄位的別名.這些欄位的站位符是以欄位別名為前置,再加上屬性名稱.在下面的例子裡,我們從一個其他的表(cat_log) 中擷取Cat對象,而非Cat對象原本在映射中繼資料中聲明的表.注意我們甚至在where子句中也可以使用屬性別名. 對於命名查詢,{}文法並不是必需的.你可以在第 17.3 節 “命名SQL查詢”得到更多的細節.

String sql = "select cat.originalId as {cat.id}, " +    "cat.mateid as {cat.mate}, cat.sex as {cat.sex}, " +    "cat.weight*10 as {cat.weight}, cat.name as {cat.name} " +    "from cat_log cat where {cat.mate} = :catId"    List loggedCats = sess.createSQLQuery(sql)    .addEntity("cat", Cat.class)    .setLong("catId", catId)    .list();

注意:如果你明確地列出了每個屬性,你必須包含這個類和它的子類的屬性! and its subclasses!

17.3. 命名SQL查詢 

可以在映射文檔中定義查詢的名字,然後就可以象調用一個命名的HQL查詢一樣直接調用命名SQL查詢.在這種情況下,我們不 需要調用addEntity()方法.

<sql-query name="mySqlQuery">    <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 'Hiber%'</sql-query>

List people = sess.getNamedQuery("mySqlQuery")    .setMaxResults(50)    .list();

一個命名查詢可能會返回一個標量值.你必須使用<return-scalar>元素來指定欄位的別名和 Hibernate類型

<sql-query name="mySqlQuery">    <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>

<return-join>和<load-collection>元素分別用作 外串連和定義那些初始化集合的查詢

17.3.1. 使用return-property來明確地指定欄位/別名 

使用<return-property>你可以明確的告訴Hibernate使用哪些欄位,這和使用{}-文法 來讓Hibernate注入它自己的別名是相反的.

<sql-query name="mySqlQuery">    <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 name="organizationCurrentEmployments">            <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>來指定辨識器欄位

17.3.2. 使用預存程序來查詢 

Hibernate 3引入了對預存程序查詢的支援. 預存程序必須返回一個結果集,作為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 name="selectAllEmployees_SP" 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>

17.3.2.1. 使用預存程序的規則和限制 

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

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

對於Oracle有如下規則:

• 預存程序必須返回一個結果集.它通過返回SYS_REFCURSOR實現(在Oracle9或10),在Oracle裡你需要定義一個REF CURSOR 類型

• 推薦的格式是 { ? = call procName(<parameters>) } 或 { ? = call procName }(這更像是Oracle規則而不是Hibernate規則)

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

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

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

17.4. 定製SQL用來create，update和delete 

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

<class name="Person">    <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 name="Person">    <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 ISBEGIN    update PERSON    set        NAME = uname,    where        ID = uid;    return SQL%ROWCOUNT;END updatePerson;

17.5. 定製裝載SQL 

你可能需要聲明你自己的SQL(或HQL)來裝載實體

<sql-query name="person">    <return alias="p" class="Person" lock-mode="upgrade"/>    SELECT NAME AS {p.name}, ID AS {p.id} FROM PERSON WHERE ID=? FOR UPDATE</sql-query>

這隻是一個前面討論過的命名查詢聲明，你可以在類映射裡引用這個命名查詢。

<class name="Person">    <id name="id">        <generator class="increment"/>    </id>    <property name="name" not-null="true"/>    <loader query-ref="person"/></class>

這也可以用於預存程序

TODO: 未完成的例子

<sql-query name="organizationEmployments">    <load-collection alias="empcol" role="Organization.employments"/>    SELECT {empcol.*}    FROM EMPLOYMENT empcol    WHERE EMPLOYER = :id    ORDER BY STARTDATE ASC, EMPLOYEE ASC</sql-query><sql-query name="organizationCurrentEmployments">    <return alias="emp" class="Employment"/>    <synchronize table="EMPLOYMENT"/>    SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer},        STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate},        REGIONCODE as {emp.regionCode}, ID AS {emp.id}    FROM EMPLOYMENT    WHERE EMPLOYER = :id AND ENDDATE IS NULL    ORDER BY STARTDATE ASC</sql-query>