接上篇中讲到的字典维护功能,在本篇中我们将实现下图中的分页和排序功能,UI层的实现以后我们会讲到,本篇只实现数据访问层
- 在字典项的数据访问接口,添加一个可以通过字典类别Id获取字典项列表的方法,并带分页和排序功能
代码namespace Demo.HIS.Infrastructure.Core.Repositories
{
//字典项的数据库访问接口
public interface IDictionaryRepository : IRepository<Dictionary>
{
//通过字典类别Id获取字典项列表
IList<Dictionary> GetPlistByCategoryId(string id, int start, int limit, string sort, string dir, out long total);
}
}参数解释:
id:字典类别的Id;
start:表示分页从第几条数据开始查询;
limit:表示一个分页显示多少条数据;
total:返回一共有多少条数据,注意out的用法;
sort:需要排序属性名称的字符串,注意这里传入的是对象的属性名,而不是表的字段名
dir:分别用 “desc”和“asc”表示倒序和正序 -
实现接口的方法
代码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>();
}
}
}这里我们通过HQL语句进行查询和排序,通过NHibernate的.SetFirstResult和.SetMaxResults方法实现分页
对于返回查询数据的总数total,只能通过再查一次数据库实现,如果大伙有更好的办法可以告诉我 -
通过NHProfiler工具查看NHibernate生成的SQL语句
NHProfiler工具自己去下载(有破解的)
Demo.HIS.FrameWork项目下在添加引用NHProfiler下的HibernatingRhinos.NHibernate.Profiler.Appender.dll
还记得我们的SessionBuilder类吗,在这个类添加代码:代码private static void CreateConfiguration()
{
HibernatingRhinos.NHibernate.Profiler.Appender.NHibernateProfiler.Initialize();//查看HQL生成的SQL
//configuration = new Configuration().Configure(System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "Configuration\\hibernate.cfg.xml");
configuration = new Configuration().Configure();
}这样打开NHProfiler工具我们就可以查看NHibernate每一次访问数据库所生成的SQL语句了
对于ORM的使用,特别是刚开始还不熟悉的时候,查看ORM生成的真正SQL语句是非常重要的! -
测试一下
我在DemoHisSite项目简单写了个测试:代码//测试一下
public ActionResult test()
{
Demo.HIS.Infrastructure.Core.Repositories.IDictionaryRepository r = new Demo.HIS.Infrastructure.Repositories.Data.DictionaryRepositoryImpl();
long total;
r.GetPlistByCategoryId("48391bb4-471b-4499-899b-cea9748e1a7b", 0, 15, "Index", "desc", 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 */代码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 */
order by dictionary0_.INDEX_FIELD desc)
where rownum <= 15 /* :p1 */
注:如果你的数据库配置好了,下面的源码是完全可以运行测试的