ASP.NET设计模式之Query Object模式

最新推荐文章于 2024-08-28 01:00:00 发布

weizengxun

最新推荐文章于 2024-08-28 01:00:00 发布

阅读量4.4k

点赞数 2

本文链接：https://blog.csdn.net/weizengxun/article/details/8656006

版权

Query Object 同时被 2 个专栏收录

1 篇文章

订阅专栏

设计模式

1 篇文章

订阅专栏

当我们应用Repository模式作为数据仓储时，如果我们不使用Query Object模式，那么我们的Repository将会充满大量的检索方法，如下：

public interface IReadOnlyRepository<T, TId> where T : IAggregateRoot 
{
    T FindBy(TId id);
    IEnumerable<T> FindAll();
    IEnumerable<T> FindBy(int GroupId);
    IEnumerable<T> FindBy(string UserName);
    IEnumerable<T> FindBy(int GroupId,string UserName);
    ....
 }

使用Query Object模式：

public interface IReadOnlyRepository<T, TId> where T : IAggregateRoot 
{
    T FindBy(TId id);
    IEnumerable<T> FindAll();
    IEnumerable<T> FindBy(Query query);
    IEnumerable<T> FindBy(Query query, int index, int count);
}

下面我们来开始构建Query Object模式，先看类图：

1.构建条件操作符枚举

public enum CriteriaOperator
{
    Equal,              //=
    NotApplicable,      //<>
    LessThanOrEqual,    //<=
    LessThan,           //<
    GreaterThan,        //>
    GreaterThanOrEqual, //>=
    In,                 //IN()
    NotIn,              //NOT IN ()
    Like               //%
}

2.构建查询条件类

public class Criterion
{
    private string _propertyName;
    private object _value;
    private CriteriaOperator _criteriaOperator;
    private ParameterDirection _direction = ParameterDirection.Input;

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

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

    public string PropertyName
    {
        get { return _propertyName; }
    }

    public object Value
    {
        get { return _value; }
    }

    public CriteriaOperator CriteriaOperator
    {
        get { return _criteriaOperator; }
    }

    public ParameterDirection Direction {
        get { return _direction; }
    }
}

3.构建查询排序类

public class OrderByItem
{
    private string _propertyName;
    private bool _desc;

    public OrderByItem(string propertyName, bool desc)
    {
        PropertyName = propertyName;
        Desc = desc;
    }

    public string PropertyName
    {
        get { return _propertyName; }
        set { _propertyName = value; }
    }

    public bool Desc
    {
        get { return _desc; }
        set { _desc = value; }
    }
}

4.构建查询条件连接符枚举，该枚举用来确定多个条件是怎么链接起来的。

public enum QueryOperator
{
    And,
    Or
}

5.有时候复杂的查询难以创建，我们需要使用数据库视图或存储过程实现，下面创建QueryName枚举类来存放所有的名称查询的名称：

public enum QueryName
{
    Dynamic = 0,
    //RetrieveOrdersUsingAComplexQuery,
    //把所有的存储过程名称写在这里
    //这样是不是不方便修改
    getuserlist
}

第一个是Dynamic表示动态生成，其他的是命名查询。

6.以上的准备我们已经做好，现在开始来构建Query类：

public class Query
{
    private QueryName _name;
    private IList<Criterion> _criteria;
    private IList<QueryOperator> _queryOperator = new List<QueryOperator>();
    private IList<OrderByItem> _orderByClause = new List<OrderByItem>();

    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; }
    }

    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 additionalcriteria to named queries");
    }

    /// <summary>
    /// 添加条件连接符
    /// </summary>
    /// <param name="queryOperator"></param>
    public void AddQueryOperator(QueryOperator queryOperator)
    {
        QueryOperator.Add(queryOperator);
    }

    /// <summary>
    /// 添加排序属性
    /// </summary>
    /// <param name="orderByItem"></param>
    public void AddOrderByItem(OrderByItem orderByItem)
    {
        OrderByClause.Add(orderByItem);
    }

    public IList<QueryOperator> QueryOperator
    {
        get { return _queryOperator; }
        set { _queryOperator = value; }
    }

    public IList<OrderByItem> OrderByClause
    {
        get { return _orderByClause; }
        set { _orderByClause = value; }
    }
}

7.QueryObject模式只是把是查询条件封装成对象，要实现查询对象转换成SQL语句，我们还要构建一个翻译类，该类主要是把查询对象翻译成对应的Sql语句。为了使用方便我们写成扩展类，不同的数据库有不同的写法，不过大体上还是差不多的，下面是翻译成MS-SQL SERVER。

public static class QueryTranslator
{
    public static void TranslateInto(this Query query, SqlCommand command)
    {
        //生成存储过程
        if (query.IsNamedQuery()) {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = string.Format("{0}{1}", BaseSettings.GetTablePrefix(), query.Name);

            //生成参数
            foreach (Criterion criterion in query.Criteria) {
                command.Parameters.AddWithValue("@" + criterion.PropertyName, criterion.Value);
                command.Parameters["@" + criterion.PropertyName].Direction = criterion.Direction;
            }
            return;
        }

        string sqlQuery = query.TranslateWhere(command);
        sqlQuery += query.TranslateOrderBy();

        command.CommandType = CommandType.Text;
        command.CommandText += sqlQuery;
    }

    /// <summary>
    /// 生成OrderBy语句
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public static string TranslateOrderBy(this Query query)
    {
        return TranslateOrderBy(query, "");
    }
    /// <summary>
    /// 生成OrderBy语句
    /// </summary>
    /// <param name="query"></param>
    /// <param name="defaultValue"></param>
    /// <returns></returns>
    public static string TranslateOrderBy(this Query query, string defaultValue)
    {
        string orderBy = GenerateOrderByClauseFrom(query.OrderByClause);
        return string.IsNullOrEmpty(orderBy) ? defaultValue : orderBy;
    }

    /// <summary>
    /// 生成条件语句和条件语句中的参数
    /// </summary>
    /// <param name="query"></param>
    /// <param name="command"></param>
    /// <returns></returns>
    public static string TranslateWhere(this Query query, SqlCommand command)
    {
        StringBuilder sqlQuery = new StringBuilder(query.Criteria.Count() > 0 ? " WHERE " : "");

        //检查条件语句个数和连接词个数是否匹配
        if (query.QueryOperator.Count() != query.Criteria.Count() - 1) {
            throw new Exception("条件语句个数和连接词个数不匹配。");
        }

        bool _isNotfirstFilterClause = false;//不是第一个条件

        int n = 0, m = 0;
        foreach (Criterion criterion in query.Criteria) {

            //如果不是第一个条件，则加上连接符
            if (_isNotfirstFilterClause) {
                sqlQuery.Append(GetQueryOperator(query, n)); n++;
            }

            //生成语句,同时生成参数
            sqlQuery.Append(AddFilterClauseFrom(criterion, command, m));

            _isNotfirstFilterClause = true;
            m++;
        }

        return sqlQuery.ToString();
    }

    /// <summary>
    /// 将排序语句清空
    /// </summary>
    /// <param name="query"></param>
    public static void ResetOrderBy(this Query query)
    {
        query.OrderByClause.Clear();
    }

    private static string GetQueryOperator(Query query, int index)
    {
        QueryOperator queryOperator = query.QueryOperator[index];

        switch (queryOperator) {
            case QueryOperator.And:
                return " AND ";
            case QueryOperator.Or:
                return " OR ";
            default:
                throw new ApplicationException("No QueryOperator defined.");
        }

    }

    private static string AddFilterClauseFrom(Criterion criterion, SqlCommand command, int m)
    {
        if (criterion.CriteriaOperator == CriteriaOperator.In
            || criterion.CriteriaOperator == CriteriaOperator.NotIn) {
            string paras = string.Empty;
            if (criterion.Value is System.Int32[]) {
                var d = criterion.Value as int[];
                for (int i = 0; i < d.Length; i++) {
                    paras += string.Format("@p{0}_{1}{2}", m, i, i == d.Length - 1 ? "" : ",");
                    command.Parameters.AddWithValue(string.Format("@p{0}_{1}", m, i), d[i]);
                }
            }
            else {
                var d = criterion.Value as string[];
                for (int i = 0; i < d.Length; i++) {
                    paras += string.Format("@p{0}_{1}{2}", m, i, i == d.Length - 1 ? "" : ",");
                    command.Parameters.AddWithValue(string.Format("@p{0}_{1}", m, i), d[i]);
                }
            }
            return string.Format("[{0}] {1} ({2}) ", criterion.PropertyName, FindSQLOperatorFor(criterion.CriteriaOperator), paras);
        }

        command.Parameters.AddWithValue("@p" + m, criterion.Value);
        command.Parameters["@p" + m].Direction = criterion.Direction;

        return string.Format("[{0}] {1} @p{2} ", criterion.PropertyName, FindSQLOperatorFor(criterion.CriteriaOperator), m);
    }

    private static string GenerateOrderByClauseFrom(IList<OrderByItem> orderByClauses)
    {
        if (orderByClauses.Count() == 0) return "";

        string orderBy = " ORDER BY ";
        int n = 0, m = orderByClauses.Count() - 1;
        foreach (var item in orderByClauses) {
            orderBy += string.Format("[{0}] {1}{2}",
                item.PropertyName, item.Desc ? "DESC" : "ASC", n == m ? "" : ",");
            n++;
        }
        return orderBy;
    }

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

8.构建UserRepository类，增加如下方法：

public IEnumerable<User> FindBy(Query query)
{
    string commandText = string.Format("SELECT * FROM [{0}user]", BaseSettings.GetTablePrefix());

    IList<User> list = new List<User>();

    using (var connection = new SqlConnection(BaseSettings.GetDbConnectionString())) {
        using (var command = connection.CreateCommand()) {

            query.TranslateInto(command);

            connection.Open();
            using (IDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    list.Add(AutoMapper.Mapper.DynamicMap<User>(reader));
                }
                return list;
            }
        }
    }
}

public IEnumerable<User> FindBy(Query query, int index, int count)
{

    IList<User> list = new List<User>();

    using (var connection = new SqlConnection(BaseSettings.GetDbConnectionString())) {
        var command = connection.CreateCommand();

        command.CommandText = CreatePageSql(
            EntityTranslator.TranslateToSql<User>(),
            query.TranslateOrderBy("ORDER BY [Id] DESC"),
            query.TranslateWhere(command));

        command.Parameters.AddWithValue("@pageindex", index - 1);
        command.Parameters.AddWithValue("@pagesize", count);

        connection.Open();
        using (IDataReader reader = command.ExecuteReader()) {
            while (reader.Read()) {
                list.Add(AutoMapper.Mapper.DynamicMap<User>(reader));
            }
            return list;
        }
    }
}

private string CreatePageSql(string fields, string orderBy, string where)
{
    return string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER ({1}) AS [ROW_NUMBER], * FROM [{3}users] AS [t0] {2}) "
                + "AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @pageindex*@pagesize + 1 AND @pageindex + @pagesize",
                fields, orderBy, where, BaseSettings.GetTablePrefix());
}

此处使用了AutoMapper类，可以使用NuGet添加。

9.开始使用吧。

//动态生成sql
Query query = new Query();
query.Add(new Criterion("Id", "100003", CriteriaOperator.GreaterThan));
query.AddQueryOperator(QueryOperator.And);
//
query.Add(new Criterion("GroupId", new int[] { 35,56 }, CriteriaOperator.In));
query.AddOrderByItem(new OrderByItem("Id", false));
query.AddOrderByItem(new OrderByItem("GroupId", true));

IList<User> list = userRepository.FindBy(query, p, 2).ToList();

//使用存储过程
//IList<Criterion> criteria = new List<Criterion>();
//Query query1 = new Query(QueryName.getuserlist, criteria);
//criteria.Add(new Criterion("pageIndex", 1, CriteriaOperator.Equal));
//criteria.Add(new Criterion("pageSize", 5, CriteriaOperator.Equal));

//list = userRepository.FindBy(query).ToList();

StringBuilder sb1 = new StringBuilder();

foreach (var item in list) {
    sb1.AppendFormat("{0} {1}<br>", item.Id, item.GroupId);
}

ViewBag.Message = sb1.ToString();

return View();

监控生成的sql语句

exec sp_executesql N'SELECT [UserName],[UserPass],[GroupId],[Id] 
FROM (SELECT ROW_NUMBER() OVER ( ORDER BY [Id] ASC,[GroupId] DESC) AS [ROW_NUMBER], * FROM [jway_users] AS [t0] 
 WHERE [Id] > @p0  AND [GroupId] IN (@p1_0) ) AS [t1] 
WHERE [t1].[ROW_NUMBER] BETWEEN @pageindex*@pagesize + 1 AND @pageindex + @pagesize',
N'@p0 nvarchar(6),@p1_0 int,@pageindex int,@pagesize 
int',@p0=N'100003',@p1_0=35,@pageindex=0,@pagesize=2

效果还不错，突然发现小疏忽,不应该是*，应该改一下这个方法。

private string CreatePageSql(string fields, string orderBy, string where)
{
    return string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER ({1}) AS [ROW_NUMBER],{0} FROM [{3}users] AS [t0] {2}) "
                + "AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @pageindex*@pagesize + 1 AND @pageindex + @pagesize",
                fields, orderBy, where, BaseSettings.GetTablePrefix());
}

参考书籍《ASP.NET设计模式》这是翻译版的原版为《Professional ASP.NET Design Patterns》，书上的是结合了EF和NH写法有点不一样。还有书上的只是简单的写了个结构，也许是为了讲解方便吧。按书上的写法，动态生成SQL只能支持单一排序并且不支持In查询，我算是扩展了一点了吧

说说Query Object的优缺点，从网上找到的。

查询对象的好处

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

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

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

其他的两点都明白，就第三点没明白，懂得朋友解释解释。

缺点没说，我来说说吧。

1.不能生成繁杂的查询，如连表查询，当然可以使用存储过程，问题在于不是所用的数据库都支持存储过程

2.命名查询时的把存储过程的名称添加到QueryName枚举中，是不是有点不方便呢？

本人才开始学习设计模式，以上是个人的愚见，望高手赐教。

点击下载Demo