This project is read-only.

Asp.Net大型项目实践(4)-用NHibernate保存和查询我们的业务领域对象之多表关联查询(附源码) (转http://www.cnblogs.com/legendxian/archive/2009/12/25/1632349.html)

Sep 29, 2011 at 4:23 PM

Asp.Net大型项目实践(4)-用NHibernate保存和查询我们的业务领域对象之多表关联查询(附源码)

  NHibernate的多表关联查询做的还是很不错的,除了比较BT的报表查询,一般业务所需的多表查询基本都能满足,且效率也还算不错,NHibernate多表查询在数据库访问层上其实比较简单。反而难点在于如何把查询出来的多层对象不转换成DTO,直接传输到UI进行绑定,下篇讲展现层的时候会详细描述(注:关于DTO的使用和利弊这里不多加讨论,在本系统中的设计原则是尽量避免使用DTO)。

依然还是接之前字典维护的例子,我们需要在字典项目的表格里显示一列“类别”即字典项目所属字典类别的名称,这个字段在字典项的表是不存在的,需要关联查询字典类别表才能得出。如下图(由于存在项目客户实际数据,所以我图改了一些...):

 

我们把上一篇已经具备分页,排序,多条件组合查询功能的方法public IList<Dictionary> GetPlistByCategoryId(...)再改一改:

 

代码
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> //现在我具备分页,排序,多条件查询,多表关联查询,基本全乎了:)
public IList<Dictionary> GetPlistByCategoryId(string id, int start, int limit, string sort, string dir, List<DataFilter> filters, out long total)
{
sort
= "d." + sort;
var strFilter
= base.GetHqlstrByExtFilter(filters, "d");//通过刚才基类的方法把filters转换成HQL字符串

var query
= Session.CreateQuery(@"select d from Dictionary as d left join fetch d.Category where d.Category.Id=:Id"//改了下这里,搞成多表查询
+ (string.IsNullOrEmpty(strFilter) ? string.Empty : " and " + strFilter)//这里把组合查询字符串加进去
+ " order by " + sort + " " + dir)
.SetString(
"Id", id)
.SetFirstResult(start)
.SetMaxResults(limit);

total
= Session.CreateQuery(@"select count(*) from Dictionary as d where d.Category.Id=:Id"
+ (string.IsNullOrEmpty(strFilter) ? string.Empty : " and " + strFilter))//别忘记这里也要加哟
.SetString("Id", id)
.UniqueResult
<long>();

return query.List<Dictionary>();
}

其实就是改了下HQL语句

以前是 var query = Session.CreateQuery(@"select d from Dictionary as d where d.Category.Id=:Id"。。。

修改后var query = Session.CreateQuery(@"select d from Dictionary as d left join fetch d.Category where d.Category.Id=:Id"。。。

加了" left join fetch d.Category" 解释如下:

a.”left join“:HQL左关联关键字,和SQL一样的,大家应该很容易理解

b."fetch":HQL关键字,非常重要,表示不使用延迟加载一次性取出所关联的类的数据。因为我们在Xml配置的时候设置的是lazy=true(延迟加载包含对象),默认的NHibernate不会把Dictionary下的Category属性的数据取出来,而是在你需要用的时候再去现查数据库取。这本来是件好事,可是当我们出集合的时候就会出现可怕的情况,出一条Dictionary数据读一次数据库去取Dictionary下的Category,比如我们这里分页15条数据,那么会产生17条SQL语句(1次查总数+1次查字典项表+15次查每个字典项所对应的字典类别),恐怖吧...

所以我们用fetch 告诉NHibernate 在这里你就一次性取出好了 不用延迟加载了,这样只会像以前一样产生2条SQL语句,如下

查总数total的和以前一样不变:

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->select count(* ) as col_0_0_
from INFRA_DICTIONARY dictionary0_
where (dictionary0_.IsDelete = 0)
and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */

 

生成了关联查询的SQL语句(除了'select'与'from'之间的语句生成有点囧,其他的基本和咱手写的也差不多):

代码
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->select *
from (select dictionary0_.DICTIONARY_ID as DICTIONARY1_0_0_,
diccategor1_.DICCATEGORY_ID
as DICCATEG1_1_1_,
dictionary0_.VERSION
as VERSION0_0_,
dictionary0_.NAME
as NAME0_0_,
dictionary0_.CODE
as CODE0_0_,
dictionary0_.INPUT_CODE1
as INPUT5_0_0_,
dictionary0_.INPUT_CODE2
as INPUT6_0_0_,
dictionary0_.INPUT_CODE3
as INPUT7_0_0_,
dictionary0_.INDEX_FIELD
as INDEX8_0_0_,
dictionary0_.DESCRIPTION
as DESCRIPT9_0_0_,
dictionary0_.CREATETIME
as CREATETIME0_0_,
dictionary0_.ISDELETE
as ISDELETE0_0_,
dictionary0_.DICCATEGORY_ID
as DICCATE12_0_0_,
diccategor1_.VERSION
as VERSION1_1_,
diccategor1_.NAME
as NAME1_1_,
diccategor1_.PARENT_ID
as PARENT4_1_1_,
diccategor1_.TREE_CODE
as TREE5_1_1_,
diccategor1_.LEAF
as LEAF1_1_,
diccategor1_.DESCRIPTION
as DESCRIPT7_1_1_,
diccategor1_.CREATETIME
as CREATETIME1_1_,
diccategor1_.ISDELETE
as ISDELETE1_1_,
diccategor1_.NODE_LEVEL
as NODE10_1_1_
from INFRA_DICTIONARY dictionary0_
left outer join INFRA_DICCATEGORY diccategor1_
on dictionary0_.DICCATEGORY_ID = diccategor1_.DICCATEGORY_ID
where (dictionary0_.IsDelete = 0)
and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */
order by dictionary0_.INDEX_FIELD desc)
where rownum <= 15 /* :p1 */

 

还是在我们那个很土的测试方法里测试一下,遍列访问 Dictionary.Category.Name(自己加点测试数据),你打开NHProfiler工具监视一下 ,然后把上面说的有点绕的"fetch"去掉,再看看,这样就能明白了。测试代码如下(写的很土,能理解就行,时间关系我就没有搞啥单元测试之类的了):

 

代码
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> //测试一下
public ActionResult test3()
{
Demo.HIS.Infrastructure.Core.Repositories.IDictionaryRepository r
= new Demo.HIS.Infrastructure.Repositories.Data.DictionaryRepositoryImpl();
long total;
var list
= r.GetPlistByCategoryId("48391bb4-471b-4499-899b-cea9748e1a7b", 0, 15, "Index", "desc", null, out total);
string str;
foreach (var item in list)
{
str
= item.Category.Name;//如果你不用fetch 每一次循环我都会去访问数据库,哈哈
}
return Content("");
}

 

如我们再复杂点多关联几个表,如此这般:

代码
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> var query = Session.CreateQuery(@"select g from GroupedServiceItem g
left join fetch g.Item i
left join fetch i.ItemCategory
left join fetch i.MedicalCategory
left join fetch i.FeesCategory
where g.ParentItem.Id=:Id
")
.SetString(
"Id", id);

以上代码是我在项目中随便摘取的,大概意思理解就可以了...

 

如上所述我们实现了NHibernate的多表查询,但是我们查询出的对象是包含嵌套的呀,如何把这样的结构送到UI及Ext进行数据绑定呢?请看下篇...

源码(上篇的源码也在这里,搞好数据库,整点测试数据,就能像我上面描述的一样运行测试):HISDemo-5.rar