这篇文章主要介绍了Spring Boot怎么集成PageHelper使用的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇Spring Boot怎么集成PageHelper使用文章都会有所收获,下面我们一起来看看吧。
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>${pagehelper.version}</version> </dependency>
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过滤处理,关于这个问题其实在上述的分页封装类中已经进行了解决
示例代码:
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 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分页是自动在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秒左右。性能大幅度提升。
关于“Spring Boot怎么集成PageHelper使用”这篇文章的内容就介绍到这里,感谢各位的阅读!相信大家对“Spring Boot怎么集成PageHelper使用”知识都有一定的了解,大家如果还想学习更多知识,欢迎关注亿速云行业资讯频道。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。