Thinking In Design Pattern——Query Object模式
Thinking In Design Pattern——Query Object模式
木宛城主 发表于3年前
Thinking In Design Pattern——Query Object模式
  • 发表于 3年前
  • 阅读 6
  • 收藏 0
  • 点赞 0
  • 评论 0

标题:腾讯云 新注册用户域名抢购1元起>>>   

Query Object模式

Query Object:可以在领域服务层构造查询然后传给资源库使用,并使用某种查询翻译器将对象查询(Query)翻译成底层数据库持久化框架可以理解的查询(即翻译成一条Sql 语句)。而Query Object即可以理解为表示数据库查询的对象。且可以构造任意查询,然后传给Repository。Query Object模式的主要好处是它完全将底层的数据库查询语言抽象出来。

如果没有某种查询机制,我们的持久化层可能会这样定义方法:

public interface IOrderRepository { IEnumerable<Order> FindAll(Query query); IEnumerable<Order> FindAllVipCustomer(); IEnumerable<Order> FindOrderBy(Guid customerId); IEnumerable<Order> FindAllCustomersWithOutOrderId(); }

很明显,可以看出持久化层很不简洁,Repository将充满大量检索方法,而我们希望我们的持久化层尽量简洁些,根据传入参数能够动态的翻译成数据库查询语言,就像下面写的这样:

public interface IOrderRepository { IEnumerable<Order> FindBy(Query query); IEnumerable<Order> FindBy(Query query, int index, int count); }

这个Query就是核心——一个表示数据库查询的对象,好处是显而易见的:完全将底层的数据库查询语言抽象出来,因此将数据持久化和检索的基础设施关注点从业务层中分离出来。

Query Object模式的架构

  • 添加一个枚举,CriteriaOperator:
public enum CriteriaOperator { Equal,//=
        LessThanOrEqual,// <=
        NotApplicable//// TODO: 省略了其他的操作符,可继续添加
    }
  • 接着添加Criterion类,表示构成查询的过滤器部分:指定一个实体属性(OR  Mapping)、要比较的值以及比较方式
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; } } /// <summary>
        /// Lambda表达式树:创建一个过滤器 /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <param name="value"></param>
        /// <param name="criteriaOperator"></param>
        /// <returns></returns>
        public static Criterion Create<T>(Expression<Func<T, object>> expression, Object value, CriteriaOperator criteriaOperator) { string propertyName = PropertyNameHelper.ResolvePropertyName<T>(expression); Criterion myCriterion = new Criterion(propertyName, value, criteriaOperator); return myCriterion; } }
  • 为了避免在构建查询时出现令人畏惧的魔幻字符串,我们创建一个辅助方法,使用表达式参数。
public static class PropertyNameHelper {   public static string ResolvePropertyName<T>(Expression<Func<T, object>> expression) { var expr = expression.Body as MemberExpression; if (expr==null) { var u = expression.Body as UnaryExpression; expr = u.Operand as MemberExpression; } return expr.ToString().Substring(expr.ToString().IndexOf(".")+1); } }

这样就可以像查询中添加一个新的查询条件:

query.Add(Criterion.Create<Order>(c=>c.CustomerId,customerId,CriteriaOperator.Equal));

而不是使用魔幻字符串:

query.Add(new Criterion("CustomerId", customerId, CriteriaOperator.Equal));
  • 下面要创建表示查询的排序属性:
public class OrderByClause { public string PropertyName { get; set; } public bool Desc { get; set; } }
  • 接着,创建另一个枚举,确定如何各个Criterion进行评估:
public enum QueryOperator { And, Or }
  • 有时候的复杂非常难以创建,在这些情况下,可以使用指向数据库视图或存储过程的命名查询,添加一个QueryName来存放查询列表:
public enum QueryName { Dynamic = 0,//动态创建
        RetrieveOrdersUsingAComplexQuery = 1//使用已经创建好了的存储过程、视图、特别是查询比较复杂时使用存储过程
    }
  • 最后,添加Query类,将Query Object模式组合在一起:
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>
        /// 判断该查询是否已经动态生成或与Repository中某个预先建立的查询相关 /// </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; } }
  • 最后创建一个工厂类,提供已存在的查询:
public static class NamedQueryFactory { public static Query CreateRetrieveOrdersUsingAComplexQuery(Guid CustomerId) { IList<Criterion> criteria = new List<Criterion>(); Query query = new Query(QueryName.RetrieveOrdersUsingAComplexQuery, criteria); criteria.Add(new Criterion ("CustomerId", CustomerId, CriteriaOperator.NotApplicable)); return query; } }

Query Object在服务层的运用

  • 建立领域模型和领域服务类:
public class Order { public Guid Id { get; set; } public bool HasShipped { get; set; } public DateTime OrderDate { get; set; } public Guid CustomerId { get; set; } }
  • 添加Repository接口:
public interface IOrderRepository { IEnumerable<Order> FindBy(Query query); IEnumerable<Order> FindBy(Query query, int index, int count); }
  • 建立领域服务层:
public class OrderService { private IOrderRepository _orderRepository; public OrderService(IOrderRepository orderRepository) { _orderRepository = orderRepository; } public IEnumerable<Order> FindAllCustomersOrdersBy(Guid customerId) { IEnumerable<Order> customerOrders = new List<Order>(); Query query = new Query(); //推介使用这种
            query.Add(Criterion.Create<Order>(c=>c.CustomerId,customerId,CriteriaOperator.Equal)); //输入魔幻字符串,容易出错
            query.Add(new Criterion("CustomerId", customerId, CriteriaOperator.Equal)); query.OrderByProperty = new OrderByClause { PropertyName = "CustomerId", Desc = true }; customerOrders = _orderRepository.FindBy(query); return customerOrders; } public IEnumerable<Order> FindAllCustomersOrdersWithInOrderDateBy(Guid customerId, DateTime orderDate) { IEnumerable<Order> customerOrders = new List<Order>(); Query query = new Query(); query.Add(new Criterion("CustomerId", customerId, CriteriaOperator.Equal)); query.QueryOperator = QueryOperator.And; query.Add(new Criterion("OrderDate", orderDate, CriteriaOperator.LessThanOrEqual)); query.OrderByProperty = new OrderByClause { PropertyName = "OrderDate", Desc = true }; customerOrders = _orderRepository.FindBy(query); return customerOrders; } public IEnumerable<Order> FindAllCustomersOrdersUsingAComplexQueryWith(Guid customerId) { IEnumerable<Order> customerOrders = new List<Order>(); Query query = NamedQueryFactory.CreateRetrieveOrdersUsingAComplexQuery(customerId); customerOrders = _orderRepository.FindBy(query); return customerOrders; } }

OrderService类包含3个方法,他们将创建的查询传递给Repository。FindAllCustomersOrdersBy和FindAllCustomersOrdersWithInOrderDateBy方法通过CriterionOrderByClaus添加来创建动态查询。FindAllCustomersOrdersUsingAComplexQueryWith是命名查询,使用NamedQueryFactory来创建要传给Repository的Query Object。

  • 最后创建一个翻译器:QueryTranslator,将查询对象翻译成一条可在数据库上运行的Sql命令:
public static class OrderQueryTranslator { private static string baseSelectQuery = "SELECT * FROM Orders "; public static void TranslateInto(this Query query, SqlCommand command) { if (query.IsNamedQuery()) { command.CommandType = CommandType.StoredProcedure; command.CommandText = query.Name.ToString(); foreach (Criterion criterion in query.Criteria) { command.Parameters.Add(new SqlParameter("@" + criterion.PropertyName, criterion.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)); command.Parameters.Add(new SqlParameter("@" + criterion.PropertyName, criterion.Value)); _isNotfirstFilterClause = true; } sqlQuery.Append(GenerateOrderByClauseFrom(query.OrderByProperty)); command.CommandType = CommandType.Text; command.CommandText = sqlQuery.ToString(); } } private static string GenerateOrderByClauseFrom(OrderByClause orderByClause) { return String.Format("ORDER BY {0} {1}", FindTableColumnFor(orderByClause.PropertyName), orderByClause.Desc ? "DESC" : "ASC"); } 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 "<="; default: throw new ApplicationException("No operator defined."); } } private static string FindTableColumnFor(string propertyName) { switch (propertyName) { case "CustomerId": return "CustomerId"; case "OrderDate": return "OrderDate"; default: throw new ApplicationException("No column defined for this property."); } } }
  • 建立简单仓储对象:
public class OrderRepository : IOrderRepository { private string _connectionString; public OrderRepository(string connectionString) { _connectionString = connectionString; } public IEnumerable<Order> FindBy(Query query) { // Move to method below with Index and count
 IList<Order> orders = new List<Order>(); using (SqlConnection connection =
                      new SqlConnection(_connectionString)) { SqlCommand command = connection.CreateCommand(); query.TranslateInto(command); connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { orders.Add(new Order { CustomerId = new Guid(reader["CustomerId"].ToString()), OrderDate = DateTime.Parse(reader["OrderDate"].ToString()), Id = new Guid(reader["Id"].ToString()) }); } } } return orders; } public IEnumerable<Order> FindBy(Query query, int index, int count) { throw new NotImplementedException(); } }

测试

 [TestFixture] public class SQLQueryTranslatorTests { [Test] public void The_Translator_Should_Produce_Valid_SQL_From_A_Query_Object() { int customerId = 9; string expectedSQL = "SELECT * FROM Orders WHERE CustomerId = @CustomerId ORDER BY CustomerId DESC"; Query query = new Query(); query.Add(new Criterion("CustomerId", customerId, CriteriaOperator.Equal)); //query.Add(Criterion.Create<Order>(c => c.CustomerId, customerId, CriteriaOperator.Equal));
            query.OrderByProperty = new OrderByClause { PropertyName = "CustomerId", Desc = true }; SqlCommand command = new SqlCommand(); query.TranslateInto(command); Assert.AreEqual(expectedSQL, command.CommandText); } }
共有 人打赏支持
粉丝 1
博文 222
码字总数 199010
×
木宛城主
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: