• Asp.Net大型项目实践(4)用NHibernate保存和查询我们的业务领域对象之分页与排序(附源码)


      接上篇中讲到的字典维护功能,在本篇中我们将实现下图中的分页和排序功能,UI层的实现以后我们会讲到,本篇只实现数据访问层

    1. 在字典项的数据访问接口,添加一个可以通过字典类别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”表示倒序和正序

    2. 实现接口的方法

      代码
      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,只能通过再查一次数据库实现,如果大伙有更好的办法可以告诉我

    3. 通过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语句是非常重要的!

    4. 测试一下
      我在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 */

    注:如果你的数据库配置好了,下面的源码是完全可以运行测试的

    源码:HISDemo-4.rar

  • 相关阅读:
    python和web框架面试题目整理(2)
    Linux系统运维面试题目整理(1)
    python和web框架面试题目整理(1)
    2018-7-11 Django REST API小试牛刀
    2018-4-27 python实现systemd进程后台运行任务
    从git中删除.idea目录
    springboot中的banner素材
    IpUtils获取用户请求真实ip工具类
    导出sqlserver数据库设计语句
    nginx: [emerg] unknown directive "if(!-e" 错误解决
  • 原文地址:https://www.cnblogs.com/legendxian/p/1632262.html
Copyright © 2020-2023  润新知