不再东寻西找，常见数据库分页方法都在这里了

茅乙丹寒

已于 2022-01-21 14:17:37 修改

阅读量1.7k

点赞数

文章标签：数据库 sql mysql java

于 2022-01-21 14:15:19 首次发布

本文链接：https://blog.csdn.net/u012505684/article/details/122615704

版权

在日常的开发中，做数据分页是很常见的需求了，面对各种各样的数据库或者是多数据源，有没有一种可以共用的实现方法呢，在明确数据量不大的情况下，逻辑分页不失为一种快速通用的方法，因为无须对sql语句进行修改，可以无视底层数据库的类型，不过这种方式的缺点也是显而易见的，一来就是效率低，二来会对应用端服务造成压力，所以一般情况下我们都会使用物理分页，把具体的实现和运算交给数据库服务。

物理分页的方式由数据库提供，效率高，但是不同的数据库对于分页的实现各有差异，所以首先要看一下不同数据库对于分页的sql实现：

在开始之前，我们首先定义几个常量，用以标记我们在具体实现中要替换的变量值：

String sqlMark = "#SQL";   本次分页的原有sql语句，例如：select * from order_info

String startMark = "#START_COUNT";   本次分页的开始行数

String endMark = "#END_COUNT";   本次分页的结束行数

String orderMark = "#ORDER";   本次分页的排序列（仅针对sqlserver）

String columnNames = "#COLUMN_NAMES";   本次分页最后要返回的列名（按需拿部分列或全部）

MySQL

select #COLUMN_NAMES from (#SQL) A limit #START_COUNT,#END_COUNT

SQL Server

select #COLUMN_NAMES from (select *,ROW_NUMBER() OVER(order by #ORDER ) AS RowId from (#SQL) AS A ) AS B where RowId between #START_COUNT and #END_COUNT

Oracle

SELECT #COLUMN_NAMES FROM (SELECT A.*,ROWNUM RN FROM (#SQL) A WHERE ROWNUM <=#END_COUNT) WHERE RN >=#START_COUNT

Hive

SELECT #COLUMN_NAMES FROM (SELECT row_number() over (ORDER BY #ORDER) AS rn,tb.* FROM (#SQL) tb) t WHERE rn > #START_COUNT AND rn < #END_COUNT

以上就是核心内容，下面来一个具体案例，一个通用的sql查询分页方法：

private Map<String,Object> startPage4Preview(String dbId, String sql, int pageIndex, int pageSize,Boolean noPagination) throws BusinessException, SQLException, RucException, IOException {
        
        Map<String, String> dbs = new HashMap<>();

        String sqlMark = "#SQL";
        String startMark = "#START_COUNT";
        String endMark = "#END_COUNT";
        String orderMark = "#ORDER";
        String columnNames = "#COLUMN_NAMES";

        dbs.put("oracle", "SELECT #COLUMN_NAMES FROM (SELECT A.*,ROWNUM RN FROM (#SQL) A WHERE ROWNUM <=#END_COUNT) WHERE RN >=#START_COUNT");
        dbs.put("mysql", "select #COLUMN_NAMES from (#SQL) A limit #START_COUNT,#END_COUNT");
        dbs.put("sqlserver", "select #COLUMN_NAMES from (select *,ROW_NUMBER() OVER(order by #ORDER ) AS RowId from (#SQL) AS A ) AS B where RowId between #START_COUNT and #END_COUNT");
        dbs.put("hive","SELECT #COLUMN_NAMES FROM (SELECT row_number() over (ORDER BY #ORDER) AS rn,tb.* FROM (#SQL) tb) t WHERE rn > #START_COUNT AND rn < #END_COUNT");

        boolean isFirstPage=false;
        boolean isLastPage=false;
        boolean haveNexPage=false;
        boolean havePerPage=false;

        Integer totalPage;

        //获取查询到的总行数
        int totalRow=getSqlCounts(dbId, sql);

        if (noPagination!=null&&noPagination&&totalRow>0){
            pageSize = totalRow;
        }
        int fromIndex=(pageIndex-1)*pageSize;
        int toIndex=pageIndex*pageSize;
        if(fromIndex==0) {
            isFirstPage=true;
        }else {
            havePerPage=true;
        }
        if(toIndex>=totalRow) {
            toIndex=totalRow;
            isLastPage=true;
        }else if (!isLastPage) {
            haveNexPage=true;
        }
        if(totalRow%pageSize==0) {
            totalPage=totalRow/pageSize;
        }else {
            totalPage=totalRow/pageSize+1;
        }

        Map<String,Object> map=new HashMap<>();
        map.put("pageIndex", pageIndex);
        map.put("totalPage", totalPage);
        map.put("totalCount", totalRow);
        map.put("pageSize", pageSize);
        map.put("firstPage", isFirstPage);
        map.put("lastPage", isLastPage);
        map.put("haveNexPage", haveNexPage);
        map.put("havePerPage", havePerPage);

        String dbType = getConnectionType(dbId);
        String newSQL = dbs.get(dbType).replace(sqlMark, sql);
        if("oracle".equals(dbType)) {
            SqlInfo sqlInfo = readSQL4SQLServer(sql);
            newSQL = newSQL.replace(columnNames,sqlInfo.getColumns())
                    .replace(startMark, Integer.toString(fromIndex+1))
                    .replace(endMark, Integer.toString(toIndex));
        }else if ("mysql".equals(dbType)) {
            SqlInfo sqlInfo = readSQL4Hive(sql);
            newSQL = newSQL.replace(columnNames,sqlInfo.getColumns())
                    .replace(startMark, Integer.toString(fromIndex))
                    .replace(endMark, Integer.toString(pageSize));
        }else if ("sqlserver".equals(dbType)) {
            SqlInfo sqlInfo = readSQL4SQLServer(sql);
            newSQL = newSQL.replace(columnNames,sqlInfo.getColumns())
                    .replace(startMark, Integer.toString(fromIndex+1))
                    .replace(endMark, Integer.toString(toIndex))
                    .replace(orderMark, "(SELECT 1)");
        }else if ("hive".equalsIgnoreCase(dbType)){
            //hive的话 目前单表查询采用的是一种偷懒的方法，拆分sql获取字段名和表名
            SqlInfo sqlInfo = readSQL4Hive(sql);
            newSQL = newSQL.replace(columnNames,sqlInfo.getColumns())
                    .replace(startMark, Integer.toString(fromIndex))
                    .replace(endMark, Integer.toString(toIndex+1))
                    .replace(orderMark, sqlInfo.getFirstColumn());
        }

        List<Map<String,Object>> select = xxxService.select(dbId, newSQL);

        map.put("list", select);
        return map;
    }

关于第一个参数：dbId，是存在数据库的相关数据源的主键值，可以理解为要操作的数据源的一些基本信息，后续不再说明。其他的几个参数：要分页的sql语句，当前页码数，分页大小，是否进行分页。

总的来说，此方法就是根据不同的数据源类型生成不同的分页sql，然后交给数据库去执行，然后返回统一格式的数据，供前端使用。