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的优点,同时有自己的特色,下次会完整介绍并进行开源。

 

    

    


版权声明:本文为iteye_2252原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接和本声明。