NHibernate 之集合的映射与查询篇

题记：

NHibernate: 2.1.2.4000

.net : > fx 2.0

(原文链接 http://ddbiz.com/?p=146)

NHibernate的<map /> 集合映射 可以使用 System.Collections.Generic.IDictionary<TKey, TValue> 来映射。map映射的几个主要部分包括:

 

集合外键: <map ...> <key column="???" />...</map>

通过 <key column="???" /> 来表明对象关系

 

索引字段: <map ...> <key ... /> <map-key .../> ...</map>

对应 IDictionary<TKey ...>, 可以使用

值类型   : <map-key /> 或者 <composite-map-key />

引用类型: <map-key-many-to-many />

 

集合元素: <map ...> <key ... /> <map-key .../> <element ... /> ... </map>

对应 IDictionary<TKey, TValue>, 可以使用

值类型   : <element /> 或 <composite-element />

应用类型: <one-to-many /> 或 <many-to-many /> 等。

【以上内容感谢 李老师文章】

集合元素的查询常用的函数包括:

index()

indiecs()

elements()

maxindex()

minindex()

minelement()

maxelement()

size()

   以及 限定词 

some, all, exists, any, in

 

接下来我们通过一个例子来展示查询和相对应的SQL语句是怎样的。

 

映射文件： msgnotice.hbm.xml

<?xml version="1.0"?><hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="true"> <import class="core.domain.msg.TMsgNoticeWayInfo, core" /> <class name="core.domain.msg.TMsgNotice, core" table="tmsgnotice" dynamic-update="true"> <cache usage="read-write" /> <id name="Id" column="Id" type="Int32" unsaved-value="0"> <generator class="identity" /> </id> <version name="Version" column="Version" type="Int32"/> <property name="MsgNoticeType" type="core.domain.msg.TMsgNoticeType, core" column="msgnoticetype" not-null="true"/> <property name="Subject" type="String(250)" column="Subject" /> <property name="Content" type="String(65535)" column="Content" lazy="true" /> <many-to-one name="Receiver" class="core.domain.member.TUser, core" column="Receiver" /> <property name="ValidateTo" type="DateTime" column="ValidateTo" /> <property name="UpdateTimestamp" type="Timestamp" column="UpdateTimestamp" /> <property name="CreateTimestamp" type="DateTime" column="CreateTimestamp" update="false" /> <map name="NoticeWays" cascade="save-update" lazy="true" table="tmsgnoticeway"> <key column="msgnotice" not-null="true" /> <map-key column="way" type="core.domain.msg.TMsgNoticeWayType, core" /> <composite-element class="core.domain.msg.TMsgNoticeWay, core" > <property name="WayNumber" type="String(64)" column="waynumber" /> <property name="Status" type="core.domain.msg.TMsgNoticeStatus, core" column="Status" /> <property name="UpdateTimestamp" type="DateTime" column="UpdateTimestamp" /> </composite-element> </map> </class></hibernate-mapping> 

类文件： msgnotice.cs

public class TMsgNotice:DomainObject<Int32> { #region /// <summary> /// MsgNoticeType 消息类型 /// </summary> public virtual TMsgNoticeType MsgNoticeType { get; set; } /// <summary> /// 标题 /// </summary> public virtual string Subject { get; set; } /// <summary> /// 正文内容，用于email类型 /// </summary> public virtual string Content { get; set; } /// <summary> /// 接受者 /// </summary> public virtual TUser Receiver { get; set; } /// <summary> /// 有效时间 /// </summary> public virtual DateTime? ValidateTo { get; set; } /// <summary> /// 通知方式 /// </summary> public virtual IDictionary<TMsgNoticeWayType, TMsgNoticeWay> NoticeWays { get; set; } #endregion }

 

类文件中的几个属性，有些是 enum定义，此处就省略了定义，读者可以自行填补，如 TMsgNoticeWayType,  TMsgNoticeType等。

 

应用说明： msgnotice 是用来保存一些信息，而这些信息是可以根据定义的 TMsgNoticeWayType的方式，通过不同的渠道发送给接收者，如 SMS短信、及时通信工具、邮件等的。

 

那么如果我们要查询当前系统中需要发送的邮件信息，如： MsgNoticeList(TMsgNoticeWayType.Email)，查询改怎么做呢?

为了使查询结果也能结构化定义，我们另外定义了一个临时类，用于储存MsgNoticeList的返回结果：

 

 

/// <summary> /// TMsgNoticeWayInfo, 单项的消息列表 /// </summary> public class TMsgNoticeWayInfo { public Int32 Id { get; set; } public string Subject { get; set; } public string Content{get;set;} public TMsgNoticeType MsgNoticeType { get; set; } /// <summary> /// 如果 /// MsgNoticeType = WebNotice, Receiver = User.Account /// MsgNoticeType = Email, Receiver = User.Email /// MsgNoticeType = SMS, Receiver = User.Profile.Cellphone /// </summary> public string WayNumber { get; set; } public TMsgNoticeStatus Status { get; set; } public DateTime ValidateTo { get; set; } public DateTime UpdateTimestamp { get; set; } public TMsgNoticeWayInfo(Int32 id, string subject, string content, TMsgNoticeType msgt, DateTime validateto, string receiver, TMsgNoticeStatus status, DateTime updatetimestamp) { this.Id = id; this.Subject = subject; this.Content = content; this.MsgNoticeType = msgt; this.WayNumber = receiver; this.ValidateTo = validateto; this.Status = status; this.UpdateTimestamp = updatetimestamp; } } 

 

如下的查询如：

StringBuilder hql = new StringBuilder(); hql.Append("select new TMsgNoticeWayInfo(a.Id, a.Subject, a.Content, a.MsgNoticeType, a.ValidateTo, "); hql.Append(" w.WayNumber, w.Status, w.UpdateTimestamp )"); hql.Append(" from TMsgNotice a inner join a.NoticeWays w "); hql.Append(" where :way in indices(a.NoticeWays) "); hql.Append(" order by a.CreateTimestamp asc "); IQuery q = session.CreateQuery(hql.ToString()); q.SetParameter("way", way); return q.List<TMsgNoticeWayInfo>(); 

这个查询生成的SQL语句：

select tmsgnotice0_.Id as col_0_0_,tmsgnotice0_.Subject as col_1_0_,tmsgnotice0_.Content as col_2_0_,tmsgnotice0_.msgnoticetype as col_3_0_,tmsgnotice0_.ValidateTo as col_4_0_,noticeways1_.waynumber as col_5_0_,noticeways1_.Status as col_6_0_,noticeways1_.UpdateTimestamp as col_7_0_ from tmsgnotice tmsgnotice0_ inner join tmsgnoticeway noticeways1_ on tmsgnotice0_.Id=noticeways1_.msgnotice where ?p0 in (select noticeways2_.way from tmsgnoticeway noticeways2_ where tmsgnotice0_.Id=noticeways2_.msgnotice) order by tmsgnotice0_.CreateTimestamp asc 

 

很显然，这不是我们要的结果，而且这个查询的效率可以说十分低下。我们换一种方式看看：

 

StringBuilder hql = new StringBuilder();hql.Append("select new TMsgNoticeWayInfo(a.Id, a.Subject, a.Content, a.MsgNoticeType, a.ValidateTo, ");hql.Append(" w.WayNumber, w.Status, w.UpdateTimestamp )");hql.Append(" from TMsgNotice a inner join a.NoticeWays w ");hql.Append(" where index(w) = :way ");hql.Append(" order by a.CreateTimestamp asc ");IQuery q = session.CreateQuery(hql.ToString());q.SetParameter("way", way);return q.List<TMsgNoticeWayInfo>(); 

这个查询生成的SQL是这样的：

select tmsgnotice0_.Id as col_0_0_,tmsgnotice0_.Subject as col_1_0_,tmsgnotice0_.Content as col_2_0_,tmsgnotice0_.msgnoticetype as col_3_0_,tmsgnotice0_.ValidateTo as col_4_0_,noticeways1_.waynumber as col_5_0_,noticeways1_.Status as col_6_0_,noticeways1_.UpdateTimestamp as col_7_0_ from tmsgnotice tmsgnotice0_ inner join tmsgnoticeway noticeways1_ on tmsgnotice0_.Id=noticeways1_.msgnotice where noticeways1_.way=?p0 order by tmsgnotice0_.CreateTimestamp asc 

 

很好，这才是我们需要的结果：直接通过 noticeways1_.way 来限定我们要的内容。

 

index(a) 还可以 使用如:

index(a) in (:way1, :way2) 这种方式，生成的SQL语句也就是

where noticeways1_.way in (:way1, :way2)

 

总结：

index    是从 key 中选择符合条件的key,

indices, 是什么呢？

(原文链接 http://ddbiz.com/?p=146)