-
什么是Query Object模式
-
Query Object的架構設計
-
Query Object在服務層的應用
-
測試
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模式的架構
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);elsethrow 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; }} 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。
- 最后創建一個翻譯器：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 ";elsereturn "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);}}
轉載于:https://www.cnblogs.com/OceanEyes/archive/2012/11/14/think-in-design-pattern-query-object.html
總結
以上是生活随笔為你收集整理的Thinking In Design Pattern——Query Object模式的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
