企业应用架构模式——QueryObject模式

场景

比如我们有个Employee实体，属性有 EmployeeID(雇员id)、NationalIDNumber(雇员标志号，如身份证)、ManagerID(雇员上司id)、Title(职位)、MaritalStatus(婚姻状态)、Gender(性别)、HireDate(雇佣日期)、SalariedFlag(工作性质，计时或月薪)。我们写数据库层时经常会遇到这样的需求：

查询标志号为"001”的雇员

public class EmployeeRepository : IRepository<Employee>
   {
      public Employee Find(object id)
      {}
   }

查询计时或月薪的雇员

public class EmployeeRepository : IRepository<Employee>
   {
      public IEnumerable<Employee> FindSalariedEmployee(SalariedFlag salariedFlag)
      {}
   }

可以看出只要我们有通过对象属性来查询对象，就要在相应的数据层增加对应的Findxxx方法，不过这只是单一的查询，如果存在组合条件的查询，Employee仓储中会充斥大量的检索方法。

QueryObject模式

QueryObject(查询对象)是一个解释器，也就是说，他是由多个对象组成的结构，该结构可以自身转化为一个sql查询。可以通过引用类的和属性来创建对应数据库表和列的查询。

查询对象的好处

1、完全将底层数据库的查询语言抽象出来，因此将数据库持久化和检索的基础设施关注点从业务层中分离出来。

2、对于多个数据库，通过设计查询对象，根据运行的数据库来产生不同的sql，屏蔽了各种不同sql语法中的差别。

3、消除对数据库的查询冗余，如果在先前的会话中运行过相同的查询，可以使用查询对象从标志映射中选取对象。

QueryObject 模型 类图

实现QueryObject 模式

首先创建一个枚举 CriteriaOperator 用来处理查询条件的映射

public enum CriteriaOperator
{
    Equal,// =
    LessThanOrEqual,// <=
    NotApplicable, // ≠
    LessThan, // <
    GreaterThan, // >
    GreaterThanOrEqual, // >=
    Like // %
}

创建查询过滤器的类Criterion

/// <summary>
/// 查询过滤器
/// </summary>
public class Criterion
{
    private string _propertyName;//实体属性
    private object _value;//进行比较的值
    private CriteriaOperator _criteriaOperator;//比较操作符

    public Criterion(string propertyName, object value, CriteriaOperator criteriaOperator)
    {
        _propertyName = propertyName;
        _value = value;
        _criteriaOperator = criteriaOperator;
    }

    public string PropertyName
    {
        get { return _propertyName; }
    }

    public object Value
    {
        get { return _value; }
    }
    public CriteriaOperator CriteriaOperator
    {
        get { return _criteriaOperator; }
    }

}

创建一个用于表示排序属性的类OrderByClause

public class OrderByClause
{
    public string PropertyName { get; set; }//属性名对应数据库表的列名

    public bool Desc { get; set; }//desc or asc
}

创建另一个枚举QueryOperator，用来表示条件的连字符 and 或者 or

public enum QueryOperator
{
    And,
    Or
}

有时候复杂的查询对象难以构建，我们会通过存储过程或视图来处理此种情况，需要构建一个枚举QueryName用来指示是存储过程(视图)还是构建动态的sql语句

public enum QueryName
{
    Dynamic = 0, //动态创建
    RetrieveOrdersUsingAComplexQuery = 1//使用已经创建好了的存储过程、视图、特别是查询比较复杂时使用存储过程
}

最后创建Query类，用来组合查询条件(Criterion)、查询条件连字符(QueryOperator)，排序(OrderByClause)

public class Query
{
    private QueryName _name;

    private IList<Criterion> _criteria;//查询条件

    public Query():this(QueryName.Dynamic,new List<Criterion>())
    { }
    public Query(QueryName name, IList<Criterion> criteria)
    {
        _name = name;
        _criteria = criteria;
    }

    public QueryName Name
    {
        get { return _name; }
    }

    /// <summary>
    /// 判断是存储过程还是动态sql
    /// </summary>
    /// <returns></returns>
    public bool IsNamedQuery()
    {
        return Name != QueryName.Dynamic;
    }

    public IEnumerable<Criterion> Criteria
    {
        get { return _criteria; }
    }

    public void Add(Criterion criterion)
    {
        if (!IsNamedQuery())
        {
            _criteria.Add(criterion);
        }
        else
        {
            throw new ApplicationException("You cannot add additional criteria to named queries");
        }
    }
    //查询条件连字符
    public QueryOperator QueryOperator
    {
        get;
        set;
    }
    //排序列
    public OrderByClause OrderByProperty
    {
        get;
        set;
    }

}

使用QueryObject模式

仓储层(Repository)接口

public interface IRepository<T>
{
    int Add(T entity);
    int Save(T entity);
    void Delete(T entity);
    T Find(object id);
    T Find(Query query);
    IEnumerable<T> FindBy(Query query);
    IEnumerable<T> FindBy(Query query, int pageSize, int pageCount);
}

创建一个翻译器，将查询对象翻译成sql语句

public static class AdmissionTicketOrderQueryTranslator
{
    private static readonly string baseSelectQuery = "SELECT * FROM AdmissionTicketOrder";

    public static void TranslateInfo(this Query query, SqlCommand cmd)
        {
            if (query.IsNamedQuery())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = query.Name.ToString();
                foreach (Criterion criteria in query.Criteria)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + criteria.PropertyName, criteria.Value));
                }
            }
            else
            {
                StringBuilder sqlQuery = new StringBuilder();
                sqlQuery.Append(baseSelectQuery);
                bool _isNotfirstFilterClause = false;

                if (query.Criteria.Count()>0)
                {
                    sqlQuery.Append(" WHERE ");
                }

                foreach (Criterion criterion in query.Criteria)
                {
                    if (_isNotfirstFilterClause)
                    {
                        sqlQuery.Append(GetQueryOperator(query));
                    }
                    sqlQuery.Append(AddFilterClauseFrom(criterion));
                    cmd.Parameters.Add(new SqlParameter("@" + criterion.PropertyName, criterion.Value));
                    _isNotfirstFilterClause = true;
                }
                sqlQuery.Append(GenerateOrderByClauseFrom(query.OrderByProperty));
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlQuery.ToString();
            }
        }

    private static string GenerateOrderByClauseFrom(OrderByClause orderByClause)
        {
            return String.Format(" ORDER BY {0} {1}",
                FindTableColumnFor(orderByClause.PropertyName), orderByClause.Desc ? "DESC" : "ASC");
        }

    /// <summary>
        ///
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
    private static string GetQueryOperator(Query query)
        {
            if (query.QueryOperator==QueryOperator.And)
            {
                return " AND ";
            }
            else
            {
                return " OR ";
            }
        }

    private static string AddFilterClauseFrom(Criterion criterion)
        {
            return string.Format("{0} {1} @{2} ", FindTableColumnFor(criterion.PropertyName), FindSQLOperatorFor(criterion.CriteriaOperator), criterion.PropertyName);
        }

    private static string FindSQLOperatorFor(CriteriaOperator criteriaOperator)
        {
            switch (criteriaOperator)
            {
                case CriteriaOperator.Equal:
                    return "=";
                case CriteriaOperator.LessThanOrEqual:
                    return "<=";
                case CriteriaOperator.NotApplicable:
                    return "<>";
                case CriteriaOperator.LessThan:
                    return "<";
                case CriteriaOperator.GreaterThan:
                    return ">";
                case CriteriaOperator.GreaterThanOrEqual:
                    return ">=";
                default:
                    throw new ApplicationException("No operator defined.");
            }
        }

    private static string FindTableColumnFor(string propertyName)
        {
            switch (propertyName)
            {
                case "ID":
                    return "ID";
                case "OrderID":
                    return "OrderID";
                case "TicketId":
                    return "TicketId";
                case "OrderUserName":
                    return "OrderUserName";
                case "OrderUserSex":
                    return "OrderUserSex";
                case "OrderUserPhone":
                    return "OrderUserPhone";
                case "ProviderContactName":
                    return "ProviderContactName";
                case "TicetUseDateTime":
                    return "TicetUseDateTime";
                case "SalePrice":
                    return "SalePrice";
                case "BuyPrice":
                    return "BuyPrice";
                case "BuyAmount":
                    return "BuyAmount";
                case "TicketType":
                    return "TicketType";
                default:
                    throw new ApplicationException("No column defined for this property.");
            }
        }

}

仓储实现层

public class AdmissionTicketOrderRepository : IRepository<AdmissionTicketOrder>
{
    private SqlHelper _sqlHelper;

    public AdmissionTicketOrderRepository(SqlHelper sqlHelper)
    {
        _sqlHelper = sqlHelper;
    }

    private static readonly string insertOrderExtendSql = "sp_InsertOrUpdateAdmissionTicketOrder";
    public int Add(AdmissionTicketOrder entity)
        {
            using (var cmd = _sqlHelper.PrepareStoredSqlCommand(insertOrderExtendSql))
            {
                cmd.AddParam("@ID", entity.ID);
                cmd.AddParam("@OrderID", entity.OrderID);
                cmd.AddParam("@TicketId", entity.TicketId);
                cmd.AddParam("@OrderUserName", entity.OrderUserName);
                cmd.AddParam("@OrderUserSex", entity.OrderUserSex);
                cmd.AddParam("@OrderUserPhone", entity.OrderUserPhone);
                cmd.AddParam("@ProviderContactName", entity.ProviderContactName);
                cmd.AddParam("@ProviderContactPhone", entity.ProviderContactPhone);
                cmd.AddParam("@TicetUseDateTime", entity.TicetUseDateTime);
                cmd.AddParam("@IsNeed", entity.IsNeed);
                cmd.AddParam("@BillAddress", entity.BillAddress);
                cmd.AddParam("@BuyPrice", entity.BuyPrice);
                cmd.AddParam("@SalePrice", entity.SalePrice);
                cmd.AddParam("@BuyAmount", entity.BuyAmount);
                cmd.AddParam("@GetTicketAddress", entity.GetTicketAddress);
                cmd.AddParam("@TicketType", entity.TicketType);
                var parm = cmd.AddReturnValueParameter("ReturnValue");
                cmd.ExecuteNonQuery();
                return (int)parm.Value;
            }
        }

    public int Save(AdmissionTicketOrder entity)
        {
            return Add(entity);
        }

    private static readonly string deleteOrder = "DELETE AdmissionTicketOrder WHERE ID=@Id";
    public void Delete(AdmissionTicketOrder entity)
        {
            using (var cmd = _sqlHelper.PrepareTextSqlCommand(deleteOrder))
            {
                cmd.AddParam("@Id", entity.ID);
                cmd.ExecuteNonQuery();
            }
        }

    private static readonly string selectOrder = "SELECT * FROM AdmissionTicketOrder WHERE ID=@Id";
    public AdmissionTicketOrder Find(object id)
        {
            using (var cmd = _sqlHelper.PrepareTextSqlCommand(selectOrder))
            {
                cmd.AddParam("@Id", id);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return (new AdmissionTicketOrder
                        {
                            Id = reader.GetValue<int>("Id"),
                            OrderID = reader.GetValue<int>("OrderID"),
                            TicketId=reader.GetValue<int>("TicketId"),
                            OrderUserName=reader.GetValue<string>("OrderUserName"),
                            OrderUserSex=reader.GetValue<int>("OrderUserSex"),
                            OrderUserPhone = reader.GetValue<string>("OrderUserPhone"),
                            ProviderContactName=reader.GetValue<string>("ProviderContactName"),
                            ProviderContactPhone=reader.GetValue<string>("ProviderContactPhone"),
                            TicetUseDateTime=reader.GetValue<DateTime>("TicetUseDateTime"),
                            IsNeed=reader.GetValue<bool>("IsNeed"),
                            BillAddress=reader.GetValue<string>("BillAddress"),
                            SalePrice = reader.GetValue<decimal>("SalePrice"),
                            BuyPrice = reader.GetValue<decimal>("BuyPrice"),
                            BuyAmount=reader.GetValue<decimal>("BuyAmount"),
                            GetTicketAddress = reader.GetValue<string>("GetTicketAddress"),
                            TicketType=reader.GetValue<string>("TicketType")
                        });
                    }
                    return null;
                }
            }
        }

    public AdmissionTicketOrder Find(Query query)
        {
            SqlCommand cmd = _sqlHelper.PrepareSqlCommand();
            query.TranslateInfo(cmd);
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    return (new AdmissionTicketOrder
                    {
                        Id = reader.GetValue<int>("Id"),
                        OrderID = reader.GetValue<int>("OrderID"),
                        TicketId = reader.GetValue<int>("TicketId"),
                        OrderUserName = reader.GetValue<string>("OrderUserName"),
                        OrderUserSex = reader.GetValue<int>("OrderUserSex"),
                        OrderUserPhone = reader.GetValue<string>("OrderUserPhone"),
                        ProviderContactName = reader.GetValue<string>("ProviderContactName"),
                        ProviderContactPhone = reader.GetValue<string>("ProviderContactPhone"),
                        TicetUseDateTime = reader.GetValue<DateTime>("TicetUseDateTime"),
                        IsNeed = reader.GetValue<bool>("IsNeed"),
                        BillAddress = reader.GetValue<string>("BillAddress"),
                        SalePrice = reader.GetValue<decimal>("SalePrice"),
                        BuyPrice = reader.GetValue<decimal>("BuyPrice"),
                        BuyAmount = reader.GetValue<decimal>("BuyAmount"),
                        GetTicketAddress = reader.GetValue<string>("GetTicketAddress"),
                        TicketType = reader.GetValue<string>("TicketType")
                    });
                }
                return null;
            }
        }

    public IEnumerable<AdmissionTicketOrder> FindBy(Query query)
        {
            IList<AdmissionTicketOrder> admissionTicketOrders = new List<AdmissionTicketOrder>();
            SqlCommand cmd = _sqlHelper.PrepareSqlCommand();
            query.TranslateInfo(cmd);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    admissionTicketOrders.Add(
                        new AdmissionTicketOrder
                        {
                            Id = reader.GetValue<int>("Id"),
                            OrderID = reader.GetValue<int>("OrderID"),
                            TicketId = reader.GetValue<int>("TicketId"),
                            OrderUserName = reader.GetValue<string>("OrderUserName"),
                            OrderUserSex = reader.GetValue<int>("OrderUserSex"),
                            OrderUserPhone = reader.GetValue<string>("OrderUserPhone"),
                            ProviderContactName = reader.GetValue<string>("ProviderContactName"),
                            ProviderContactPhone = reader.GetValue<string>("ProviderContactPhone"),
                            TicetUseDateTime = reader.GetValue<DateTime>("TicetUseDateTime"),
                            IsNeed = reader.GetValue<bool>("IsNeed"),
                            BillAddress = reader.GetValue<string>("BillAddress"),
                            SalePrice = reader.GetValue<decimal>("SalePrice"),
                            BuyPrice = reader.GetValue<decimal>("BuyPrice"),
                            BuyAmount = reader.GetValue<decimal>("BuyAmount"),
                            GetTicketAddress = reader.GetValue<string>("GetTicketAddress"),
                            TicketType = reader.GetValue<string>("TicketType")
                        }
                    );
                }
            }
            return admissionTicketOrders;
        }

    public IEnumerable<AdmissionTicketOrder> FindBy(Query query, int pageSize, int pageCount)
    {
        return FindBy(query).Skip(pageSize * pageCount).Take(pageCount);
    }
}

 

控制台调用

static void Main(string[] args)
{
    using (var sqlHelper = new SqlHelper("ConnectionString"))
    {
        AdmissionTicketOrderRepository ator = new AdmissionTicketOrderRepository(sqlHelper);
        AdmissionTicketOrder ato = ator.Find(2);

        Query query = new Query();

        query.Add(new Criterion("SalePrice", 100, CriteriaOperator.GreaterThan));
        query.QueryOperator = QueryOperator.And;
        query.Add(new Criterion("TicketType", "单一票型", CriteriaOperator.Equal));
        query.Add(new Criterion("BuyAmount", 250, CriteriaOperator.LessThan));
        query.QueryOperator = QueryOperator.And;
        query.OrderByProperty = new OrderByClause { PropertyName = "TicetUseDateTime", Desc = true };

        List<AdmissionTicketOrder> list = ator.FindBy(query).ToList();
        foreach (var item in list)
        {
            Console.WriteLine("{0}{1}{2}{3}",item.ID,item.ProviderContactName,item.BuyPrice,item.SalePrice);
        }
    }
    Console.Read();

}

源代码下载