问题描述:
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="width: 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="width: 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>