项目中数据分页是一个很常见的需求，目前大部分项目都会使用pagehelper进行分页，那么在使用的过程中是否考虑如下问题？

基本集成

引入jar包

  com.github.pagehelperpagehelper-spring-boot-starter${pagehelper.version} 复制代码

Yml配置文件中添加相关配置

pagehelper:    helperDialect: mysql    reasonable: true    supportMethodsArguments: true    params: count=countSql复制代码

封装相关分页方法

分页参数类 public class PageParam implements Serializable{    private static final long serialVersionUID = -7916211163897873899L;    private int pageNum=1;    private int pageSize=10;        //条件参数    private T param;        //排序字段    private String orderBy;        public int getPageSize()    {        return pageSize;    }    public void setPageSize(int pageSize)    {        this.pageSize = pageSize;    }    public int getPageNum()    {        return pageNum;    }    public void setPageNum(int pageNum)    {        this.pageNum = pageNum;    }    public T getParam()    {        return param;    }    public void setParam(T param)    {        this.param = param;    }    public String getOrderBy()    {        return orderBy;    }    public void setOrderBy(String orderBy)    {        //需要注意sql注入        this.orderBy = orderBy;    }}分页结果类public class PagedList implements Serializable{    private static final long serialVersionUID = -1253790062865437768L;    private int pageNum = 1;    private List data = null;    private int pageCount = 0;    private int recordCount = -1;    private int pagingType = 0;    private int pageSize;    private String orderBy;    /**     * @return the pageSize     */    public int getPageSize()    {        return pageSize;    }    /**     * @param pageSize     *            the pageSize to set     */    public void setPageSize(int pageSize)    {        if (pageSize <= 0)        {            return;        }        this.pageSize = pageSize;    }    /**     * @return the pageCount     */    public int getPageCount()    {        return pageCount;    }    /**     * @param pageCount     *            the pageCount to set     */    public void setPageCount(int pageCount)    {        if (pageCount <= 0)        {            return;        }        this.pageCount = pageCount;    }    /**     * @return the recordCount     */    public int getRecordCount()    {        return recordCount;    }    /**     * @param recordCount     *            the recordCount to set     */    public void setRecordCount(int recordCount)    {        this.recordCount = recordCount;        calcPageCount();    }    private void calcPageCount()    {        if (this.recordCount < 0)        {            return;        }        int tmp = this.recordCount % getPageSize();        this.pageCount = (tmp == 0 ? (this.recordCount / getPageSize())                : (this.recordCount / getPageSize() + 1));        if (this.pageNum > this.pageCount && this.pageCount != 0)        {            this.pageNum = this.pageCount;        }        this.pageNum = this.pageCount;    }    public void setData(List data)    {        this.data = data;        if (ObjectUtil.isNotEmpty(data) && this.recordCount == -1)        {            this.recordCount = data.size();        }    }    public List getData()    {        return data;    }    /**     * @return the pagingType     */    public int getPagingType()    {        return pagingType;    }    /**     * @param pagingType     *            the pagingType to set     */    public void setPagingType(int pagingType)    {        this.pagingType = pagingType;    }    public void setOrderBy(String orderBy)    {        this.orderBy = orderBy;    }    public int getPageNum()    {        return pageNum;    }    public void setPageNum(int pageNum)    {        this.pageNum = pageNum;    }    public String getOrderBy()    {        return orderBy;    }}分页工具类public class PageUtils implements Serializable{    private static final long serialVersionUID = 377943433889798799L;        public static  PagedList exportPagedList(PageParam pageParam)    {        PagedList pl = new PagedList();        // pagesize        int pageSize = pageParam.getPageSize();        if (pageSize <= 0)        {            pageSize = 10;        }        else        {            pl.setPageSize(pageSize);        }        int pageNum  = pageParam.getPageNum();        pl.setPageNum(pageNum);               String orderBy= pageParam.getOrderBy();       if(StringUtil.isNotEmpty(orderBy))       {           //防止sql注入           String orderBySql=SQLFilter.sqlInject(orderBy);           pl.setOrderBy(orderBySql);       }        return pl;    }                public static PagedList toPageList(PageInfo spage)    {        PagedList pagedList = new PagedList();        pagedList.setPageSize((int) spage.getPageSize());        pagedList.setPageNum((int) spage.getPageNum());        pagedList.setRecordCount((int) spage.getTotal());        pagedList.setData(spage.getList());        pagedList.setPageCount((int) spage.getPages());        return pagedList;    }}复制代码

示例代码

  @PostMapping("getPageList")    public Result getPageList(@RequestBody PageParam pageParm)    {       //接收参数        PagedList pl =PageUtils.exportPagedList(pageParm);        return Result.success(userService.queryPageList(pl, pageParm.getParam()));    }  public PagedList queryPageList(PagedList page,TUser user)    {       PageInfo pageInfo= PageHelper.startPage(page).doSelectPageInfo(()-> list(user));       //转换结果       return PageUtils.toPageList(pageInfo);    }复制代码

前段传入参数

{    "pageSize":10,    "pageNum":"1",    //查询条件     "param":{         "name":"张三210001"    },    //排序字段    "orderBy":"age desc"}复制代码

执行结果

2022-04-15 22:26:39.914 [http-nio-9090-exec-9] DEBUG [613920d89eb54bfd8601c93ec8572dcf] c.s.f.m.UserMapper.queryPageList - ==>  Preparing: SELECT * FROM t_user u LEFT JOIN t_user_role ur ON ur.userOid = u.oid WHERE name = ? order by age desc LIMIT ? 2022-04-15 22:26:39.919 [http-nio-9090-exec-9] DEBUG [613920d89eb54bfd8601c93ec8572dcf] c.s.f.m.UserMapper.queryPageList - ==> Parameters: 张三210001(String), 10(Integer)2022-04-15 22:26:40.267 [http-nio-9090-exec-9] DEBUG [613920d89eb54bfd8601c93ec8572dcf] c.s.f.m.UserMapper.queryPageList - <==      Total: 1复制代码

基础的分页查询已经发完成了，下面解答上面的问题的方法

分页中的排序字段如何防止SQL注入问题

对于前段传入的排序字段，我们需要进行SQL过滤处理，关于这个问题其实在上述的分页封装类中已经进行了解决

示例代码

public class SQLFilter{    public static String sqlInject(String str)    {        if (StringUtil.isBlank(str))        {            return null;        }        // 去掉'|"|;|\字符        str = StringUtil.replace(str, "'", "");        str = StringUtil.replace(str, "\"", "");        str = StringUtil.replace(str, ";", "");        str = StringUtil.replace(str, "\", "");        // 转换成小写        str = str.toLowerCase();        // 非法字符        String[] keywords = { "master", "truncate", "insert", "select",                "delete", "update", "declare", "alert", "drop" };        // 判断是否包含非法字符        for (String keyword : keywords)        {            if (str.indexOf(keyword) != -1)            {                throw new SysException("包含非法字符");            }        }        return str;    }}复制代码

复杂的SQL分页语句，需要自定义SQL的count语句如何实现

PageHelper实现分页，默认是查询自定义的count语句是否存在，如果存在就用自定义的语句，否则就在外层包装查询的语句，而自定义count语句只需要在在查询语句名称后面添加_COUNT即可。例如

查询集合的语句名称为queryPageList，那么查询count的语句为queryPageList_COUNT，返回Long类型即可。

 select count(1) from t_user  u  left join t_user_role ur on ur.userOid=u.oid   name=#{name} 复制代码

分页失效的常见的场景有哪些？

1.pageHelper分页查询有个特殊的要求，查询下sql语句一定要紧跟在分页查询的后面，否则分页查询会失效。之前采用的如下写法容易失效，建议采用java8的写法

  PageHelper.startPage(pagedList.getPageNum(),pagedList.getPageSize());        //紧跟分页查询后面        List list = list(user);        PageInfo pageInfo =new PageInfo<>(list);        return PageUtils.toPageList(pageInfo);复制代码

2.注意pagehelper的reasonable 默认为false，遇到查询页数大于总页数时，出现分页失败

pagehelper的reasonable 默认为false，遇到查询页数大于总页数时，查询为空；当reasonable设置为true时，遇到查询页数大于总页数时，查询最后一页数据；

3.PageHelper先开启分页，后对list数据操作将会导致分页错误

示例代码：

    public PageInfo getUserPageList(int pageNum, int pageSize) {        PageHelper.startPage(pageNum,pageSize);        List tUserVOsByView = userMapper.getUserList();        List TUserVOs = new ArrayList<>();        for (TUserVO TUserVO : tUserVOsByView) {            TUserVO TUserVOSingle = new TUserVO();            TUserVOSingle.setHdId(TUserVO.getHdId());            TUserVOs.add(TUserVOSingle);        }        PageInfo pageViewInfo = new PageInfo<>(TUserVOs);        return pageViewInfo;    }复制代码

3.PageHelper先对list数据操作，后开启分页，将会导致分页失效

示例代码：

    public PageInfo getUserPageList(int pageNum, int pageSize) {               List tUserVOsByView = userMapper.getUserList();        List TUserVOs = new ArrayList<>();        for (TUserVO TUserVO : tUserVOsByView) {            TUserVO TUserVOSingle = new TUserVO();            TUserVOSingle.setHdId(TUserVO.getHdId());        }        PageHelper.startPage(pageNo,pageSize);        PageInfo pageViewInfo = new PageInfo<>(TUserVOs);        return pageViewInfo;    }复制代码

大家需要注意下，抽时间可以去验证下结果。

大表数据PageHelper分页性能如何

PageHelper 对于大表查询数据量越大，性能越差，这是因为PageHelper分页是自动在sql语句后面拼接limit没有进行相关的优化，一旦数据大，性能就比较慢。

例如：

优化前SQL语句：

SELECT d.* FROM tag_detail d LIMIT 10000000,10 复制代码

查询的时间大概需要10秒左右，执行速度比较慢。

优化后SQL语句：

SELECT d.* FROM tag_detail dINNER JOIN     (SELECT oid FROM tag_detail LIMIT 10000000,10) tON d.oid= t.oid;复制代码

子查询先通过分页查询主键字段，然后进行关联查询，经过优化后，查询时间大概为1秒左右。性能大幅度提升。

总结

本文讲解了PageHelper的基本的使用和相关的问题，这些都是我从实际的项目中总结出来的问题以及相关的解决方案，大家在使用的时候要特别注意，不要放同样的错误。

作者：剑圣无痕

原文出处：https://juejin.cn/post/7086854293080260639