Criteria API关联查询
如果说HQL查询还有需要了解点SQL语法知识，并不是完全彻底面向对象查询，
那么Criterial API就是完全面向对象的查询方式。
public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate)
{
return _session.CreateCriteria(typeof(Customer))
.Add(Restrictions.Eq("FirstName", "easy5")) //为本表查询添加查询参数
.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。我们使用投影可以很容易的组合我们需要的各种方法。
范例：
#region CriterialAPI 查询
public IList<Customer> GetEntitysByDateTimeCriterialApi(DateTime dateTime)
{
IList<Customer> result = null;
ISession session = _sessionManager.GetSession();
result = session.CreateCriteria(typeof(Customer))
.CreateCriteria("Orders")
.Add(Restrictions.Gt("OrderDate", dateTime))
.List<Customer>();
return result;
}
/// <summary>
/// 利用CreiteriaAPI进行的IN关键字的查询测试
/// </summary>
public IList<Customer> UseCreiteriaAPI_IN_Query()
{
ISession session = _sessionManager.GetSession();
// return session.CreateCriteria(typeof(Customer)).CreateCriteria("Orders").Add(Restrictions.Between("OrderDate", startDate, endDate))
// .List<Customer>();
object[] firstnames = { "zhang", "li" };
return session.CreateCriteria(typeof(Customer))
.Add(Restrictions.In("FirstName", firstnames))
.List<Customer>();
//return null;
}
/// <summary>
/// 利用CreiteriaAPI进行的Or关键字的查询测试
/// </summary>
public IList<Customer> UseCreiteriaAPI_Or_Query()
{
ISession session = _sessionManager.GetSession();
String[] firstnames = { "zhang", "li" };
return session.CreateCriteria(typeof(Customer))
.Add(Restrictions.Or(Restrictions.Eq("FirstName", "zhang"), Restrictions.Eq("FirstName", "li")))
.List<Customer>();
}
//CriteriaAPI的预过滤测试
public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate)
{
ISession session = _sessionManager.GetSession();
return session.CreateCriteria(typeof(Customer))
.CreateCriteria("Orders")
.Add(Restrictions.Gt("OrderDate", orderDate))
.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer()) //Distinct--过滤重复项
.List<Customer>();
}
//CriteriaAPI的Projection投影测试
public IList<Customer> UseCriteriaAPI_GetDistinctCustomers(DateTime orderDate)
{
ISession session = _sessionManager.GetSession();
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>();
}
//CriteriaAPI的Projection投影测试
public IList<Object[]> UseCriteriaAPI_GetDistinctCustomers2()
{
ISession session = _sessionManager.GetSession();
return session.CreateCriteria(typeof(Customer))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("FirstName")).Add(Projections.Property("LastName")))
.List<Object[]>();
}
#endregion
动态查询---关联查询：
public IList<Customer> UseCriteriaApiAdvQuery(String firstName,
String lastName,
DateTime dateTime)
{
ICriteria criteria = _session.CreateCriteria("Customer");
if (null != firstName)
{
criteria.Add(Restrictions.Eq("FirstName", firstName));
}
if (null != lastName)
{
criteria.Add(Restrictions.Eq("LastName", lastName));
} if (null != dateTime)
{
//关联查询Order
// 注意：不是criteria.CreateCriteria("Order")，如果是，会抛异常：
//{"could not resolve property: Order of: Model.Customer"}
//而是criteria.CreateCriteria("Orders")，Orders是Customer类在关联的Order类的属性
criteria.CreateCriteria("Orders").Add(Restrictions.Eq("OrderDate", dateTime));
} return criteria.List<Customer>();
} 测试代码： [TestMethod]
public void TestUseCriteriaApiADvQuery()
{
CustomerService customerService = new CustomerService();
OrderService orderService = new OrderService(); Customer customer = new Customer()
{
FirstName = "Test",
LastName = "TestUseCriteriaApiADvQuery",
Age =
}; 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.UseCriteriaApiAdvQuery(customer.FirstName,
customer.LastName, order1.OrderDate); Assert.IsTrue(customers.Count > );
}
动态查询---模糊查询：
#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 =
};
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 > );
}
#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 =
}; Customer customer2 = new Customer()
{
FirstName = "Test",
LastName = "TestUseCriteriaApiLikeQuery2",
Age =
}; 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[].LastName== customer2.LastName);
}