Asp.Net大型项目实践(4)-用NHibernate保存和查询我们的业务领域对象之多条件组合查询（附源码）
上篇介绍了在系统表格查询中的分页和排序，而在实际业务中客户往往需要灵活的多条件组合查询，如果比较没技巧的针对每个表格写不同的组合查询代码和界面无疑工作量是比较大的，在此我们使用EXT的GridFilters插件结合后台的NHibernate争取用一种统一简便的方法去实现，如下图：
大家可以看到支持多种数据类型，还有一些其他数据类型如日期型，bool型 因界面关系就不贴出来了，这样客户通过表格的列头几乎可以满足所有的组合查询需求，同样在本篇我们只实现数据访问层，UI层的实现以后讲解。
- 通过DataFilter集合表示组合查询的数据结构
a.为什么属性名为小写，到EXT的时候会解释
代码
namespace Demo.HIS.FrameWork.DomainBase
{
public class DataFilter
{
public string type { get; set; }
public string value { get; set; }
public string field { get; set; }
public string comparison { get; set; }
}
}
b.属性解释：
field:查询字段的名称（准确的说应该是对象的属性名）
tyep:查询字段的数据类型，我们的类型名约定为：string,date,numeric,boolean,list(如上图中的“医疗类别”)
value:查询字段的匹配值值
comparison:运算符，我们的运算符名约定为：lt(小于),gt(大于),eq(等于) ，如上图中的“单价”这类的数值范围查询或日期范围查询
c.举例说明：比如我们要同时查询姓名（Name）包含“张”，且年龄(Age)大于20小于30，且未婚的用户：
代码
var dfList = new List<DataFilter>();
dfList.Add(new DataFilter { field = "Name", type = "string", value = "张" });
dfList.Add(new DataFilter { field = "Age", type = "numeric", value = "20", comparison = "gt" });
dfList.Add(new DataFilter { field = "Age", type = "numeric", value = "30", comparison = "lt" });
dfList.Add(new DataFilter { field = "IsMarry", type = "boolean", value = "false" });
注1：我们约定对于字串的查询都是模糊包含，字段间的关系为并且(and)，也可以做成or,但只能选择其一;
注2：在后面结合EXT后我们不用这么麻烦的写组合查询条件，因为EXT UI组件后会自动传输这样的集合给后台处理 -
在 RepositoryNhbImpl<T>数据访问基类中实现将List<DataFilter>集合转换为Hql字符串：
代码
//处理ExtJs字段过滤
public string GetHqlstrByExtFilter(List<DataFilter> filters,string a)
{
if (filters == null)
return string.Empty;
StringBuilder result = new StringBuilder();
//type=string
var stringList = from f in filters where f.type == "string" select f;
foreach (var i in stringList)
{
result.Append(a+"."+i.field + " like " + "'%" + i.value + "%'" + " and ");
}
//type=boolean
var booleanList = from f in filters where f.type == "boolean" select f;
foreach (var i in booleanList)
{
result.Append(a+"."+i.field + "=" + i.value + " and ");
}
//type=numeric
var numericList = from f in filters where f.type == "numeric" group f by f.field into g select g;
foreach (var i in numericList)
{
result.Append("( ");
string iiStr = string.Empty;
foreach (var ii in i)
{
iiStr += a+"."+ii.field + GetComparison(ii.comparison) + ii.value + " and ";
}
result.Append(iiStr.Substring(0, iiStr.Length - 4));
result.Append(" )");
result.Append(" and ");
}
//type=date
var dateList = from f in filters where f.type == "date" group f by f.field into g select g;
foreach (var i in dateList)
{
result.Append("( ");
string iiStr = string.Empty;
foreach (var ii in i)
{
iiStr += a+"."+ii.field + GetComparison(ii.comparison) + " to_date('" + ii.value + "', 'mm/dd/yyyy')" + " and ";
}
result.Append(iiStr.Substring(0, iiStr.Length - 4));
result.Append(" )");
result.Append(" and ");
}
//type=list :["1","2"]
var listList = from f in filters where f.type == "list" select f;
foreach (var i in listList)
{
result.Append(a+"."+i.field + " in " + i.value.Replace("[", "( ").Replace("]", " )").Replace("\"","'") + " and ");
}
return result.ToString().Substring(0, result.Length - 4);
}
private string GetComparison(string comparison)
{
string res = string.Empty;
switch (comparison)
{
case "lt":
res = "<";
break;
case "gt":
res = ">";
break;
case "eq":
res = "=";
break;
}
return res;
}以上代码具备一定的Linq和Hql就能看懂，我就不解释了，大概意思就是通过Linq检索集合里的不同数据类型分别处理最后拼出Hql字串符，参数"a"是为以后多表查询用的 ，用于指定对象的别名。
如上面 同时查询姓名（Name）包含“张”，且年龄(Age)大于20小于30，且未婚的用户 这个例子，生成的HQL为：o.Name like '%张%' and o.IsMarry=false and ( o.Age>20 and o.Age<30 ) -
给上篇字典项查询的分页排序查询方法增加多条件组合查询功能
还记得上篇的这个方法吗：代码
namespace Demo.HIS.Infrastructure.Repositories.Data
{
public class DictionaryRepositoryImpl : RepositoryNhbImpl<Dictionary>, IDictionaryRepository
{
public IList<Dictionary> GetPlistByCategoryId(string id, int start, int limit, string sort, string dir, out long total)
{
sort = "d." + sort;
var query = Session.CreateQuery(@"select d from Dictionary as d where d.Category.Id=:Id"
+ " 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")
.SetString("Id", id)
.UniqueResult<long>();
return query.List<Dictionary>();
}
}
}现在我们给这个方法增加一个参数List<DataFilter> filters，并添加组合查询功能如下：
代码
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 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拼接进去，有两点注意一下：
a.注意联接关键字的判断,用"where"还是"and"
b.注意获取total总数的HQL同样也要拼接 -
最后接上篇那个测试修改一下
代码
//测试一下
public ActionResult test()
{
Demo.HIS.Infrastructure.Core.Repositories.IDictionaryRepository r = new Demo.HIS.Infrastructure.Repositories.Data.DictionaryRepositoryImpl();
long total;
var dfList = new List<DataFilter>();
dfList.Add(new DataFilter { field = "Code", type = "string", value = "01" });
r.GetPlistByCategoryId("48391bb4-471b-4499-899b-cea9748e1a7b", 0, 15, "Index", "desc", dfList, out total);
return Content("");
}通过NHProfiler的查看，我们看到NHibernate生成了2条SQL语句：
代码
select count(* ) as col_0_0_
from INFRA_DICTIONARY dictionary0_
where (dictionary0_.IsDelete = 0)
and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */
and (dictionary0_.CODE like '%01%')代码
select *
from (select dictionary0_.DICTIONARY_ID as DICTIONARY1_0_,
dictionary0_.VERSION as VERSION0_,
dictionary0_.NAME as NAME0_,
dictionary0_.CODE as CODE0_,
dictionary0_.INPUT_CODE1 as INPUT5_0_,
dictionary0_.INPUT_CODE2 as INPUT6_0_,
dictionary0_.INPUT_CODE3 as INPUT7_0_,
dictionary0_.INDEX_FIELD as INDEX8_0_,
dictionary0_.DESCRIPTION as DESCRIPT9_0_,
dictionary0_.CREATETIME as CREATETIME0_,
dictionary0_.ISDELETE as ISDELETE0_,
dictionary0_.DICCATEGORY_ID as DICCATE12_0_
from INFRA_DICTIONARY dictionary0_
where (dictionary0_.IsDelete = 0)
and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */
and (dictionary0_.CODE like '%01%')
order by dictionary0_.INDEX_FIELD desc)
where rownum <= 15 /* :p1 */可以看到我们的查询条件都加进去了，现在唯一感觉比较麻烦的就是构造List<DataFilter>，不过到后面结合EXT后根本不用自己去构造，UI自动传过来，这样是不是就比较完美了。
源码：这篇的源码就不上传了，所有代码在上面都已经贴出来了，在上篇基础上改改就OK了，等下篇多表关联查询一起上传吧：）
