mybatis oracle rownum 报错,mybatis-paginator oracle取区间数据

目前在使用中发现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 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 orders) {

this(page, limit, orders, true);

}

public PageBounds(int page, int limit, List 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 getOrders() {

return this.orders;

}

public void setOrders(List 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地址如下:

只修改了Oracle部分区间取数据的功能，地址如下:

后续有时间再完善。