Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring data Pageable and LIMIT/OFFSET

i'm looking at migrating our traditional jpa/dao solution to 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.

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.

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

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.

like image 275
Mathias Avatar asked May 13 '15 14:05

Mathias


People also ask

Can we use limit in JPQL?

JPQL does not provide a mechanism to limit queries. This is most often achieved by using the setMaxResults() method on the Query . If you must avoid specifying this in Java code, you could make a view in the database that contains your query and performs the limit.

How do you set limits in Jparepository?

JPA Setup In our repository method, we use the EntityManager to create a Query on which we call the setMaxResults() method. This call to Query#setMaxResults will eventually result in the limit statement appended to the generated SQL: select passenger0_.id as id1_15_, passenger0_.

How does Spring Pageable work?

In Spring Data, if we need to return a few results from the complete data set, we can use any Pageable repository method, as it will always return a Page. The results will be returned based on the page number, page size, and sorting direction.


2 Answers

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

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:

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

}
like image 91
woemler Avatar answered Sep 30 '22 18:09

woemler


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 this is what you want

like image 27
Almas Abdrazak Avatar answered Sep 30 '22 19:09

Almas Abdrazak