Native sql (本地sql)在Hibernate中

Native sql 本地sql在Hibernate中

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

 

使用SQLQuery

 

1.      标量查询scalar query

如

sess.CreateSQLQuery("SELECT * FROM CATS")
  
  

 .AddScalar("ID", NHibernateUtil.Int64)
  
  

 .AddScalar("NAME", NHibernateUtil.String)
  
  

 .AddScalar("BIRTHDATE", NHibernateUtil.Date)
  
  

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

2.      Entity query

如

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

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

返回List的Cat[]。

 

 

3.      处理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))

 

4.      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.*}

 

 

 
 5.      
 
 得到non-managed entities
  
  

处理继承
  
  

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

 
 6.      
 
 参数
  
  

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

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

 
 2.      
 
 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的实体。
  
  

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

           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。
  
  

 
 4.      
 
 
 
 
 
 
 
 利用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();
  
  

 
 5.      
 
 显示指定结果集中的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},
  
  

        
 
 
  
  
   
   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>
  
  

 
 6.      
 
 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:

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

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