NHibernate中的QueryOver查询(No.1)

在NHibernate 3.0的时候开始,NHibernate引入了新的查询API —— QueryOver。这中查询是构建在NHibernate原有的ICriteria API之上的,支持Lambda表达式和扩展方法。

实例场景:

客户(Customer)与订单(Order),一个客户可以下多个订单。

 实体基类: Entity

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MyWorkShop.Model.Entities
{
    public abstract class Entity<TId>
    {
        public virtual TId Id { get; protected set; }
    }
}

客户类:Customer,映射的数据表为MyWorkShop_Customer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MyWorkShop.Model.Entities
{
    public class Customer : Entity<int>
    {
        public virtual string Name { get; set; }
        public virtual string Address { get; set; }
        public virtual string Phone { get; set; }
    }
}

订单类:Order,映射的数据表为MyWorkShop_Order

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MyWorkShop.Model.Entities
{
    //Guid:主键
    public class Order : Entity<Guid>
    {
        //下单客户
        public virtual Customer Customer { get; set; }
        //下单时间
        public virtual DateTime OrderedDateTime { get; set; }
        //金额
        public virtual Decimal? Amount { get; set; }
    }
}

查询场景

筛选数据(Restriction)
根据客户姓名查找客户,假设客户姓名唯一

public Customer GetByName(string customerName)
{
    Customer entity = null;

    using (var session = NHibernateSession)
    using (var transaction = session.BeginTransaction())
    {

        entity = session.QueryOver<Customer>()
            .Where(c => c.Name == customerName)
            .SingleOrDefault();

        transaction.Commit();
    }
    return entity;
}

等价SQL:

SELECT this_.Id as Id5_0_, this_.Name as Name5_0_, this_.Address as Address5_0_, this_.Phone as Phone5_0_ FROM MyWorkShop_Customer this_ WHERE this_.Name = @p0;@p0 = 'Name' [Type: String (50)]

代码说明:
1. 筛选条件调用Where方法,使用Lambda表达式“c => c.Name == customerName”,这样就消除了ICriteria的字段名字符串硬编码的问题;
2. 返回单个值调用SingleOrDefault(),若查询结果不唯一则抛出异常NHibernate.NonUniqueResultException。

 根据客户地址查找多个客户

public IEnumerable<Customer> GetByAddress(string address)
 {
     IEnumerable<Customer> list = null;
 
     using (var session = NHibernateSession)
     using (var transaction = session.BeginTransaction())
     {
         list = session.QueryOver<Customer>()
             .Where(c => c.Address == address)
             .List();
 
         transaction.Commit();
     }
 
     return list;
 }

等效SQL:

SELECT this_.Id as Id5_0_, this_.Name as Name5_0_, this_.Address as Address5_0_, this_.Phone as Phone5_0_ FROM MyWorkShop_Customer this_ WHERE this_.Address = @p0;@p0 = 'Address' [Type: String (100)]

代码说明:
1. 查询多条数据调用List()方法。 

根据客户姓名模糊查找客户

public IEnumerable<Customer> GetByLikeName(string likeName)
 {
     IEnumerable<Customer> list = null;
 
     using (var session = NHibernateSession)
     using (var transaction = session.BeginTransaction())
     {
         list = session.QueryOver<Customer>()
             .WhereRestrictionOn(o => o.Name)
             .IsLike(likeName, MatchMode.Anywhere)
             .List();
 
         transaction.Commit();
     }
 
     return list;
 }

等效SQL

SELECT this_.Id as Id5_0_, this_.Name as Name5_0_, this_.Address as Address5_0_, this_.Phone as Phone5_0_ FROM MyWorkShop_Customer this_ WHERE this_.Name like @p0;@p0 = '%e%' [Type: String (50)]

代码说明:
1. 对于某些SQL函数与操作符(比如like、between...and...),没有直接对应的Lambda表达式,需要先使用WhereRestrictionOn方法指定筛选条件的列,然后再调用相应的方法指定筛选条件;
2. IsLike方法指定字符串匹配查找。

查找金额在指定范围内的订单

public IEnumerable<Order> GetByAmount(decimal minAmount, decimal maxAmount)
 {
     IEnumerable<Order> list = null;
 
     using (var session = NHibernateSession)
     using (var transaction = session.BeginTransaction())
     {
         list = session.QueryOver<Order>()
             .Where(o => o.Amount >= minAmount)
             .And(o => o.Amount <= maxAmount)
             .OrderBy(o => o.Amount)
             .Desc
             .List();
 
         transaction.Commit();
     }
 
     return list;
 }

等效SQL:

SELECT this_.Id as Id8_0_, this_.CustomerId as CustomerId8_0_, this_.OrderedDateTime as OrderedD3_8_0_, this_.Amount as Amount8_0_ FROM MyWorkShop_Order this_ WHERE this_.Amount >= @p0 and this_.Amount <= @p1 ORDER BY this_.Amount desc;@p0 = 100 [Type: Decimal (0)], @p1 = 200 [Type: Decimal (0)]

代码说明:
1. 多个条件可使用Where...And...逐个指定,也可以在一个Where方法中指定,比如上面的条件可以写成Where(o => o.Amount >= minAmount && o.Amount <= maxAmount);
2. 排序使用OrderBy,升序降序使用Asc与Desc。

连接(Join)

内连接:根据客户姓名查找订单

public IEnumerable<Order> GetByCustomerName(string customerName)
 {
     IEnumerable<Order> list = null;
 
     using (var session = NHibernateSession)
     using (var transaction = session.BeginTransaction())
     {
         list = session.QueryOver<Order>()
             .OrderBy(o=>o.Amount).Desc
             .Inner
             .JoinQueryOver<Customer>(o => o.Customer)
             .Where(c => c.Name == customerName)            
             .List();
 
         transaction.Commit();
     }
 
     return list;
 } 

等效SQL:

SELECT this_.Id as Id8_1_, this_.CustomerId as CustomerId8_1_, this_.OrderedDateTime as OrderedD3_8_1_, this_.Amount as Amount8_1_, customer1_.Id as Id9_0_, customer1_.Name as Name9_0_, customer1_.Address as Address9_0_, customer1_.Phone as Phone9_0_ FROM MyWorkShop_Order this_ inner join MyWorkShop_Customer customer1_ on this_.CustomerId=customer1_.Id WHERE customer1_.Name = @p0 ORDER BY this_.Amount desc;@p0 = 'Name' [Type: String (50)

代码说明:
1. Inner.JoinQueryOver指定内连接,如果省略Inner仅写JoinQueryOver默认就是内连接;
2. Left、.Right则分别为左外连接、右外连接

使用别名进行内连接:根据客户姓名查找订单

public IEnumerable<Order> GetByCustomerNameViaAlias(string customerName)
 {
     //定义用于内连接的别名变量,该变量必须赋值为null
     Customer customer = null;
 
     IEnumerable<Order> list = null;
 
     using (var session = NHibernateSession)
     using (var transaction = session.BeginTransaction())
     {
         list = session.QueryOver<Order>()
             .JoinAlias(o => o.Customer, () => customer) //指定别名customer
             .Where(() => customer.Name == customerName)
             .List();
 
         transaction.Commit();
     }
 
     return list;
 }

等效SQL:

SELECT this_.Id as Id8_1_, this_.CustomerId as CustomerId8_1_, this_.OrderedDateTime as OrderedD3_8_1_, this_.Amount as Amount8_1_, customer1_.Id as Id9_0_, customer1_.Name as Name9_0_, customer1_.Address as Address9_0_, customer1_.Phone as Phone9_0_ FROM MyWorkShop_Order this_ inner join MyWorkShop_Customer customer1_ on this_.CustomerId=customer1_.Id WHERE customer1_.Name = @p0 ORDER BY this_.Amount desc;@p0 = 'Name' [Type: String (50)]

代码说明:
1. 可以通过.Inner.JoinQueryOver来显式进行内连接,也可以通过.JoinAlias创建连接别名进行连接;
2. 连接别名变量在QueryOver使用之前定义,并且必须赋null值。

与ICriteria API相比,个人认为QueryOver并不见得会提高代码的可读性,但QueryOver解决了
ICriteria API字符串硬编码的问题,从而减少代码输入的错误,大大提高了代码重构的能力,因此用QueryOver取代ICriteria API是值得的。

Friday, June 01, 2012 | NHibernate

文章评论

No comments posted yet.

发表评论

Please add 8 and 1 and type the answer here:

关于博主

  一枚成分复杂的网络IT分子,常年游弋于电子商务,属于互联网行业分类中的杂牌军。当前正在待业中...