sqlServer2008版本不支持offset 分页，手动分页sql

最新推荐文章于 2024-01-30 15:32:42 发布

原创最新推荐文章于 2024-01-30 15:32:42 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

该博客内容展示了如何使用Java实现一个名为`SqlServerPageUtil`的工具类，用于将SQL查询语句转化为支持分页的查询。类中包含了一个静态方法`convert`，该方法接收SQL查询、分页对象和主键字段作为参数，通过修改原始SQL，插入ROW_NUMBER()窗口函数来实现分页。方法首先根据`from`关键字拆分SQL，然后构建带有行号的子查询，并设置分页条件。此工具对于处理大量数据的分页显示非常有用。

import cn.hutool.db.Page;

public class SqlServerPageUtil {

    public static String convert(String sql, Page page, String primaryKey) {
        //一定要注意分页是从O页开始的
        //        Page page = new Page(0,2);
        //  sql = "select [spid] as 'SKU编码',[default_price] as '售价' from [t_item] ";
        // String sql2 = "select * FROM (select ROW_NUMBER()Over(order by id desc) as rowId,* from c_class) as t where rowId between 21 and 40";
        String[] str = sql.split("from", sql.length());
        String selectSQL = str[0];
        String fromSQL = " from ";
        String tableSQL = str[1];
        String modelSQL = " (select ROW_NUMBER()Over(order by " + primaryKey + " desc ) as rowId,* from " + tableSQL + " ) as t ";
        String whereSQL = "where rowId between " + (page.getPageSize() * page.getPageNumber() + 1) + " and " + (page.getPageSize() * page.getPageNumber() + page.getPageSize());
        String exeSql = selectSQL + fromSQL + modelSQL + whereSQL;
        return exeSql;
        //System.out.println(exeSql);
    }
//    public static void main(String[] args) {
//    String         sql = "select [spid] as 'SKU编码',[default_price] as '售价' from [t_item] ";

//        Page page = new Page(0,2);
//        convert(sql,page,"id");
//    }
}