`

hibernate在命名查询named queries中使用native sql

 
阅读更多

13.2.Named SQL queries

Named SQL queries may be defined in the mapping document and called in exactly the same way as a named HQL query. In this case, we do not need to call 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>
IList people = sess.GetNamedQuery("persons")
.SetString("namePattern", namePattern)
.SetMaxResults(50)
.List();

The <return-join> and <load-collection> elements are used to join associations and define queries which initialize collections, respectively.

<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},
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>

A named SQL query may return a scalar value. You must declare the column alias and NHibernate type using the <return-scalar> element:

<sql-query name="mySqlQuery">
<return-scalar column="name" type="String"/>
<return-scalar column="age" type="Int64"/>
SELECT p.NAME AS name,
p.AGE AS age,
FROM PERSON p WHERE p.NAME LIKE 'Hiber%'
</sql-query>

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 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},
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>

You can alternatively use the resultset mapping information in your .hbm.xml files directly in code.

IList cats = sess.CreateSQLQuery(
"select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
)
.SetResultSetMapping("catAndKitten")
.List();

13.2.1.Using return-property to explicitly specify column/alias names

With <return-property> you can explicitly tell NHibernate what column aliases to use, instead of using the {}-syntax to let NHibernate inject its own aliases.

<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> also works with multiple columns. This solves a limitation with the {}-syntax which can not allow fine grained control of multi-column properties.

<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>

Notice that in this example we used <return-property> in combination with the {}-syntax for injection, allowing users to choose how they want to refer column and properties.

If your mapping has a discriminator you must use <return-discriminator> to specify the discriminator column.

13.2.2.Using stored procedures for querying

NHibernate 1.2 introduces support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset to be able to work with NHibernate. An example of such a stored function in MS SQL Server 2000 and higher is as follows:

CREATE PROCEDURE selectAllEmployments AS
SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE,
REGIONCODE, EMPID, VALUE, CURRENCY
FROM EMPLOYMENT

To use this query in NHibernate you need to map it via a named query.

<sql-query name="selectAllEmployments_SP">
<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>
exec selectAllEmployments
</sql-query>

Notice that stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.

13.2.2.1.Rules/limitations for using stored procedures

To use stored procedures with NHibernate the procedures/functions have to follow some rules. If they do not follow those rules they are not usable with NHibernate. If you still want to use these procedures you have to execute them via session.Connection. The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries can't be paged with SetFirstResult()/SetMaxResults().

Recommended call form is dependent on your database. For MS SQL Server use exec functionName <parameters>.

For Oracle the following rules apply:

  • A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.

For MS SQL server the following rules apply:

  • The procedure must return a result set. NHibernate will use IDbCommand.ExecuteReader() to obtain the results.

  • If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.





参考:
http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querysql.html

http://blog.csdn.net/cm4ever/archive/2005/04/05/337533.aspx
分享到:
评论

相关推荐

    Hibernate 中文 html 帮助文档

    外置命名查询(Externalizing named queries) 10.4.2. 过滤集合 10.4.3. 条件查询(Criteria queries) 10.4.4. 使用原生SQL的查询 10.5. 修改持久对象 10.6. 修改脱管(Detached)对象 10.7. 自动状态检测 10.8. 删除...

    最全Hibernate 参考文档

    外置命名查询(Externalizing named queries) 10.4.2. 过滤集合 10.4.3. 条件查询(Criteria queries) 10.4.4. 使用原生SQL的查询 10.5. 修改持久对象 10.6. 修改脱管(Detached)对象 10.7. 自动状态检测 10.8. 删除...

    hibernate 体系结构与配置 参考文档(html)

    外置命名查询(Externalizing named queries) 10.4.2. 过滤集合 10.4.3. 条件查询(Criteria queries) 10.4.4. 使用原生SQL的查询 10.5. 修改持久对象 10.6. 修改脱管(Detached)对象 10.7. 自动状态检测 10.8....

    Hibernate教程

    外置命名查询(Externalizing named queries) 11.4.2. 过滤集合 11.4.3. 条件查询(Criteria queries) 11.4.4. 使用原生SQL的查询 11.5. 修改持久对象 11.6. 修改脱管(Detached)对象 11.7. 自动状态检测 11.8. ...

    Hibernate3+中文参考文档

    外置命名查询(Externalizing named queries) 10.4.2. 过滤集合 10.4.3. 条件查询(Criteria queries) 10.4.4. 使用原生SQL的查询 10.5. 修改持久对象 10.6. 修改脱管(Detached)对象 10.7. 自动状态检测 10.8. 删除...

    hibernate3.04中文文档.chm

    外置命名查询(Externalizing named queries) 11.4.2. 过滤集合 11.4.3. 条件查询(Criteria queries) 11.4.4. 使用原生SQL的查询 11.5. 修改持久对象 11.6. 修改脱管(Detached)对象 11.7. 自动状态检测 11.8....

    Hibernate参考文档

    外置命名查询(Externalizing named queries) 10.4.2. 过滤集合 10.4.3. 条件查询(Criteria queries) 10.4.4. 使用原生SQL的查询 10.5. 修改持久对象 10.6. 修改脱管(Detached)对象 10.7. 自动状态检测 10.8. 删除...

    hibernate 框架详解

    外置命名查询(Externalizing named queries) 11.4.2. 过滤集合 11.4.3. 条件查询(Criteria queries) 11.4.4. 使用原生SQL的查询 11.5. 修改持久对象 11.6. 修改脱管(Detached)对象 11.7. 自动状态检测 ...

    Hibernate Reference Documentation3.1

    10.4.4. Queries in native SQL 10.5. Modifying persistent objects 10.6. Modifying detached objects 10.7. Automatic state detection 10.8. Deleting persistent objects 10.9. Replicating object between two...

    hibernate3.6 文档(pdf 格式)

    3.4.1. SQL Dialects .......................................................................................... 42 3.4.2. Outer Join Fetching ..............................................................

Global site tag (gtag.js) - Google Analytics