仿orm自动生成分页SQL分享
发布时间：2022-11-09 22:48:30
先看看目前这4种数据库的分页写法:
-- Oracle
SELECT * 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
-- SQL2000
SELECT 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 ( Select {0} {1} ) PageTab where ROWNUM <= :PageEnd ", sqls[2], sqlOrderChange);
//如果查询不是第一页，则需要判断起始行号
if (start > 1)
{
pageSql.Insert(0, "SELECT * FROM ( ");
pageSql.Append(" ) ");
pageSql.Append(" WHERE RN>= :PageStart ");
}
}
else if (dbType == DBType.SQLite)
{
pageSql.AppendFormat("SELECT * FROM ( SELECT {0} {1} limit @PageStart,@PageLimit ) PageTab ", sqls[2], sqlOrderChange);
}
//恢复排序
pageSql.Append(sqlOrder);
//存储生成的分页SQL语句
sqls[1] = pageSql.ToString();
//临时测试
sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");
Console.WriteLine("【count】{0}", sqls[0]);
Console.WriteLine("【page】{0}", sqls[1]);
Console.WriteLine();
}
/// <summary>
/// 生成常规Sql语句
/// </summary>
/// <param name="sql"></param>
/// <param name="sqls"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="createCount"></param>
public void CreatePageSql(string sql, out string[] sqls, int start, int limit, bool createCount = false)
{
//需要输出的sql数组
sqls = null;
//生成count的SQL语句 SqlServer生成分页，必须通过正则拆分
if (createCount || dbType == DBType.SqlServer || dbType == DBType.SqlServer2000)
{
sqls = SplitSqlForPaging(sql);
if (sqls == null)
{
//无法解析的SQL语句
throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
}
}
else
{
sqls = new string[2];
}
//组织分页SQL语句
var pageSql = new StringBuilder();
var end = start + limit;
if (dbType == DBType.SqlServer2000)
{
pageSql.AppendFormat("SELECT TOP @PageEnd {0} {1}", sqls[2], sqls[3]);
if (start > 1)
{
var orderChange = 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, "SELECT TOP 100 PERCENT * FROM (SELECT TOP @PageLimit * FROM ( ");
pageSql.AppendFormat(" ) PageTab {0} ) PageTab2 {1}", orderChange, sqls[3]);
}
}
else if (dbType == DBType.SqlServer)
{
pageSql.AppendFormat(" Select top @PageEnd ROW_NUMBER() over ({0}) RN , {1}",
string.IsNullOrEmpty(sqls[3]) ? "ORDER BY (SELECT NULL)" : sqls[3],
sqls[2]);
//如果查询不是第一页，则需要判断起始行号
if (start > 1)
{
pageSql.Insert(0, "Select PageTab.* from ( ");
pageSql.Append(" ) PageTab Where RN >= @PageStart");
}
}
else if (dbType == DBType.Oracle)
{
pageSql.Append("select ROWNUM RN, PageTab.* from ");
pageSql.AppendFormat(" ( {0} ) PageTab ", sql);
pageSql.Append(" where ROWNUM <= :PageEnd ");
//如果查询不是第一页，则需要判断起始行号
if (start > 1)
{
pageSql.Insert(0, "select * from ( ");
pageSql.Append(" ) Where RN>= :PageStart ");
}
}
else if (dbType == DBType.SQLite)
{
pageSql.AppendFormat("{0} limit @PageStart,@PageLimit", sql, start, limit);
}
//存储生成的分页SQL语句
sqls[1] = pageSql.ToString();
//临时测试
sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");
Console.WriteLine("【count】{0}", sqls[0]);
Console.WriteLine("【page】{0}", sqls[1]);
Console.WriteLine();
}
}
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条数据。
0
投稿猜你喜欢
- 一、什么是iText？在企业的信息系统中，报表处理一直占比较重要的作用，iText是一种生成PDF报表的Java组件。通过在服务器端使用Js
- 看代码吧~package com.mtpc.admin.controller.exportSql;import ch.qos.logback
- public class MyGestureLintener extends SimpleOnGestureListener {privat
- 下面给大家分享一小段代码给大家介绍C# 输出字符串到文本文件中，具体代码如下所示： public class WriteHelp
- 在实际的工作中直接使用反射的机会比较少，有印象的就是一次自己做的WinForms小工具的时候利用反射来动态获取窗体上的每个控件，并且为必要的
- 本文实例讲述了java设置session过期时间的实现方法，分享给大家供大家参考。具体实现方法如下：1、Timeout in the dep
- 本文实例讲述了Android编程之手机壁纸WallPaper设置方法。分享给大家供大家参考，具体如下：/** * Andorid设置手机屏幕
About Spring开源免费框架，轻量级，非入侵式框架。Spring就是一个轻量级的控制反转（IOC）和面向切片编程（AOP）的框架Ma- this:this理解为：当前对象 或 当前正在创建的对象可以调用的结构：属性、方法；构造器this调用属性、方法：先了解一下形参:形参的意
- 先说一句：密码是无法解密的。大家也不要再问松哥微人事项目中的密码怎么解密了！密码无法解密，还是为了确保系统安全。今天松哥就来和大家聊一聊，密
- TCPServer 1、使用的通讯通道：socket2、用到的基本功能：①Bind,②Listen,③BeginAccept④En
- 删除字符串的所有标点str = str.replaceAll("[\\pP‘'“”]", ""
- JNI中的java接口使用项目需求，需要在c++函数中监听相应的状态，并在java端进行一些列的处理。这个需要在JNI中写一个subscri
- 前言：Java数据结构与算法专题会不定时更新，欢迎各位读者监督。本文从最简单的一个排序算法——桶排序开始，分析桶排序的实现思路，代码实现，性
- 线程池中ThreadGroup的坑在Java中每一个线程都归属于某个线程组管理的一员，例如在主函数main()主工作流程中产生一个线程，则产
- 在Web应用系统开发中，文件上传和下载功能是非常常用的
- 本文实例讲述了Android编程中沉浸式状态栏的三种实现方式。分享给大家供大家参考，具体如下：沉浸式状态栏Google从android ki
- 线程启动：1.start() 和 run()的区别说明start() : 它的作用是启动一个新线程，新线程会执行相应的run()方法。sta
- 在学习MyBatis过程中想实现模糊查询，可惜失败了。后来上百度上查了一下，算是解决了。记录一下MyBatis实现模糊查询的几种方式。 数据
- 前言缓存技术被认为是减轻服务器负载、降低网络拥塞、增强Web可扩展性的有效途径之一，其基本思想是利用客户访问的时间局部性（Temproral
