Native SQL查詢

weixin_34262482發表於2013-06-09
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 IS
BEGIN

    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>

相關文章