Spring数据可分页和LIMIT / OFFSET

i'm looking at migrating our traditional jpa/dao solution to Spring Data.

我正在考虑将传统的jpa / dao解决方案迁移到Spring Data。

However, one of our front-ends is SmartGWT, and their databound components load data progressively using limit/offset only, making it hard to use Pageable.

但是,我们的一个前端是SmartGWT,它们的数据绑定组件仅使用限制/偏移量逐步加载数据,这使得难以使用Pageable。

This causes problems, since it's not certain that the limit/offset can be translated evently into a page number. (it might differ depending on how the user scrolls, screen is resized etc.).

这会导致问题,因为不能确定限制/偏移是否可以最终转换为页码。 (它可能会有所不同,具体取决于用户如何滚动,屏幕调整大小等)。

I looked at Slice etc, but wasn't able to find a way to use the limit/offset values anywhere.

我查看了Slice等,但无法找到在任何地方使用限制/偏移值的方法。

Was wondering if someone has any pointers? Optimally i would like to continue using limit/offset, but use them in my Repository interfaces without having to code an implementation and set them manually like i do now (query.setMaxResults etc.)

想知道是否有人有任何指针?最好我想继续使用limit / offset,但是在我的Repository接口中使用它们而不必编写实现并像我现在那样手动设置它们(query.setMaxResults等)

Edit: To clarify why i have issues - The limit/offset might differ between the initial and subsequent data fetches in a smartgwt component. For a listgrid, the first fetch might have limit set to 89 for example, since that's the amount of rows visible on screen, and offset 0. The next request, though, might have offset 89, and limit 50 since that's the component's "datapagesize" value to 50, so that's what it'll fetch when i scroll down. If i scroll to far down before releasing, it might, depending on the settings, fetch for example rows 159-209 instead. Basically, there's no guarantee that the offset is a multiple of anything. It's hard to convert offset 17, limit 5 to a page.

编辑:澄清我遇到问题的原因 - 在smartgwt组件中初始和后续数据提取之间的限制/偏移可能不同。对于listgrid,第一次获取可能将限制设置为89,例如,因为这是屏幕上可见的行数,并且偏移量为0.但是,下一个请求可能具有偏移量89,并且限制为50,因为这是组件的“datapagesize” “值为50,这就是我向下滚动时所取的东西。如果我在发布之前滚动到远处,它可能会取决于设置,例如取代行159-209。基本上,不能保证偏移量是任何东西的倍数。将偏移17,限制5转换为页面很难。

2 个解决方案

#1

Pagebale implementations do use limit and offset to create pagination. The page value in the constructor is used to generate the offset value in the AbstractPageRequest class getOffset method:

Pagebale实现确实使用limit和offset来创建分页。构造函数中的页面值用于在AbstractPageRequest类getOffset方法中生成偏移值:

public int getOffset() {
    return this.page * this.size;
}

If you want to only use limit and offset and discard the page parameter from the mix, take a look at the Spring Data documentation on web support, particularly the part about overriding the default configuration. You could create your own implementation of Pageable that takes limit and offset as constructor arguments and the implement your own HandlerMethodArgumentResolver to replace the standard PageRequest resolving. Quick-and-dirty example:

如果您只想使用限制和偏移并从混合中丢弃页面参数,请查看Web支持上的Spring Data文档,特别是有关覆盖默认配置的部分。您可以创建自己的Pageable实现,它将限制和偏移作为构造函数参数,并实现您自己的HandlerMethodArgumentResolver来替换标准的PageRequest解析。快速而肮脏的例子:

Pageable implementation

public class BetterPageRequest implements Pageable {

    public BetterPageRequest(int limit, int offset){
        this.limit = limit;
        this.offset = offset;
    }

    // Other method implementations

}

HandlerMethodArgumentResolver implementation

public class BetterPageableResolver implements HandlerMethodArgumentResolver {

    @Override
    public boolean supportsParameter(MethodParameter parameter){
        return Pageable.class.equals(parameter.getParameterType());
    }

    @Override
    public Object resolveArgument(MethodParameter parameter, ModelAndViewContainer container, NativeWebRequest request, WebDataBinderFactory factory){
        Map<String,String[]> params = request.getParameterMap();
        return new BetterPageRequest(params.get('limit')[0], params.get('offset')[0]);
    }

}

#2

public class OffsetLimitPageable extends PageRequest {
    private int offset;
    public OffsetLimitPageable(int offset, int limit){
        super(offset,limit);
        this.offset=offset;
    }
    @Override
    public long getOffset(){
        return this.offset;
    }
}

And example

Page<WashComment> washComments = washCommentRepository.findWashCommentByWashId_CarWashIdOrderByDateDesc(carWash, new OffsetLimitPageable(offsetNumberRepresentation,
                 limitNumberRepresentation > Config.getMaxLimitAmount() ? Config.getMaxLimitAmount() : limitNumberRepresentation));

Let me know if it what you want

如果你想要它,请告诉我

#1