Native sql (本地sql)在Hibernate中

Native sql 本地sql在Hibernate中　　

http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html　　

　　



使用SQLQuery　　



　　

<!--[if !supportLists]-->1. <!--[endif]-->标量查询scalar query　　

如　　


sess.CreateSQLQuery("SELECT * FROM CATS")　　

.AddScalar("ID", NHibernateUtil.Int64)　　

.AddScalar("NAME", NHibernateUtil.String)　　

.AddScalar("BIRTHDATE", NHibernateUtil.Date)　　

返回List的Object[]，每个Object有上述三个字段组成。　　

<!--[if !supportLists]-->2. <!--[endif]-->Entity query　　

如　　


sess.CreateSQLQuery("SELECT * FROM CATS").AddEntity(typeof(Cat));　　

sess.CreateSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").AddEntity(typeof(Cat));　　

返回List的Cat[]。　　

　　

　　

<!--[if !supportLists]-->3. <!--[endif]-->处理association和collections　　

如　　

sess.CreateSQLQuery("SELECT c.*, m.* FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")　　

.AddEntity("cat", typeof(Cat))　　

.AddEntity("mother", typeof(Cat))　　

每行将返回两个Cat对象：一个Cat，一个Cat的mother。　　

但是上面的代码会造成列名的冲突问题。　　

因此：　　

sess.CreateSQLQuery("SELECT {cat.*}, {mother.*} FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")　　

.AddEntity("cat", typeof(Cat))　　

.AddEntity("mother", typeof(Cat))　　

　　

<!--[if !supportLists]-->4. <!--[endif]-->alias和property引用　　

Description	Syntax	Example
A simple property	{[aliasname].[propertyname]}	A_NAME as {item.Name}
A composite property	{[aliasname].[componentname].[propertyname]}	CURRENCY as {item.Amount.Currency}, VALUE as   {item.Amount.Value}
Discriminator of an entity	{[aliasname].class}	DISC as {item.class}
All properties of an entity	{[aliasname].*}	{item.*}
A collection key	{[aliasname].key}	ORGID as {coll.key}
The id of an collection	{[aliasname].id}	EMPID as {coll.id}
The element of an collection	{[aliasname].element}	XID as {coll.element}
property of the element in the collection	{[aliasname].element.[propertyname]}	NAME as {coll.element.Name}
All properties of the element in the collection	{[aliasname].element.*}	{coll.element.*}
All properties of the the collection	{[aliasname].*}	{coll.*}

　　

　　

<!--[if !supportLists]-->5. <!--[endif]-->得到non-managed entities　　

　　

处理继承　　

native sql查询的实体是一个继承结构中的一部分的话，就必须包括进来其基类和子类的属性。　　

　　

<!--[if !supportLists]-->6. <!--[endif]-->参数　　

Query query = sess.CreateSQLQuery("SELECT * FROM CATS WHERE NAME like ?").AddEntity(typeof(Cat));　　

IList pusList = query.SetString(0, "Pus%").List();　　

　　

query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like :name").AddEntity(typeof(Cat));　　

IList pusList = query.SetString("name", "Pus%").List();
　　

Named sql queries
　　

<!--[if !supportLists]-->1. <!--[endif]-->Scalar query　　

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

　　

　　

<!--[if !supportLists]-->2. <!--[endif]-->Entity query　　

<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();　　

return的含义：这个查询返回一个alias的实体。　　

　　

　　

<!--[if !supportLists]-->3. <!--[endif]-->return-join和load-collection　　

<sql-query name="personsWith">　　

<!--[if gte vml 1]><v:line
id="_x0000_s1028" style='position:absolute;flip:y;z-index:3' from="39.6pt,13.8pt"
to="48.6pt,45pt" coordsize="21600,21600">
<v:stroke endarrow="block" />
</v:line><![endif]--><!--[if !vml]--><!--[endif]--> <return alias="person" class="eg.Person"/>　　

<!--[if gte vml 1]><v:line
id="_x0000_s1027" style='position:absolute;flip:x y;z-index:2' from="30.6pt,13.8pt"
to="66.6pt,84pt" coordsize="21600,21600">
<v:stroke endarrow="block" />
</v:line><![endif]--><!--[if !vml]--><!--[endif]--> <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-join和load-collection都可以参照第一部分中的assocation和collection。　　

　　

<!--[if !supportLists]-->4. <!--[endif]--><!--[if gte vml 1]><v:line
id="_x0000_s1026" style='position:absolute;left:0;text-align:left;flip:x y;
z-index:1' from="30.6pt,-245.7pt" to="147.6pt,3.9pt" coordsize="21600,21600">
<v:stroke endarrow="block" />
</v:line><![endif]--><!--[if !vml]--><!--[endif]-->利用resultset扩展关于结果集映射（参考上面的return和return-join）的信息　　

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

用程序的方式处理上面的配置信息：　　

IList cats = sess.CreateSQLQuery(　　

"select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"　　

)　　

.SetResultSetMapping("catAndKitten")　　

.List();　　

　　

　　

<!--[if !supportLists]-->5. <!--[endif]-->显示指定结果集中的column的显示名字（return-property）　　

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

将多column映射为一个名字　　

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

<place><city><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'>STARTDATE</span></city><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'> </span><state><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'>AS</span></state></place> {emp.StartDate}, <place><city><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'>ENDDATE</span></city><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'> </span><state><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'>AS</span></state></place> {emp.EndDate},　　

REGIONCODE as {emp.RegionCode}, <place><city><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'>EID</span></city><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'> </span><state><span lang="EN-US" style='font-size: 10.5pt; font-family: "Courier New";'>AS</span></state></place> {emp.Id}, VALUE, CURRENCY　　

FROM EMPLOYMENT　　

WHERE EMPLOYER = :id AND ENDDATE IS NULL　　

ORDER BY STARTDATE ASC　　

</sql-query>　　

　　

　　

<!--[if !supportLists]-->6. <!--[endif]-->stored procedures　　

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

stored procedures的使用有一些限制，如果不遵守，就不能在Hibernate中使用stored procedures，而只能用session.connection()。限制根据db的不同而不同，因为不同的db中stored procedures有差别。　　

　　Stored procedure queries can't be paged with setFirstResult()/setMaxResults().　　

　　Recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.　　

　　For Oracle the following rules apply:　　

<!--[if !supportLists]-->· <!--[endif]-->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 Sybase or MS SQL server the following rules apply:　　

<!--[if !supportLists]-->· <!--[endif]-->The procedure must return a result set. Note that since these servers can/will return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.　　

<!--[if !supportLists]-->· <!--[endif]-->If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.　　



Custom sql for create, update ,delete, loading