仿Orm 自动生成分页SQL

8/3/2015来源:C#应用人气:1296

仿Orm 自动生成分页SQL

分页的写法

  自从用上了Orm,分页这种事就是腰不酸腿不痛了。不过有时候想用纯粹的ado.net来操作,希望返回的数据是原生的DataTable或DbDataReader类似的东西,故研究下怎么生成分页的SQL语句。

  平时接触的数据库有sql2000-2008,Oracle,SQLite 。 分页逻辑,Oracle和SQLite相对好写,就SQL事多,Sql2000下只能用top,排序2次,而Sql2005+就可以使用ROW_NUMBER()分析函数了,据说Sql2012对分页又有了改进,暂时用不上那么高的版本,所以没做。先看看目前这4种数据库的分页写法:

-- OracleSELECT * FROM (     SELECT ROWNUM RN,  PageTab.* FROM                  (                 SELECT * FROM User_Tables order by id desc                 ) PageTab  where ROWNUM <= 3010             ) Where RN>= 3001             -- SQLite    select * from User_Tables order by id desc limit 3001,10-- SQL2000SELECT TOP 100 PERCENT  * FROM (    SELECT TOP 10 * FROM (         SELECT TOP 3010 * from User_Tables  order by id desc ) PageTab order by id ASC  ) PageTab2 order by id desc-- SQL2005+    Select PageTab.* from (      Select top 3010 ROW_NUMBER() over (order by id desc) RN , * from User_Tables  ) PageTab Where RN >= 3001

  其中针对 Oracle和Sql2005+的分页写法做个说明。

  Oracle使用ROWNUM要比Row_Number()要快。sql示例中均是查询 [3001,3010] 区间的数据,在Sql语句中,尽可能在子查询中减少查询的结果集行数,然后针对排序过后的行号,在外层查询中做条件筛选。 如Oracle写法中 子查询有ROWNUM <= 3010 ,Sql2005 中有 top 3010 * 。

  当然今天要讨论的问题,不是分页语句的性能问题,如果你知道更好更快的写法,欢迎交流。

  上面的分页写法,基于的查询sql语句是:

select * from User_Tables order by id desc

  首先要从Sql语句中分析出行为,我把该Sql拆成了n部分,然后完成了以上拼接功能。按照模子往里面套数据,难度不大。

逆序分页

  我们来描述另外一种场景,刚刚演示的sql是查询 满足条件下行数在[3001,3010]之间的数据,如果说总行数仅仅只有3500行,那么结果则是需要查询出3010行数据,并取出最后10条,而前面3000条数据,是没用的。

  所以借鉴以前的经验,姑且叫它 逆序分页 。在知道总行数的前提下,我们可以进行分析,是否需要逆序分页,因为逆序分页得到分页Sql语句,也是需要时间的,并非所有的情况都有必要这么做。之前有假设,数据仅仅有3500行,我们期望取出 按照id 倒叙排序后的[3001,3010]数据,换种方式理解,若按照id升序,我们期望取出的数据则是[491,500] 这个区间,然后将这个数据,再按照id倒叙排序,也就是我们需要的数据了。

  理论知识差不多就说完了,需要了解更多的话,百度一下,你就知道。下面是代码,有点长,展开当心:

    public enum DBType    {        SqlServer2000,        SqlServer,        Oracle,        SQLite    }    public class Page    {        /// <summary>        /// 数据库类别        /// </summary>        public DBType dbType = DBType.Oracle;        /// <summary>        /// 逆序分页行数,总行数大于MaxRow,则会生成逆序分页SQL        /// </summary>        public int MaxRow = 1000;//临时测试,把值弄小点        /// <summary>        /// 匹配SQL语句中Select字段        /// </summary>        PRivate Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);        /// <summary>        /// 匹配SQL语句中Order By字段        /// </summary>        private Regex rxOrderBy = new Regex(@"\b(?<ordersql>ORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+)(?:\s+(?<order>ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);        /// <summary>        /// 匹配SQL语句中Distinct        /// </summary>        private Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);        private string[] SplitSqlForPaging(string sql)        {            /*存储分析过的SQL信息 依次为:             * 0.countsql             * 1.pageSql(保留位置此处不做分析)             * 2.移除了select的sql             * 3.order by 字段 desc             * 4.order by 字段             * 5.desc             */            var sqlInfo = new string[6];            // Extract the columns from "SELECT <whatever> FROM"            var m = rxColumns.Match(sql);            if (!m.Success)                return null;            // Save column list and replace with COUNT(*)            Group g = m.Groups[1];            sqlInfo[2] = sql.Substring(g.Index);            if (rxDistinct.IsMatch(sqlInfo[2]))                sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);            else                sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);            // Look for an "ORDER BY <whatever>" clause            m = rxOrderBy.Match(sqlInfo[0]);            if (!m.Success)            {                sqlInfo[3] = null;            }            else            {                g = m.Groups[0];                sqlInfo[3] = g.ToString();                //统计的SQL 移除order                sqlInfo[0] = sqlInfo[0].Substring(0, g.Index) + sqlInfo[0].Substring(g.Index + g.Length);                //存储排序信息                sqlInfo[4] = m.Groups["ordersql"].Value;//order by xxx                sqlInfo[5] = m.Groups["order"].Value;//desc                 //select部分 移除order                sqlInfo[2] = sqlInfo[2].Replace(sqlInfo[3], string.Empty);            }            return sqlInfo;        }        /// <summary>        /// 生成逆序分页Sql语句        /// </summary>        /// <param name="sql"></param>        /// <param name="sqls"></param>        /// <param name="start"></param>        /// <param name="limit"></param>        /// <param name="total"></param>        public void CreatePageSqlReverse(string sql,ref string[] sqls, int start, int limit, int total = 0)        {            //如果总行数不多或分页的条数位于前半部分,没必要逆序分页            if (total < 100 || start <= total / 2)            {                return;            }            //sql正则分析过后的数组有5个值,若未分析,此处分析            if (sqls == null || sqls.Length == 6)            {                sqls = SplitSqlForPaging(sql);                if (sqls == null)                {                    //无法解析的SQL语句                    throw new Exception("can't parse sql to pagesql ,the sql is " + sql);                }            }            //如果未定义排序规则,则无需做逆序分页计算            if (string.IsNullOrEmpty(sqls[5]))            {                return;            }            //逆序分页检查            string sqlOrder = sqls[3];            int end = start + limit;            //获取逆序排序的sql            string sqlOrderChange = string.Compare(sqls[5], "desc", true) == 0 ?                string.Format("{0} ASC ", sqls[4]) :                string.Format("{0} DESC ", sqls[4]);            /*理论             * total:10000 start:9980 limit:10              * 则 end:9990 分页条件为 RN >= 9980+1 and RN <= 9990             * 逆序调整后              * start = total - start = 20             * end = total - end = 10             * 交换start和end,分页条件为 RN >= 10+1 and RN<= 20             */            //重新计算start和end            start = total - start;            end = total - end;            //交换start end            start = start + end;            end = start - end;            start = start - end;            //定义分页SQL            var pageSql = new StringBuilder();            if (dbType == DBType.SqlServer2000)            {                pageSql.AppendFormat("SELECT TOP @PageLimit * FROM ( SELECT TOP @PageEnd {0} {1} ) ", sqls[2], sqlOrderChange);            }            else if (dbType == DBType.SqlServer)            {                //组织分页SQL语句                pageSql.AppendFormat("SELECT PageTab.* FROM ( SELECT TOP @PageEnd ROW_NUMBER() over ({0}) RN , {1}  ) PageTab ",                    sqlOrderChange,                    sqls[2]);                //如果查询不是第一页,则需要判断起始行号                if (start > 1)                {                    pageSql.Append("Where RN >= :PageStart ");                }            }            else if (dbType == DBType.Oracle)            {                pageSql.AppendFormat("SELECT ROWNUM RN,  PageTab.* FROM