问题描述：
1：我们使用PageHelper插件的时候，PageHelper.startPage(pageNow,pageSize)要放在查询语句的前面
2：当startPage的后面有多次查询的话，它只对第一条查询语句有效果
3：假如要进行多次查询，然后对结果进行分页，关注点是：对最后的结果进行分页，而不是第一次查询出来的结果
最终解决办法有2种：第一种：依旧是用老套路，用原始自己写的PageUtil做【因为里面存放的数据就是List的，所以将最终查询的结果（不管你查询多少次）直接放进去即可】
第二种：重新写一个SQL语句，解决问题所需【这样就只需查询一次】
效果如下：
开始代码如下：
@Override //重写父类的无条件分页查询 public PageUtil queryAllByPage(int pageNow, int pageSize) { //先定义一个存放查询结果的地方 List<SpecsParamVO> specsParam = new ArrayList<>(); //先实例化一个Example对象 TbSpuExample example = new TbSpuExample(); //设置分页的数据 PageHelper.startPage(pageNow,pageSize); //无条件查询tb_spu表中的所有数据 List<TbSpu> tbSpus = spuMapper.selectByExample(example); log.info("共查询到："+tbSpus.size()+"条数据"); //遍历每一条数据的id for (TbSpu spu:tbSpus){ //根据id查询tb_sku表，每个id对应多少条数据 TbSkuExample skuExample = new TbSkuExample(); //利用skuExample进行条件查询 TbSkuExample.Criteria criteria = skuExample.createCriteria(); //in条件查询 //涉及：long类型转integer类型： //criteria.andSpuIdEqualTo(Integer.valueOf(spu.getId()+"")); criteria.andSpuIdEqualTo(Integer.valueOf(spu.getId().toString())); //开始查询 List<TbSku> tbSkus = skuMapper.selectByExample(skuExample); //对应的条数就是规格数量 long specsCount = tbSkus.size(); log.info("id为："+spu.getId()+"查询到的规格数量一共是："+specsCount); //根据id查询tb_spu_detail表，统计参数数量 TbSpuDetail detail = spuDetailMapper.selectByPrimaryKey(spu.getId()); //获取detail的specifications，即所有的参数 try { String[] split = detail.getSpecifications().split("group"); //对应的数量就是参数数量 long paramCount = split.length-1; log.info("id为："+spu.getId()+"查询到的参数数量一共是："+paramCount); //存放每条数据 SpecsParamVO vo = new SpecsParamVO(spu.getId(), spu.getTitle(), paramCount, specsCount); specsParam.add(vo); }catch (Exception e){ log.info("该条数据字符串切分异常："+spu.getId()); //存放每条数据 SpecsParamVO vo = new SpecsParamVO(spu.getId(), spu.getTitle(), (long)0, specsCount); } } //使用pageInfo对结果进行封装 PageInfo<SpecsParamVO> pageInfo = new PageInfo<>(specsParam); log.info(pageInfo.getTotal()); //始终是15条数据 log.info(pageInfo.getPages()); //总页数也是只有1条 return pageInfo; }
解决方法一：
1.重新编写一个工具类PageUtil[粘贴即可用]
package com.blb.util; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Set; public class PageUtil { /** * 总记录数 */ private int totalCount; /** * 每页的条数 */ private int pageSize; /** * 总页数 */ private int totalPage; /** * 当前是第几页 */ private int pageNow; /** * 当前页的数据 */ private List data; /** * 当前页的下一页是几 */ private int next; /** * 当前页的上一页是几 */ private int last; /** * 是否有下一页 */ private boolean hasNext; /** * 是否有上一页 */ private boolean hasLast; /** * 是否是第一页 */ private boolean isFirst; /** * 是否是最后一页 */ private boolean isLast; /** * 自定义的页码的格式 */ private int num = 7; /** * 页码数 */ private List pages = new ArrayList<>(); // 用于循环的第一个 private int forMin; // 用于循环的最后一个 private int forMax; private String buidUrl; /** * 分页时携带的查询条件 */ private Map param; public String getBuidUrl() { StringBuffer url = new StringBuffer(); if (null != param && param.size() > 0) { Set<Map.Entry<String, String>> entrySet = param.entrySet(); for (Map.Entry<String, String> e : entrySet) { url.append("¶m." + e.getKey() + "=" + e.getValue()); } } return url.toString(); } public PageUtil(int totalCount, int pageNow, int pageSize, Map param) { this.totalCount = totalCount; this.pageSize = pageSize; this.pageNow = pageNow; this.param = param; // 先把总页数算出来 this.totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize) + 1; this.next = pageNow + 1; this.last = pageNow - 1; if (this.totalPage == 1) { this.isFirst = true; this.isLast = true; this.hasLast = false; this.hasNext = false; } else { if (this.pageNow <= 1) { this.pageNow = 1; this.last = 1; this.isFirst = true; this.hasLast = false; this.hasNext = true; this.isLast = false; } else if (this.pageNow >= this.totalPage) { this.pageNow = this.totalPage; this.next = this.totalPage; this.isFirst = false; this.isLast = true; this.hasLast = true; this.hasNext = false; } else { this.isFirst = false; this.isLast = false; this.hasLast = true; this.hasNext = true; } } // 设置最大值和最小值 int min = (int) (this.pageNow - Math.floor(num / 2)); int max = (int) (this.pageNow + Math.floor(num / 2)); if (totalPage >= num) { if (min < 1) { forMin = 1; forMax = num; for (int i = forMin; i <= forMax; i++) { pages.add(i); } return; } if (max > totalPage) { forMax = totalPage; forMin = totalPage - num + 1; for (int i = forMin; i <= forMax; i++) { pages.add(i); } return; } for (int i = min; i <= max; i++) { pages.add(i); } } else { for (int i = 1; i <= totalPage; i++) { pages.add(i); } } } public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public int getPageNow() { return pageNow; } public void setPageNow(int pageNow) { this.pageNow = pageNow; } public List getData() { return data; } public void setData(List data) { this.data = data; } public int getNext() { return next; } public void setNext(int next) { this.next = next; } public int getLast() { return last; } public void setLast(int last) { this.last = last; } public boolean getHasNext() { return hasNext; } public void setHasNext(boolean hasNext) { this.hasNext = hasNext; } public boolean getHasLast() { return hasLast; } public void setHasLast(boolean hasLast) { this.hasLast = hasLast; } public boolean getIsFirst() { return isFirst; } public void setFirst(boolean isFirst) { this.isFirst = isFirst; } public boolean getIsLast() { return isLast; } public void setLast(boolean isLast) { this.isLast = isLast; } public int getNum() { return num; } public void setNum(int num) { this.num = num; } public List getPages() { return pages; } public void setPages(List pages) { this.pages = pages; } public Map getParam() { return param; } public void setParam(Map param) { this.param = param; } }
2.service的代码修改
@Override //重写父类的无条件分页查询 public PageUtil queryAllByPage(int pageNow, int pageSize) { //先定义一个存放查询结果的地方 List<SpecsParamVO> specsParam = new ArrayList<>(); //先实例化一个Example对象 TbSpuExample example = new TbSpuExample(); //设置分页的数据 PageHelper.startPage(pageNow,pageSize); //无条件查询tb_spu表中的所有数据 List<TbSpu> tbSpus = spuMapper.selectByExample(example); log.info("共查询到："+tbSpus.size()+"条数据"); //遍历每一条数据的id for (TbSpu spu:tbSpus){ //根据id查询tb_sku表，每个id对应多少条数据 TbSkuExample skuExample = new TbSkuExample(); //利用skuExample进行条件查询 TbSkuExample.Criteria criteria = skuExample.createCriteria(); //in条件查询 //涉及：long类型转integer类型： //criteria.andSpuIdEqualTo(Integer.valueOf(spu.getId()+"")); criteria.andSpuIdEqualTo(Integer.valueOf(spu.getId().toString())); //开始查询 List<TbSku> tbSkus = skuMapper.selectByExample(skuExample); //对应的条数就是规格数量 long specsCount = tbSkus.size(); log.info("id为："+spu.getId()+"查询到的规格数量一共是："+specsCount); //根据id查询tb_spu_detail表，统计参数数量 TbSpuDetail detail = spuDetailMapper.selectByPrimaryKey(spu.getId()); //获取detail的specifications，即所有的参数 try { String[] split = detail.getSpecifications().split("group"); //对应的数量就是参数数量 long paramCount = split.length-1; log.info("id为："+spu.getId()+"查询到的参数数量一共是："+paramCount); //存放每条数据 SpecsParamVO vo = new SpecsParamVO(spu.getId(), spu.getTitle(), paramCount, specsCount); specsParam.add(vo); }catch (Exception e){ log.info("该条数据字符串切分异常："+spu.getId()); //存放每条数据 SpecsParamVO vo = new SpecsParamVO(spu.getId(), spu.getTitle(), (long)0, specsCount); } }
PageUtil pageUtil = new PageUtil((int) spuMapper.countByExample(example), pageNow, pageSize, null); pageUtil.setData(specsParam); return pageUtil; }
3.jsp页面分页代码
<!--分页--> <div id="u1950-${pageUtil.data.size()+1}" class="preeval" style=" 1100px; height: 49px;"> <div id="u1961_div" class="ax_default _表格 u1951" style="visibility: inherit"> <div class="pages"> <ul> <li> <span>共</span><span style="color:#FF3C2C;">${pageUtil.totalPage}</span><span>页/</span><span style="color:#FF0000;">${pageUtil.totalCount}</span><span>条数据</span> </li> <c:if test="${!pageUtil.isFirst}"> <li> <a href="${pageContext.request.contextPath}/specsParam/1/15">首页</a> </li> </c:if> <c:if test="${pageUtil.hasLast}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${pageUtil.last}/15">上一页</a> </li> </c:if> <c:forEach items="${pageUtil.pages}" var="page"> <c:if test="${pageUtil.pageNow eq page}"> <li> <a id="curr" href="${pageContext.request.contextPath}/specsParam/${page}/15">${page}</a> </li> </c:if> <c:if test="${pageUtil.pageNow ne page}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${page}/15">${page}</a> </li> </c:if> </c:forEach> <c:if test="${pageUtil.hasNext}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${pageUtil.next}/15">下一页</a> </li> </c:if> <c:if test="${!pageUtil.isLast}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${pageUtil.totalPage}/15">尾页</a> </li> </c:if> </ul> </div> </div> </div> </div>
解决办法二：
第一种办法显得太low了，所以我们通过写一个SQL语句解决问题，只用查询一次，也就不用自己写工具类了
1.写一个数据类用于存放最后查询出来的结果
package com.blb.vo; import lombok.Data; @Data public class SpecsParamVO { private Long id; private String title; private Long specsCount; private String paramCount; public SpecsParamVO(Long id, String title, Long specsCount, String paramCount) { this.id = id; this.title = title; this.specsCount = specsCount; this.paramCount = paramCount; } public SpecsParamVO() { } }
2.在mapper.xml中添加SQL语句
还有在对应的mapper接口添加该方法：
List<SpecsParamVO> getAll_SpecsParam();
<select id="getAll_SpecsParam" resultType="com.blb.vo.SpecsParamVO"> select s.id, s.title,specifications as paramCount,count(s.id) as specsCount from (select * from tb_spu spu left join tb_spu_detail detail on spu.id = detail.spu_id) s left join tb_sku sku on s.id = sku.spu_id group by s.id </select>
3.写service层的代码
特别注意：要注意对结果进行封装的位置
【你是先查询，然后对结果进行字符串切分等操作，做完之后再进行封装】 --> 该做法会出问题，导致PageHelper分页不了
【你是先查询，然后将结果进行封装，封装完之后将需要做字符串切分等操作取出来，做完操作之后重新设置回去(setList)】--> 完美解决问题
@Override //通过SQL语句解决多次查询的问题 public PageInfo queryAllByPage2(int pageNow, int pageSize) { //定义一个List集合，存放查询出来的数据 ArrayList<SpecsParamVO> info = new ArrayList<>(); //设置分页的数据 PageHelper.startPage(pageNow,pageSize); //只需查询一次即可 List<SpecsParamVO> list = spuMapper.getAll_SpecsParam();
//需要特别注意对查询结果封装的位置 PageInfo<SpecsParamVO> infos = new PageInfo<>(list); //特别注意...... log.info(infos.getTotal()); log.info(infos.getPages()); //遍历查询出来的数据--为了获取所有参数值 for (SpecsParamVO sp : infos.getList()){ String specifications = sp.getParamCount(); //对字符串做切分操作 try { String[] split = specifications.split("group"); //对应的数量就是参数数量 long paramCount = split.length-1; log.info("id为："+sp.getId()+"查询到的参数数量一共是："+paramCount); //存放每条数据 SpecsParamVO vo = new SpecsParamVO(sp.getId(), sp.getTitle(), sp.getSpecsCount(), paramCount+""); info.add(vo); }catch (Exception e){ log.info("该条数据字符串切分异常："+sp.getId()); //存放每条数据 SpecsParamVO vo = new SpecsParamVO(sp.getId(), sp.getTitle(), sp.getSpecsCount(), "0"); info.add(vo); } } infos.setList(info);
错误做法： // //对结果进行封装 // PageInfo<SpecsParamVO> pageInfo = new PageInfo<>(info); // log.info(pageInfo.getTotal()); // log.info(pageInfo.getPages()); return infos; }
4.jsp页面分页代码
<!--分页--> <div id="u1950-${pageInfo.list.size()+1}" class="preeval" style=" 1100px; height: 49px;"> <div id="u1961_div" class="ax_default _表格 u1951" style="visibility: inherit"> <div class="pages"> <ul> <li> <span>共</span><span style="color:#FF3C2C;">${pageInfo.pages}</span><span>页/</span><span style="color:#FF0000;">${pageInfo.total}</span><span>条数据</span> </li> <c:if test="${!pageInfo.isFirstPage}"> <li> <a href="${pageContext.request.contextPath}/specsParam/1/15">首页</a> </li> </c:if> <c:if test="${pageInfo.hasPreviousPage}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${pageInfo.lastPage}/15">上一页</a> </li> </c:if> <c:forEach items="${pageInfo.navigatepageNums}" var="page"> <c:if test="${pageInfo.pageNum eq page}"> <li> <a id="curr" href="${pageContext.request.contextPath}/specsParam/${page}/15">${page}</a> </li> </c:if> <c:if test="${pageInfo.pageNum ne page}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${page}/15">${page}</a> </li> </c:if> </c:forEach> <c:if test="${pageInfo.hasNextPage}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${pageInfo.nextPage}/15">下一页</a> </li> </c:if> <c:if test="${!pageInfo.isLastPage}"> <li> <a href="${pageContext.request.contextPath}/specsParam/${pageInfo.pages}/15">尾页</a> </li> </c:if> </ul> </div> </div> </div>
PageHelper遇到问题可以参考：https://www.cnblogs.com/my12/p/10096076.html
PageHelper分页失效原因：https://blog.csdn.net/qq_40995335/article/details/80845914