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.