Spring 数据可分页和 LIMIT/OFFSET [英] Spring data Pageable and LIMIT/OFFSET

问题描述

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

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

但是，我们的前端之一是 SmartGWT，它们的数据绑定组件仅使用限制/偏移量逐步加载数据，因此很难使用 Pageable.

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.

这会导致问题，因为不确定是否可以将限制/偏移量最终转换为页码.(它可能会因用户滚动方式、屏幕大小调整等而异).

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

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

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

想知道是否有人有任何指示?最佳情况下，我想继续使用限制/偏移量，但在我的 Repository 接口中使用它们，而不必像我现在一样编写实现和手动设置它们(query.setMaxResults 等)

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

澄清为什么我有问题 - smartgwt 组件中的初始和后续数据提取之间的限制/偏移量可能不同.对于列表网格，例如，第一次获取的限制可能设置为 89，因为这是屏幕上可见的行数，偏移量为 0.不过，下一个请求可能有偏移 89，限制为 50，因为这是组件的数据页面大小"" 值为 50，因此当我向下滚动时，它将获取到的值.如果我在发布之前向下滚动到很远的地方，它可能会根据设置而获取例如第 159-209 行.基本上，不能保证偏移量是任何东西的倍数.很难将偏移量17，限制为5 转换为一页.

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.

推荐答案

Pagebale implementations do 使用 limit 和 offset 来创建分页.构造函数中的page值用于生成AbstractPageRequest类getOffset方法中的偏移值:

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:

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

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

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:

分页实现

public class BetterPageRequest implements Pageable {

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

    // Other method implementations

}

HandlerMethodArgumentResolver 实现

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]);
    }

}

这篇关于Spring 数据可分页和 LIMIT/OFFSET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！