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就是核心——一个表示数据库查询的对象,好处是显而易见的:完全将底层的数据库查询语言抽象出来,因此将数据持久化和检索的基础设施关注点从业务层中分离出来。
public enum CriteriaOperator
{
Equal,//=
LessThanOrEqual,// <=
NotApplicable//≠
// TODO: 省略了其他的操作符,可继续添加
}
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; }
}
public enum QueryOperator
{
And,
Or
}
public enum QueryName
{
Dynamic = 0,//动态创建
RetrieveOrdersUsingAComplexQuery = 1//使用已经创建好了的存储过程、视图、特别是查询比较复杂时使用存储过程
}
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;
}
}
public class Order
{
public Guid Id { get; set; }
public bool HasShipped { get; set; }
public DateTime OrderDate { get; set; }
public Guid CustomerId { get; set; }
}
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方法通过Criterion和OrderByClaus添加来创建动态查询。FindAllCustomersOrdersUsingAComplexQueryWith是命名查询,使用NamedQueryFactory来创建要传给Repository的Query Object。
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);
}
}