17.2. 命名 SQL 查询

优质

小牛编辑

131浏览

2023-12-01

可以在映射文档中定义查询的名字,然后就可以象调用一个命名的 HQL 查询一样直接调用命名 SQL 查询.在这种情况下，我们不需要调用 addEntity() 方法。


<sql-query name="persons">
    <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 name="personsWith">
    <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},
           address.STREET AS {address.street},
           address.CITY AS {address.city},
           address.STATE AS {address.state},
           address.ZIP AS {address.zip}
    FROM PERSON person
    JOIN ADDRESS address
        ON person.ID = address.PERSON_ID AND address.TYPE='MAILING'
    WHERE person.NAME LIKE :namePattern
</sql-query
>

一个命名查询可能会返回一个标量值。你必须使用 <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
>

你可以把结果集映射的信息放在外部的 <resultset> 元素中，这样就可以在多个命名查询间，或者通过 setResultSetMapping() API 来访问。


<resultset name="personAddress">
    <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},
           address.STREET AS {address.street},
           address.CITY AS {address.city},
           address.STATE AS {address.state},
           address.ZIP AS {address.zip}
    FROM PERSON person
    JOIN ADDRESS address
        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();

`17.2.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.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 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.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，这可能会效率更高，但这不是必需的。