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<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

后续有时间再完善。

喜欢 (0)赏

[🍬谢谢你请我吃糖果🍬🍬～]

分享 (0)