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","*"));
IList list = criteria.List();
Eq是Equal的缩写,意思是添加一个查询表达式,Person.Name = “*”
对应HQL就是:from Person p where p.Name=”*”
1.1.1 经常还有一个实体里面有多条件查询,也可以如下面写法,简单地new出一个person对象,然后填充其属性即可,不用再去构造那丑陋的条件判断语句了:
ICriteria criteria = session.CreateCriteria(typeof(Person));
Person person = new Person();
person.Name = "*";
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类,其它类的代码大家直接查看源代码;
2.3 在DAL层里面创建一个基类,把分页的代码写在这里,下面就例出比较重要的几个代码:
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();
}
效果图:
感谢您的阅读,坚持每天进步一点点,离成功就更新一步;希望文章对您有所帮助;源代码下载
转载至:http://www.cnblogs.com/wujy/p/3589548.html