目前在使用中发现oracle取区间的数据存在问题。
`package com.github.miemiedev.mybatis.paginator.domain;
import com.github.miemiedev.mybatis.paginator.domain.Order;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.ibatis.session.RowBounds;
public class PageBounds extends RowBounds implements Serializable {
private static final long serialVersionUID = -6414350656252331011L;
public static final int NO_PAGE = 1;
protected int page;
protected int limit;
protected List orders;
protected boolean containsTotalCount;
protected Boolean asyncTotalCount;
public PageBounds() {
this.page = 1;
this.limit = 2147483647;
this.orders = new ArrayList();
this.containsTotalCount = false;
}
public PageBounds(RowBounds rowBounds) {
this.page = 1;
this.limit = 2147483647;
this.orders = new ArrayList();
if(rowBounds instanceof PageBounds) {
PageBounds pageBounds = (PageBounds)rowBounds;
this.page = pageBounds.page;
this.limit = pageBounds.limit;
this.orders = pageBounds.orders;
this.containsTotalCount = pageBounds.containsTotalCount;
this.asyncTotalCount = pageBounds.asyncTotalCount;
} else {
this.page = rowBounds.getOffset() / rowBounds.getLimit() + 1;
this.limit = rowBounds.getLimit();
}
}
public PageBounds(int limit) {
this.page = 1;
this.limit = 2147483647;
this.orders = new ArrayList();
this.limit = limit;
this.containsTotalCount = false;
}
public PageBounds(int page, int limit) {
this(page, limit, new ArrayList(), true);
}
public PageBounds(int page, int limit, boolean containsTotalCount) {
this(page, limit, new ArrayList(), containsTotalCount);
}
public PageBounds(List<Order> orders) {
this(1, 2147483647, orders, false);
}
public PageBounds(Order... order) {
this(1, 2147483647, (Order[])order);
this.containsTotalCount = false;
}
public PageBounds(int page, int limit, Order... order) {
this(page, limit, Arrays.asList(order), true);
}
public PageBounds(int page, int limit, List<Order> orders) {
this(page, limit, orders, true);
}
public PageBounds(int page, int limit, List<Order> orders, boolean containsTotalCount) {
this.page = 1;
this.limit = 2147483647;
this.orders = new ArrayList();
this.page = page;
this.limit = limit;
this.orders = orders;
this.containsTotalCount = containsTotalCount;
}
public int getPage() {
return this.page;
}
public void setPage(int page) {
this.page = page;
}
public int getLimit() {
return this.limit;
}
public void setLimit(int limit) {
this.limit = limit;
}
public boolean isContainsTotalCount() {
return this.containsTotalCount;
}
public void setContainsTotalCount(boolean containsTotalCount) {
this.containsTotalCount = containsTotalCount;
}
public List<Order> getOrders() {
return this.orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
public Boolean getAsyncTotalCount() {
return this.asyncTotalCount;
}
public void setAsyncTotalCount(Boolean asyncTotalCount) {
this.asyncTotalCount = asyncTotalCount;
}
public int getOffset() {
return this.page >= 1?(this.page - 1) * this.limit:0;
}
public String toString() {
StringBuilder sb = new StringBuilder("PageBounds{");
sb.append("page=").append(this.page);
sb.append(", limit=").append(this.limit);
sb.append(", orders=").append(this.orders);
sb.append(", containsTotalCount=").append(this.containsTotalCount);
sb.append(", asyncTotalCount=").append(this.asyncTotalCount);
sb.append(’}’);
return sb.toString();
}
}`
在PageBounds文件中使用PageBounds(int page, int limit)函数,查询返回的数据存在问题。
(由于此问题已经超出分页的主题,So……想实现如下功能)
如下:假如要查询3到9页的数据,每页10条数据,是否是这样设置?
PageBounds pageBounds = new PageBounds(page, pagesize * (endPage – page + 1));
这样的结果区间是对的,但是开始的数据不是从page开始的,而是从page * pagesize 开始。
打印如下:
where rownum_ <= ? and rownum_ > ?
2016-08-09 18:58:37,003 [http-apr-8086-exec-8] DEBUG [com.whatled.queryTest] – ==> Parameters: 210(Integer), 140(Integer)
18:58:37,003 DEBUG queryTest:139 – ==> Parameters: 210(Integer), 140(Integer)
2016-08-09 18:58:37,041 [http-apr-8086-exec-8] DEBUG [com.whatled.queryTest] – <== Total: 70
18:58:37,041 DEBUG queryTest:139 – <== Total: 70
源码如下:
private static final long serialVersionUID = -6414350656252331011L;
public final static int NO_PAGE = 1;
/** 页号 /
protected int page = NO_PAGE;
/* 分页大小 /
protected int limit = NO_ROW_LIMIT;
/* 分页排序信息 /
protected List orders = new ArrayList();
/* 结果集是否包含TotalCount */
protected boolean containsTotalCount;
protected Boolean asyncTotalCount;
public PageBounds(){
containsTotalCount = false;
}
public PageBounds(RowBounds rowBounds) {
if(rowBounds instanceof PageBounds){
PageBounds pageBounds = (PageBounds)rowBounds;
this.page = pageBounds.page;
this.limit = pageBounds.limit;
this.orders = pageBounds.orders;
this.containsTotalCount = pageBounds.containsTotalCount;
this.asyncTotalCount = pageBounds.asyncTotalCount;
}else{
this.page = (rowBounds.getOffset()/rowBounds.getLimit())+1;
this.limit = rowBounds.getLimit();
}
}
正常的查询了70条数据,但是看请求参数Parameters: 210(Integer), 140(Integer),请求到的数据是从第14页开始的(140 / 10),长度为70,所以到210条结束。
问题在PageBounds(int page, int limit)函数,如何修改可以让请求的参数为20-90共70条。
由于这个函数只返回一页内容。page为页号,limit为分页大小,那么3到9页就是从大于第二页也就是从(page – 1) * limit 开始,对应((3 – 1) * 10)于20,但是这里设置了limit为70,所以就得到了140了。
因此这一页的数据就从140到210了。
现在想增加这样一个函数
public PageBounds(int page, int endPage, int limit)
page为当前页,endpage为结束页,查询这个区间的数据。
由于这个区间的数据不分页,当成一页,那么这里就把limit变成了区间的数量。
需要调整当前页的参数即可查询出相应区间的值。
(这里不能用page * limit,而是要把page * pagesize)
那么这个功能就可以实现了。
Issues地址如下:
https://github.com/miemiedev/mybatis–paginator/issues/19
只修改了Oracle部分区间取数据的功能,地址如下:
https://github.com/sxyseo/mybatis–paginator
后续有时间再完善。