仿orm自动生成分页SQL分享
先看看目前这4种数据库的分页写法:
--Oracle
SELECT*FROM(
SELECTROWNUMRN, PageTab.*FROM
(
SELECT*FROMUser_Tablesorderbyiddesc
)PageTab whereROWNUM<=3010
)WhereRN>=3001
--SQLite
select*fromUser_Tablesorderbyiddesclimit3001,10
--SQL2000
SELECTTOP100PERCENT *FROM(
SELECTTOP10*FROM(
SELECTTOP3010*fromUser_Tables orderbyiddesc)PageTaborderbyidASC
)PageTab2orderbyiddesc
--SQL2005+
SelectPageTab.*from(
Selecttop3010ROW_NUMBER()over(orderbyiddesc)RN,*fromUser_Tables
)PageTabWhereRN>=3001
其中针对Oracle和Sql2005+的分页写法做个说明。
Oracle使用ROWNUM要比Row_Number()要快。sql示例中均是查询[3001,3010]区间的数据，在Sql语句中，尽可能在子查询中减少查询的结果集行数，然后针对排序过后的行号，在外层查询中做条件筛选。如Oracle写法中子查询有ROWNUM<=3010,Sql2005中有top3010*。
当然今天要讨论的问题，不是分页语句的性能问题，如果你知道更好更快的写法，欢迎交流。
上面的分页写法，基于的查询sql语句是:
select*fromUser_Tablesorderbyiddesc
首先要从Sql语句中分析出行为，我把该Sql拆成了n部分，然后完成了以上拼接功能。按照模子往里面套数据，难度不大。
逆序分页
我们来描述另外一种场景，刚刚演示的sql是查询满足条件下行数在[3001,3010]之间的数据，如果说总行数仅仅只有3500行，那么结果则是需要查询出3010行数据，并取出最后10条，而前面3000条数据，是没用的。
所以借鉴以前的经验，姑且叫它逆序分页。在知道总行数的前提下，我们可以进行分析，是否需要逆序分页，因为逆序分页得到分页Sql语句，也是需要时间的，并非所有的情况都有必要这么做。之前有假设，数据仅仅有3500行，我们期望取出按照id倒叙排序后的[3001,3010]数据，换种方式理解，若按照id升序，我们期望取出的数据则是[491,500]这个区间，然后将这个数据，再按照id倒叙排序，也就是我们需要的数据了。
理论知识差不多就说完了，需要了解更多的话，百度一下，你就知道。下面是代码，有点长，展开当心:
publicclassPage ///<summary> //SavecolumnlistandreplacewithCOUNT(*) if(rxDistinct.IsMatch(sqlInfo[2])) //select部分移除order returnsqlInfo; //sql正则分析过后的数组有5个值，若未分析，此处分析 //如果未定义排序规则，则无需做逆序分页计算 //逆序分页检查 //获取逆序排序的sql /*理论 //定义分页SQL if(dbType==DBType.SqlServer2000) //如果查询不是第一页，则需要判断起始行号 //如果查询不是第一页，则需要判断起始行号 //恢复排序 //存储生成的分页SQL语句 //临时测试 Console.WriteLine("【count】{0}",sqls[0]); ///<summary> //生成count的SQL语句SqlServer生成分页，必须通过正则拆分 //组织分页SQL语句 varend=start+limit; if(start>1) //如果查询不是第一页，则需要判断起始行号 //如果查询不是第一页，则需要判断起始行号 //存储生成的分页SQL语句 //临时测试 Console.WriteLine("【count】{0}",sqls[0]);
1.交换2个整数用了这样的算法。交换a和b，a=a+b;b=a-b;b=a-b;这是原来找工作的时候被考到的，如果在不使用第三方变量的情况下交换2个整数。 2.Sql2000下由于是使用top进行分页，除非条件一条数据都查不到，否则在分页start和limit参数超过了总行数时，也会查询出数据。 3.拆分Sql语句，参考了PetaPoco的部分源代码。 4.我的应用场景则是在dbhelp类，某个方法传递sql，start，limit参数即可对sql查询出来的结果进行分页。其中start：查询结果的起始行号(不包括它)，limit：需要取出的行数。如start:0,limit:15则是取出前15条数据。
publicenumDBType
{
SqlServer2000,
SqlServer,
Oracle,
SQLite
}
{
///<summary>
///数据库类别
///</summary>
publicDBTypedbType=DBType.Oracle;
///<summary>
///逆序分页行数，总行数大于MaxRow，则会生成逆序分页SQL
///</summary>
publicintMaxRow=1000;//临时测试，把值弄小点
///匹配SQL语句中Select字段
///</summary>
privateRegexrxColumns=newRegex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b",RegexOptions.IgnoreCase|RegexOptions.Multiline|RegexOptions.Singleline|RegexOptions.Compiled);
///<summary>
///匹配SQL语句中OrderBy字段
///</summary>
privateRegexrxOrderBy=newRegex(@"\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>
privateRegexrxDistinct=newRegex(@"\ADISTINCT\s",RegexOptions.IgnoreCase|RegexOptions.Multiline|RegexOptions.Singleline|RegexOptions.Compiled);
privatestring[]SplitSqlForPaging(stringsql)
{
/*存储分析过的SQL信息依次为:
*0.countsql
*1.pageSql(保留位置此处不做分析)
*2.移除了select的sql
*3.orderby字段desc
*4.orderby字段
*5.desc
*/
varsqlInfo=newstring[6];
//Extractthecolumnsfrom"SELECT<whatever>FROM"
varm=rxColumns.Match(sql);
if(!m.Success)
returnnull;
Groupg=m.Groups[1];
sqlInfo[2]=sql.Substring(g.Index);
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);
//Lookforan"ORDERBY<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;//orderbyxxx
sqlInfo[5]=m.Groups["order"].Value;//desc
sqlInfo[2]=sqlInfo[2].Replace(sqlInfo[3],string.Empty);
}
}
///<summary>
///生成逆序分页Sql语句
///</summary>
///<paramname="sql"></param>
///<paramname="sqls"></param>
///<paramname="start"></param>
///<paramname="limit"></param>
///<paramname="total"></param>
publicvoidCreatePageSqlReverse(stringsql,refstring[]sqls,intstart,intlimit,inttotal=0)
{
//如果总行数不多或分页的条数位于前半部分，没必要逆序分页
if(total<100||start<=total/2)
{
return;
}
if(sqls==null||sqls.Length==6)
{
sqls=SplitSqlForPaging(sql);
if(sqls==null)
{
//无法解析的SQL语句
thrownewException("can"tparsesqltopagesql,thesqlis"+sql);
}
}
if(string.IsNullOrEmpty(sqls[5]))
{
return;
}
stringsqlOrder=sqls[3];
intend=start+limit;
stringsqlOrderChange=string.Compare(sqls[5],"desc",true)==0?
string.Format("{0}ASC",sqls[4]):
string.Format("{0}DESC",sqls[4]);
*total:10000start:9980limit:10
*则end:9990分页条件为RN>=9980+1andRN<=9990
*逆序调整后
*start=total-start=20
*end=total-end=10
*交换start和end，分页条件为RN>=10+1andRN<=20
*/
//重新计算start和end
start=total-start;
end=total-end;
//交换startend
start=start+end;
end=start-end;
start=start-end;
varpageSql=newStringBuilder();
{
pageSql.AppendFormat("SELECTTOP@PageLimit*FROM(SELECTTOP@PageEnd{0}{1})",sqls[2],sqlOrderChange);
}
elseif(dbType==DBType.SqlServer)
{
//组织分页SQL语句
pageSql.AppendFormat("SELECTPageTab.*FROM(SELECTTOP@PageEndROW_NUMBER()over({0})RN,{1} )PageTab",
sqlOrderChange,
sqls[2]);
if(start>1)
{
pageSql.Append("WhereRN>=:PageStart");
}
}
elseif(dbType==DBType.Oracle)
{
pageSql.AppendFormat("SELECTROWNUMRN, PageTab.*FROM (Select{0}{1})PageTab whereROWNUM<=:PageEnd",sqls[2],sqlOrderChange);
if(start>1)
{
pageSql.Insert(0,"SELECT*FROM(");
pageSql.Append(")");
pageSql.Append("WHERERN>=:PageStart");
}
}
elseif(dbType==DBType.SQLite)
{
pageSql.AppendFormat("SELECT*FROM(SELECT{0}{1}limit @PageStart,@PageLimit)PageTab",sqls[2],sqlOrderChange);
}
pageSql.Append(sqlOrder);
sqls[1]=pageSql.ToString();
sqls[1]=sqls[1].Replace("@","").Replace(":","").Replace("PageStart",++start+"").Replace("PageEnd",end+"").Replace("PageLimit",limit+"");
Console.WriteLine("【page】{0}",sqls[1]);
Console.WriteLine();
}
///生成常规Sql语句
///</summary>
///<paramname="sql"></param>
///<paramname="sqls"></param>
///<paramname="start"></param>
///<paramname="limit"></param>
///<paramname="createCount"></param>
publicvoidCreatePageSql(stringsql,outstring[]sqls,intstart,intlimit,boolcreateCount=false)
{
//需要输出的sql数组
sqls=null;
if(createCount||dbType==DBType.SqlServer||dbType==DBType.SqlServer2000)
{
sqls=SplitSqlForPaging(sql);
if(sqls==null)
{
//无法解析的SQL语句
thrownewException("can"tparsesqltopagesql,thesqlis"+sql);
}
}
else
{
sqls=newstring[2];
}
varpageSql=newStringBuilder();
if(dbType==DBType.SqlServer2000)
{
pageSql.AppendFormat("SELECTTOP@PageEnd{0}{1}",sqls[2],sqls[3]);
{
varorderChange=string.IsNullOrEmpty(sqls[5])?null:
string.Compare(sqls[5],"desc",true)==0?
string.Format("{0}ASC",sqls[4]):
string.Format("{0}DESC",sqls[4]);
pageSql.Insert(0,"SELECTTOP100PERCENT *FROM(SELECTTOP@PageLimit*FROM(");
pageSql.AppendFormat(")PageTab{0})PageTab2{1}",orderChange,sqls[3]);
}
}
elseif(dbType==DBType.SqlServer)
{
pageSql.AppendFormat("Selecttop@PageEndROW_NUMBER()over({0})RN,{1}",
string.IsNullOrEmpty(sqls[3])?"ORDERBY(SELECTNULL)":sqls[3],
sqls[2]);
if(start>1)
{
pageSql.Insert(0,"SelectPageTab.*from(");
pageSql.Append(")PageTabWhereRN>=@PageStart");
}
}
elseif(dbType==DBType.Oracle)
{
pageSql.Append("selectROWNUMRN, PageTab.*from");
pageSql.AppendFormat("({0})PageTab",sql);
pageSql.Append("whereROWNUM<=:PageEnd");
if(start>1)
{
pageSql.Insert(0,"select*from(");
pageSql.Append(")WhereRN>=:PageStart");
}
}
elseif(dbType==DBType.SQLite)
{
pageSql.AppendFormat("{0}limit@PageStart,@PageLimit",sql,start,limit);
}
sqls[1]=pageSql.ToString();
sqls[1]=sqls[1].Replace("@","").Replace(":","").Replace("PageStart",++start+"").Replace("PageEnd",end+"").Replace("PageLimit",limit+"");
Console.WriteLine("【page】{0}",sqls[1]);
Console.WriteLine();
}
}
相关文章