Criteria API关联查询
如果说HQL查询还有需要了解点SQL语法知识，并不是完全彻底面向对象查询，
那么Criterial API就是完全面向对象的查询方式。
1 public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate) 2 { 3 return _session.CreateCriteria(typeof(Customer))
.Add(Restrictions.Eq("FirstName", "easy5")) //为本表查询添加查询参数 4 .CreateCriteria("Orders") //关联查询Order表开始，注意：“Oreders”是类Customer中关联到Order类的属性
.Add(Restrictions.Gt("OrderDate", orderDate))//OrderDate是Order类中的属性，不是数据库表Order的列名，如果是列名就不是面向对象编程了
.List<Customer>();
}
我们使用CreateCriteria()在关联之间导航，很容易地在实体之间指定约束。
这里第二个CreateCriteria()返回一个ICriteria的新实例，并指向Orders实体的元素。
在查询中子对象使用子CreateCriteria语句，这是因为实体之间的关联我们在映射文件中已经定义好了。
还有一种方法使用CreateAlias()不会创建ICriteria的新实例。
预过滤
使用ICriteria接口的SetResultTransformer(IResultTransformer resultTransformer)方法返回满足特定条件的Customer。上面例子中使用条件查询，观察其生成的SQL语句并没有distinct，这时可以使用NHibernate.Transform命名空间中的方法或者使用NHibernate提供的NHibernate.CriteriaUtil.RootEntity、NHibernate.CriteriaUtil.DistinctRootEntity、NHibernate.CriteriaUtil.AliasToEntityMap静态方法实现预过滤的作用。那么上面的查询应该修改为：
public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate)
{
return _session.CreateCriteria(typeof(Customer))
.CreateCriteria("Orders")
.Add(Restrictions.Gt("OrderDate", orderDate))
.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer()) //过滤重复项
.List<Customer>();
}
这个例子从转换结果集的角度实现了我们想要的效果。
投影
调用SetProjection()方法可以实现应用投影到一个查询中。NHibernate.Criterion.Projections是Projection的实例工厂，Projections提供了非常多的方法，看看下面的截图
现在可以条件查询提供的投影来完成上面同样的目的：
public IList<Customer> UseCriteriaAPI_GetDistinctCustomers(DateTime orderDate)
{
IList<int> ids = _session.CreateCriteria(typeof(Customer))
.SetProjection(Projections.Distinct(Projections.ProjectionList()
.Add(Projections.Property("CustomerId"))
)
)
.CreateCriteria("Orders")
.Add(Restrictions.Gt("OrderDate", orderDate))
.List<int>();
return _session.CreateCriteria(typeof(Customer))
.Add(Restrictions.In("CustomerId", ids.ToArray<int>()))
.List<Customer>();
}
我们可以添加若干的投影到投影列表中，例如这个例子我添加一个CustomerId属性值到投影列表中，这个列表中的所有属性值都设置了Distinct投影，第一句返回订单时间在orderDate之后所有顾客Distinct的CustomerId，第二句根据返回的CustomerId查询顾客列表。达到上面的目的。这时发现其生成的SQL语句中有distinct。我们使用投影可以很容易的组合我们需要的各种方法。
范例：
1 #region CriterialAPI 查询 2 3 public IList<Customer> GetEntitysByDateTimeCriterialApi(DateTime dateTime) 4 { 5 IList<Customer> result = null; 6 ISession session = _sessionManager.GetSession(); 7 8 result = session.CreateCriteria(typeof(Customer)) 9 .CreateCriteria("Orders") 10 .Add(Restrictions.Gt("OrderDate", dateTime)) 11 .List<Customer>(); 12 13 return result; 14 } 15 16 17 /// <summary> 18 /// 利用CreiteriaAPI进行的IN关键字的查询测试 19 /// </summary> 20 public IList<Customer> UseCreiteriaAPI_IN_Query() 21 { 22 ISession session = _sessionManager.GetSession(); 23 // return session.CreateCriteria(typeof(Customer)).CreateCriteria("Orders").Add(Restrictions.Between("OrderDate", startDate, endDate)) 24 // .List<Customer>(); 25 26 object[] firstnames = { "zhang", "li" }; 27 return session.CreateCriteria(typeof(Customer)) 28 .Add(Restrictions.In("FirstName", firstnames)) 29 .List<Customer>(); 30 31 //return null; 32 } 33 34 /// <summary> 35 /// 利用CreiteriaAPI进行的Or关键字的查询测试 36 /// </summary> 37 public IList<Customer> UseCreiteriaAPI_Or_Query() 38 { 39 ISession session = _sessionManager.GetSession(); 40 String[] firstnames = { "zhang", "li" }; 41 return session.CreateCriteria(typeof(Customer)) 42 .Add(Restrictions.Or(Restrictions.Eq("FirstName", "zhang"), Restrictions.Eq("FirstName", "li"))) 43 .List<Customer>(); 44 } 45 46 //CriteriaAPI的预过滤测试 47 public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate) 48 { 49 ISession session = _sessionManager.GetSession(); 50 51 return session.CreateCriteria(typeof(Customer)) 52 .CreateCriteria("Orders") 53 .Add(Restrictions.Gt("OrderDate", orderDate)) 54 .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer()) //Distinct--过滤重复项 55 56 .List<Customer>(); 57 } 58 59 //CriteriaAPI的Projection投影测试 60 public IList<Customer> UseCriteriaAPI_GetDistinctCustomers(DateTime orderDate) 61 { 62 ISession session = _sessionManager.GetSession(); 63 64 IList<int> ids = session.CreateCriteria(typeof(Customer)) 65 .SetProjection(Projections.Distinct(Projections.ProjectionList().Add(Projections.Property("CustomerId")) 66 )) 67 .CreateCriteria("Orders") 68 .Add(Restrictions.Gt("OrderDate", orderDate)) 69 .List<int>(); 70 71 72 return session.CreateCriteria(typeof(Customer)) 73 .Add(Restrictions.In("CustomerId", ids.ToArray<int>())) 74 .List<Customer>(); 75 } 76 77 //CriteriaAPI的Projection投影测试 78 public IList<Object[]> UseCriteriaAPI_GetDistinctCustomers2() 79 { 80 ISession session = _sessionManager.GetSession(); 81 82 return session.CreateCriteria(typeof(Customer)) 83 .SetProjection(Projections.ProjectionList() 84 .Add(Projections.Property("FirstName")).Add(Projections.Property("LastName"))) 85 .List<Object[]>(); 86 87 88 } 89 90 #endregion
动态查询---关联查询：
1 public IList<Customer> UseCriteriaApiAdvQuery(String firstName, 2 String lastName, 3 DateTime dateTime) 4 { 5 ICriteria criteria = _session.CreateCriteria("Customer"); 6 if (null != firstName) 7 { 8 criteria.Add(Restrictions.Eq("FirstName", firstName)); 9 } 10 if (null != lastName) 11 { 12 criteria.Add(Restrictions.Eq("LastName", lastName)); 13 } 14 15 if (null != dateTime) 16 { 17 //关联查询Order 18 // 注意：不是criteria.CreateCriteria("Order")，如果是，会抛异常： 19 //{"could not resolve property: Order of: Model.Customer"} 20 //而是criteria.CreateCriteria("Orders")，Orders是Customer类在关联的Order类的属性 21 criteria.CreateCriteria("Orders").Add(Restrictions.Eq("OrderDate", dateTime)); 22 } 23 24 return criteria.List<Customer>(); 25 } 26 27 测试代码： 28 29 [TestMethod] 30 public void TestUseCriteriaApiADvQuery() 31 { 32 CustomerService customerService = new CustomerService(); 33 OrderService orderService = new OrderService(); 34 35 Customer customer = new Customer() 36 { 37 FirstName = "Test", 38 LastName = "TestUseCriteriaApiADvQuery", 39 Age = 111 40 }; 41 42 Order order1 = new Order() 43 { 44 OrderDate = DateTime.Now, 45 Customer = customer 46 }; 47 48 customer.Orders.Add(order1); 49 customerService.Add(customer); 50 51 Assert.IsNotNull(customerService.Find(customer.CustomerId)); 52 Assert.IsNotNull(orderService.Find(order1.OrderId)); 53 54 55 IList<Customer> customers = 56 customerService.UseCriteriaApiAdvQuery(customer.FirstName, 57 customer.LastName, order1.OrderDate); 58 59 Assert.IsTrue(customers.Count > 0); 60 }
动态查询---模糊查询：
#region CriterialAPI 动态+模糊查询 public IList<Customer> UseCriteriaApiLikeQuery(String firstName, String lastName) { ICriteria criteria = _session.CreateCriteria("Customer"); if (null != firstName) { //方式一：用原生的匹配字符,如：% criteria.Add(Restrictions.Like("FirstName", "%" + firstName +"%")); } if (null != lastName) { //方式二：用MatchMode criteria.Add(Restrictions.Like("LastName", lastName, MatchMode.Anywhere)); } return criteria.List<Customer>(); } #endregion 测试代码： #region 测试CriteriaAPI查询--动态 + 模糊查询 [TestMethod] public void TestUseCriteriaApiLikeQuery() { CustomerService customerService = new CustomerService(); OrderService orderService = new OrderService(); Customer customer = new Customer() { FirstName = "Test", LastName = "TestUseCriteriaApiLikeQuery", Age = 111 }; Order order1 = new Order() { OrderDate = DateTime.Now, Customer = customer }; customer.Orders.Add(order1); customerService.Add(customer); Assert.IsNotNull(customerService.Find(customer.CustomerId)); Assert.IsNotNull(orderService.Find(order1.OrderId)); IList<Customer> customers = customerService.UseCriteriaApiLikeQuery(null, "CriteriaApiLike"); Assert.IsTrue(customers.Count > 0); } #endregion
排序：
/// <summary> /// 模糊查询+排序 /// </summary> /// <param name="firstName"></param> /// <param name="lastName"></param> /// <returns></returns> public IList<Customer> UseCriteriaApiLikeQueryAndDescOrder(String firstName, String lastName) { ICriteria criteria = _session.CreateCriteria("Customer"); if (null != firstName) { //方式一：用原生的匹配字符,如：% criteria.Add(Restrictions.Like("FirstName", "%" + firstName +"%")); } if (null != lastName) { //方式二：用MatchMode criteria.Add(Restrictions.Like("LastName", lastName, MatchMode.Anywhere)) .AddOrder(NHibernate.Criterion.Order.Desc("CustomerId")); } return criteria.List<Customer>(); } #endregion 测试代码： [TestMethod] public void TestUseCriteriaApiLikeQueryAndDescOrder() { CustomerService customerService = new CustomerService(); OrderService orderService = new OrderService(); Customer customer1 = new Customer() { FirstName = "Test", LastName = "TestUseCriteriaApiLikeQuery1", Age = 111 }; Customer customer2 = new Customer() { FirstName = "Test", LastName = "TestUseCriteriaApiLikeQuery2", Age = 111 }; customerService.Add(customer1); customerService.Add(customer2); Assert.IsNotNull(customerService.Find(customer1.CustomerId)); Assert.IsNotNull(customerService.Find(customer2.CustomerId)); IList<Customer> customers = customerService.UseCriteriaApiLikeQueryAndDescOrder(null, "CriteriaApiLike"); Assert.IsTrue(customer1.CustomerId < customer2.CustomerId); Assert.IsTrue(customers[0].LastName== customer2.LastName); }