NHibernate初学三之条件查询(Criteria Queries)与AspNetPager分页实例

浏览数：163 / 时间：2015年06月09日

NHibernate除了SQL与HQL两种查询操作外，还有一种就是条件查询Criteria，本文将从网上整理一些Criteria的理论及小实例，最后通过一个结合AspNetPager分页来加深理解，必竟分页这种功能在平时的项目中还是经常需要；

一：条件查询(Criteria Queries)理论

为了对应HQL的种种查询条件，NHibernate预定义了大量的Expression方法，列几个如下：

Eq = Equal
Gt = Greater than
Lt = Less than
Like = Like
Not = Not
IsNull = Is Null

1.1 条件查询(Criteria Queries)：具有一个直观的、可扩展的条件查询API是NHibernate的特色。NHibernate.ICriteria接口表示特定持久类的一个查询。ISession是 ICriteria 实例的工厂。

//创建关联到某个类的查询对象
ICriteria criteria = session.CreateCriteria(typeof(Person));
//添加表达式
criteria.Add(Expression.Eq("Name","Jackie Chan"));
IList list = criteria.List();

Eq是Equal的缩写，意思是添加一个查询表达式，Person.Name = “Jackie Chan”
对应HQL就是：from Person p where p.Name=”Jackie Chan”

1.1.1 经常还有一个实体里面有多条件查询，也可以如下面写法，简单地new出一个person对象，然后填充其属性即可，不用再去构造那丑陋的条件判断语句了：

ICriteria criteria = session.CreateCriteria(typeof(Person));
Person person = new Person();
person.Name = "Jackie Chan";
person.Age = 50;
//创建一个Example对象
criteria.Add(Example.Create(person));
IList list = criteria.List();

1.2 限制结果集内容:一个单独的查询条件是NHibernate.Expression.ICriterion 接口的一个实例。NHibernate.Expression.Expression类定义了获得某些内置ICriterion类型的工厂方法。

IList cats = sess.CreateCriteria(typeof(Cat))
    .Add( Expression.Like("Name", "Fritz%") )
    .Add( Expression.Between("Weight", minWeight, maxWeight) )
    .List();

1.2.1 约束可以按逻辑分组

IList cats = sess.CreateCriteria(typeof(Cat))
    .Add( Expression.Like("Name", "Fritz%") )
    .Add( Expression.Or(
        Expression.Eq( "Age", 0 ),
        Expression.IsNull("Age")
    ) )
    .List();


IList cats = sess.CreateCriteria(typeof(Cat))
    .Add( Expression.In( "Name", new String[] { "Fritz", "Izi", "Pk" } ) )
    .Add( Expression.Disjunction()
        .Add( Expression.IsNull("Age") )
        .Add( Expression.Eq("Age", 0 ) )
        .Add( Expression.Eq("Age", 1 ) )
        .Add( Expression.Eq("Age", 2 ) )
    ) )
    .List();

1.2.2 允许你直接使用SQL,{alias}占位符应当被替换为被查询实体的列别名

IList cats = sess.CreateCriteria(typeof(Cat))
            .Add( Expression.Sql("lower({alias}.Name) like lower(?)", "Fritz%", NHibernateUtil.String )
            .List();

1.3 结果集排序:可以使用NHibernate.Expression.Order来为查询结果排序,其方法有两个Order.Asc()及Order.Desc()

IList cats = sess.CreateCriteria(typeof(Cat))
    .Add( Expression.Like("Name", "F%")
    .AddOrder( Order.Asc("Name") )
    .AddOrder( Order.Desc("Age") )
    .SetMaxResults(50)
    .List();

1.4 限制记录范围

ICriteria criteria = session.CreateCriteria(typeof(Person));
//从第10条记录开始取
criteria.SetFirstResult(10);
//取20条记录
criteria.SetMaxResults(20);
IList list = criteria.List();

二：Criteria结合AspNetPager分页实例

分页功能基本上在第一个项目都会出现，能过本实例完成一个简单的分页功能，本文把主要代码贴出来，完整的源代码文章最后提供下载；

2.1 创建一个T_School的表，并创建一个存储过程用于循环插入数据进行分页，脚本如下：

CREATE TABLE [dbo].[T_School](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SchoolName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
    [BuildDate] [datetime] NULL,
    [Address] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [IsSenior] [bit] NULL,
    [StudentNum] [int] NULL,
 CONSTRAINT [PK_T_School] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Create PROCEDURE [dbo].[Pro_Insert]
AS
declare @ID int
BEGIN
 set @ID=0
 while @ID<200000
 begin
 insert into T_School(SchoolName,BuildDate,Address,IsSenior,StudentNum) VALUES(‘中学教育‘+cast(@ID as varchar),‘2014/3/4 21:49:32‘,‘厦门软件园‘,1,390)
 set @ID=@ID+1
 end
END

2.2 在实体层简单定义几个分页要用到的类，对一些属性进行封装，下面只是PageInfo类，其它类的代码大家直接查看源代码；

namespace Wujy.ModelLibrary.Pagination
{
    public class PageInfo
    {
        public PageInfo() { }

        public PageInfo(Type entityType,int pageIndex) 
        {
            this._entityType = entityType;
            this._pageIndex = pageIndex;
        }

        public PageInfo(Type entityType, int pageIndex,params NCondition[] conditions)
        {
            this._entityType = entityType;
            this._pageIndex = pageIndex;
            this._conditions = conditions;
        }

        public PageInfo(Type entityType, int pageIndex, NCondition[] conditions,NOrder[] orders)
        {
            this._entityType = entityType;
            this._pageIndex = pageIndex;
            this._conditions = conditions;
            this._orderFields = orders;
        }

        private Type _entityType;//类

        public Type EntityType
        {
            get { return _entityType; }
            set { _entityType = value; }
        }
        private int _pageIndex = 1;//页号

        public int PageIndex
        {
            get { return _pageIndex; }
            set { _pageIndex = value; }
        }

        private NCondition[] _conditions;//条件

        public NCondition[] Conditions
        {
            get { return _conditions; }
            set { _conditions = value; }
        }
        private NOrder[] _orderFields;//排序

        public NOrder[] OrderFields
        {
            get { return _orderFields; }
            set { _orderFields = value; }
        }
        private int pageSize = 10;

        public int PageSize
        {
            get { return pageSize; }
            set { pageSize = value; }
        }


        private int _recordCount;

        public int RecordCount
        {
            get { return _recordCount; }
            set { _recordCount = value; }
        }
        private int pageCount;

        public int PageCount
        {
            get { return pageCount; }
            set { pageCount = value; }
        }
        private System.Collections.IList list;

        public IList List
        {
            get { return list; }
            set { list = value; }
        }
    }
}

View Code

2.3 在DAL层里面创建一个基类，把分页的代码写在这里，下面就例出比较重要的几个代码：

 public void DoPager(PageInfo pi)
        {
            if (pi.EntityType == null)
            {
                throw new Exception("分页类名不能为空");
            }

            using (ISession session = new NHibernateHelper().GetSession())
            {

                ICriteria qbc = session.CreateCriteria(pi.EntityType);
                //总条数
                qbc.SetProjection(NHibernate.Criterion.Projections.RowCount());
                prepareConditions(qbc, pi.Conditions);
                pi.RecordCount = qbc
                            .SetMaxResults(1)
                            .UniqueResult<int>();
                //总页数
                pi.PageCount = 
                    pi.RecordCount % pi.PageSize == 0?
                    pi.RecordCount / pi.PageSize:
                    pi.RecordCount / pi.PageSize + 1;
                //qbc.SetProjection(null);
                //分页结果
                ICriteria _qbc = session.CreateCriteria(pi.EntityType);
                prepareConditions(_qbc, pi.Conditions);
                //设置排序
                prepareOrder(_qbc, pi.OrderFields);
                //分页结果
                pi.List = _qbc
                        .SetFirstResult((pi.PageIndex - 1) * pi.PageSize)
                        .SetMaxResults(pi.PageSize)                        
                        .List();
            }
        }



/// <summary>
        /// 处理条件
        /// </summary>
        /// <param name="qbc"></param>
        /// <param name="conditions"></param>
        private void prepareConditions(ICriteria qbc,params NCondition[] conditions)
        {
            if (qbc == null || conditions == null || conditions.Length == 0)
            {
                return;
            }
            foreach (NCondition condition in conditions)
            {
                switch (condition.Operate)
                {
                    case Operation.EQ:
                        qbc.Add(Expression.Eq(condition.PropertyName, condition.PropertyValue));
                        break;
                    case Operation.GT:
                        qbc.Add(Expression.Gt(condition.PropertyName, condition.PropertyValue));
                        break;
                    case Operation.LT:
                        qbc.Add(Expression.Lt(condition.PropertyName, condition.PropertyValue));
                        break;
                    case Operation.GE:
                        qbc.Add(Expression.Ge(condition.PropertyName, condition.PropertyValue));
                        break;
                    case Operation.LE:
                        qbc.Add(Expression.Le(condition.PropertyName, condition.PropertyValue));
                        break;
                    case Operation.NE:
                        qbc.Add(Expression.Not(
                                Expression.Eq(condition.PropertyName, condition.PropertyValue)
                            ));
                        break;
                    case Operation.BETWEEN:
                        qbc.Add(Expression.Between(
                            condition.PropertyName,
                            (condition.PropertyValue as Object[])[0],
                            (condition.PropertyValue as Object[])[1]
                          )
                        );
                        break;
                    case Operation.LIKE:
                        qbc.Add(Expression.Like(
                            condition.PropertyName,
                            condition.PropertyValue.ToString(),
                            MatchMode.Anywhere
                           )
                         );
                        break;
                    case Operation.IN:
                        qbc.Add(Expression.In(condition.PropertyName, condition.PropertyValue as object[]));
                        break;
                }
            }
        }

        /// <summary>
        /// 处理排序
        /// </summary>
        /// <param name="qbc"></param>
        /// <param name="orderFields"></param>
        private void prepareOrder(ICriteria qbc, params Wujy.ModelLibrary.Pagination.NOrder[] orderFields)
        {
            if (qbc == null || orderFields == null ||
                orderFields.Length == 0)
            {
                return;
            }

            foreach (Wujy.ModelLibrary.Pagination.NOrder order in orderFields)
            {
                qbc.AddOrder(
                    order.OrderType == Wujy.ModelLibrary.Pagination.NOrder.OrderDirection.ASC ?
                    Order.Asc(order.PropertyName) :
                    Order.Desc(order.PropertyName)
                );
            }
        }

View Code

2.4 UI层结合AspNetPager不带条件

        private void BindData()
        {
            ModelLibrary.Pagination.PageInfo pi = new ModelLibrary.Pagination.PageInfo(typeof(SchoolModel), this.AspNetPager2.CurrentPageIndex);
            new PaginationBLL().GetToPager(pi);
            this.AspNetPager2.RecordCount = pi.RecordCount;
            this.DataList1.DataSource = pi.List;
            this.DataList1.DataBind();
            this.Label1.Text = "当前第" + this.AspNetPager2.CurrentPageIndex + "页 总" + pi.PageCount + "页"; 
        }

        protected void AspNetPager2_PageChanged(object sender, EventArgs e)
        {
            BindData();
        }

效果图：

2.5 UI层结合AspNetPager带条件及排序

        private void BindData()
        {
            ModelLibrary.Pagination.NCondition[] nclist=new ModelLibrary.Pagination.NCondition[2];
            nclist[0] = new ModelLibrary.Pagination.NCondition("SchoolName", ModelLibrary.Pagination.Operation.LIKE, "踏浪帅");
            nclist[1] = new ModelLibrary.Pagination.NCondition("StudentNum", ModelLibrary.Pagination.Operation.GE, 390);
            ModelLibrary.Pagination.NOrder[] orderlist = new ModelLibrary.Pagination.NOrder[1];
            orderlist[0]=new ModelLibrary.Pagination.NOrder("StudentNum", ModelLibrary.Pagination.NOrder.OrderDirection.DESC);
            ModelLibrary.Pagination.PageInfo pi = new ModelLibrary.Pagination.PageInfo(typeof(SchoolModel), this.AspNetPager2.CurrentPageIndex,nclist,orderlist);
            new PaginationBLL().GetToPager(pi);
            this.AspNetPager2.RecordCount = pi.RecordCount;
            this.DataList1.DataSource = pi.List;
            this.DataList1.DataBind();
            this.Label1.Text = "当前第" + this.AspNetPager2.CurrentPageIndex + "页 总" + pi.PageCount + "页"; 
        }

        protected void AspNetPager2_PageChanged(object sender, EventArgs e)
        {
            BindData();
        }