亲宝软件园·资讯

展开

Spring Boot 集成PageHelper

剑圣无痕  人气:0

前言:

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

一、基本集成

引入jar包

  <dependency>
		<groupId>com.github.pagehelper</groupId>
		<artifactId>pagehelper-spring-boot-starter</artifactId>
		<version>${pagehelper.version}</version>
 </dependency>

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

pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql

封装相关分页方法

分页参数类
 public class PageParam<T> 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<T> implements Serializable
{
    private static final long serialVersionUID = -1253790062865437768L;
    private int pageNum = 1;
    private List<T> 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<T> data)
    {
        this.data = data;
        if (ObjectUtil.isNotEmpty(data) && this.recordCount == -1)
        {
            this.recordCount = data.size();
        }
    }
    public List<T> 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 <T> PagedList<T> exportPagedList(PageParam<T> pageParam)
    {
        PagedList<T> pl = new PagedList<T>();
        // 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 <T>PagedList<T> toPageList(PageInfo<T> spage)
    {
        PagedList<T> pagedList = new PagedList<T>();
        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<TUser> pageParm)
    {
       //接收参数
        PagedList<TUser> pl =PageUtils.exportPagedList(pageParm);
        return Result.success(userService.queryPageList(pl, pageParm.getParam()));
    }
  public PagedList<TUser> queryPageList(PagedList<TUser> page,TUser user)
    {
       PageInfo<TUser> 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分页语句,需要自定义SQL的count语句如何实现呢?

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

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

<select id="queryPageList_COUNT" resultType="java.lang.Long">
	 select count(1) from t_user  u 
	 left join t_user_role ur on ur.userOid=u.oid
	 <where>
	  <if test="name != null">name=#{name}</if>
	 </where>
</select>

四、分页失效的常见的场景有哪些?

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

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

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

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

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

示例代码:

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

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

示例代码:

    public PageInfo<TUserVO> getUserPageList(int pageNum, int pageSize) {       
        List<TUserVO> tUserVOsByView = userMapper.getUserList();
        List<TUserVO> TUserVOs = new ArrayList<>();
        for (TUserVO TUserVO : tUserVOsByView) {
            TUserVO TUserVOSingle = new TUserVO();
            TUserVOSingle.setHdId(TUserVO.getHdId());
        }
        PageHelper.startPage(pageNo,pageSize);
        PageInfo<TUserVO> 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 d
INNER JOIN 
    (SELECT oid FROM tag_detail LIMIT 10000000,10) t
ON d.oid= t.oid;

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

总结:

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

加载全部内容

相关教程
猜你喜欢
用户评论