Sqltoy ORM框架之:分页查询（没错包含你所有所有能用到的关系型数据库分页）

      本来打算再过一阵子整体介绍一下个人的Sqltoy ORM框架，但经历几家公司，发觉在数据库操作层面，就算是很大的公司仍然在使用非常古老的做法，为了给大家一点建议，分享一下有关数据库分页的做法。

     1、数据库分页一般分两步，第一：取总记录数，第二：根据页号和总记录数以及每页条数封装特定数据库分页查询语句。

     2、针对普通开发者，应该提供一个统一的分页封装，将取记录数和取分页记录进行有效封装，让开发者只需要一步操作即可完成分页查询，这里一般需要封装一个paginationModel，里面有pageNo，pageSize，rows<List>，totalRecord等属性。

     分页应该充分考虑性能问题，其关键点：

1、提取查询记录数的sql，一般要截取 from 前部分，组成 select count(1) from xxxxx,同时需要剔除掉 整个sql中的 group by 以及order by ，这样有助于提升sql执行性能

2、需要考虑sql语句中存在 with xxx as () 模式的语句，大多数公司的框架根本就没有考虑这种问题，导致sql一旦比较特殊查询就报错。 

      这里个人提供一下各类数据库的分页机制：

1、mysql：select * from xx   limit :start,:pageSize

2、oracle12c和sqlserver2012：select * from xxx  offset ? rows fetch next ? rows only 

3、oracle11g以及之前，分两种情况

    A：排序：SELECT * FROM (SELECT ROWNUM page_row_id,SAG_Paginationtable.* FROM (rejectWithAsSql这括号里就是你写的sql ) SAG_Paginationtable  ) WHERE  page_row_id<=? and page_row_id >?

    B：不排序：SELECT * FROM (SELECT ROWNUM page_row_id,SAG_Paginationtable.* FROM   (rejectWithAsSql这括号里就是你写的sql ) SAG_Paginationtable where ROWNUM <=?  ) WHERE  page_row_id >?

4、sqlserver2012之前的版本

    SELECT sag_sqlserver_tmp.*
   FROM (SELECT ROW_NUMBER() OVER(order by tempColumn) as page_row_id,SAG_Paginationtable.*
   FROM ( select top 40 tempColumn=0,* from rejectWithAsSql ) SAG_Paginationtable ) sag_sqlserver_tmp
where sag_sqlserver_tmp.page_row_id >?

   sqlserver第一个top 地方不可以用？形式，必须用数字替代进去。

   （当然sqlserver的处理还需要考虑是否union查询,如果是union则必须将整个查询语句包裹起来增加select  * from（你的unionsql） as xxx）

5、db2：

   SELECT t_sag_pageTable.* FROM
        (SELECT rownumber() over() as page_row_id,SAG_Paginationtable.*
         FROM (rejectWithAsSql:你的sql语句被裹在这里 ) SAG_Paginationtable ) t_sag_pageTable where t_sag_pageTable.page_row_id <=? and t_sag_pageTable.page_row_id >?

6、informix：

   SELECT SKIP ? FIRST ?  SAG_Paginationtable.* FROM (rejectWithSql) SAG_Paginationtable

7、POSTGRESQL和sqlite

    select * from xxxtable limit ? offset ?

8、sybase ASE

   sybase得通过临时表，比较变态一点，但一定要注意方式(临时表的名字建议用"#SAG_TMP_" + System.nanoTime()来组成)，一定先用top提取部分数据插入临时表，避免全量查询插入临时表

   select top ？ page_row_id=identity(12),sag_tmp_table.* into #tmptable from （你的sql） sag_tmp_table

9、sybase iq：

    类似于ASE，但注sql中的 order by，IQ子查询中不支持order by，需要将order by 提取到外边

 

   当然分页过程中有些数据库需要注意一些细节，以sybase 为例首先分简单sql和复杂sql，简单sql其实就不需要变成select * from （你的sql） into #table 这种模式，可以直接select top ?,* from xxx into #tmp,即在你得sql查询部分剔除select 换成select top ? ,

    短短这些应该无法全部表达需要注意的点和优化的地方，主要是给大家点建议。要完整的实现各类数据库的有效分页机制，确实需要大量的项目实践，sqltoy经历了无数项目，也经历了几乎所有能用到的关系型数据库，有意的去发展才收集了这些问题的解决机制。

     当然sqltoy的特性并不是简单的强调分页，它已经成为一个完整的O/R Mapping解决机制，集mybatis和hibernate的优点，同时有自己的特色，下次会完整介绍并进行开源。