• 欢迎访问少将全栈,学会感恩,乐于付出,珍惜缘份,成就彼此、推荐使用最新版火狐浏览器和Chrome浏览器访问本网站。
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏少将全栈吧
  • 欢迎加博主微信:jiang_shaobo

mybatis-paginator oracle取区间数据

点滴 admin 3年前 (2016-08-13) 346次浏览 已收录 扫描二维码

目前在使用中发现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/mybatispaginator/issues/19

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

https://github.com/sxyseo/mybatispaginator

后续有时间再完善。

喜欢 (0)
[🍬谢谢你请我吃糖果🍬🍬~]
分享 (0)
关于作者:
少将,关注Web全栈开发、项目管理,持续不断的学习、努力成为一个更棒的开发,做最好的自己,让世界因你不同。