用Java实现异构数据库的高效通用分页查询功能

不同数据库的分页查询语句有着较大区别，其中MySQL数据的limit offset语法最为简单，而SQL Server数据库和Oracle数据库的分页就比较复杂了。

网上常见的SQL Server和Oracle数据库的分页语句都或多或少对表结构有要求，比如必须有递增的主键ID等。

 

当我们不能确定所连查询数据表的表结构（比如表是客户动态提供的）时，如何以一种通用且高效的方式在不同数据库上实现分页查询功能呢？

 

解决方案1：使用JDBC的通用分页功能

第一步：利用java.sql.Statement#setMaxRows方法，设置本次查询的最大记录数。

第二步：查询结束后，利用java.sql.ResultSet#absolute方法，跳过前面不属于查询页的记录。

这种方法的优点是简单通用。它的缺点也很明显，就是性能问题，比如查询最后一页数据时，JDBC实现原理是先查出所有数据，然后跳过前面页的数据，性能损伤很大。

如果你确定你所处的业务场景，用户通常只会触发前几页的查询（比如搜索场景：如果前几页没有自己想要的东西，通常是缩小查询范围重新搜索，而不是逐页往后去查找），那么这种简单且通用的方法不失为一种性价比较高的选择。

 

解决方案2：使用各类数据库的通用分页SQL语句

这是Hibernate的解决思路，由于最终将向数据库发送的是数据库相关的特定分页SQL语句，数据库有充分的信息进行优化，且最终只返回一页的记录。

现在问题的关键在于各类数据库的通用（对表结构没有限制）分页查询语句格式是怎样的呢？

下面将一一介绍。

注：为了便于举例，下面的SQL语句均实现的是查询一张名为test表中的column1、column2、column3字段的值，查询时设置了基于column1的过滤条件，同时可选是否基于column1排序。column1、column2、colum3没有任何特殊性，读者可以将下述SQL格式套用在任何表结构上，同时可以丰富过滤条件。

MySQL/MariaDB数据库（不排序）

SELECT column1, column2, column3 FROM test WHERE column1 = ? LIMIT 1000 OFFSET 0

MySQL/MariaDB数据库（排序）

SELECT column1, column2, column3 FROM test WHERE column1 = ? ORDER BY column1 ASC LIMIT 1000 OFFSET 0

SQL Server数据库（不排序）

SELECT column1, column2, column3 FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROWNUM,* FROM test WHERE column1 = ?)t WHERE t.ROWNUM > 0 AND t.ROWNUM <= 1000

SQL Server数据库（排序）

SELECT column1, column2, column3 FROM (SELECT ROW_NUMBER() OVER ( ORDER BY column1 ASC) AS ROWNUM,* FROM test WHERE column1 = ?)t WHERE t.ROWNUM > 0 AND t.ROWNUM <= 1000

 Oracle/达梦数据库（不排序）

SELECT column1, column2, column3 FROM (SELECT ROWNUM RN,t.* FROM (SELECT * FROM test WHERE column1 = ?)t WHERE ROWNUM <= 1000) WHERE RN > 0

Oracle/达梦数据库（排序）

SELECT column1, column2, column3 FROM (SELECT ROWNUM RN,t.* FROM (SELECT * FROM test WHERE column1 = ? ORDER BY column1 ASC)t WHERE ROWNUM <= 1000) WHERE RN > 0

 这种方法的优点是效率高，缺点是需要学习不同种类的数据库的通用分页SQL语句格式。

希望本文总结的这几种常见数据库的通用分页SQL格式能够覆盖你的需求，为你带来帮助。