How to limit select count globally in spring data jpa?

Question

Latest project I used Spring boot and Spring Data Jpa, and in some web interface user can dynamic search some records，e.g. search order by createdDate, search goods by name and so on. Because exist many search conditions, e.g name,brand,status and so on. So I used Jpa Specification to implement dynamic search. So page could pass below query parameter to search records, e.g.

GET /goods?search_LIKE_name=foo #fuzzy search goods by name
GET /goods?search_LIKE_name=foo&search_EQ_status=1 #fuzzy search goods by name and status

and so on.

But if page pass nothing, then it will select all. I don't want this happen. And I know mysql command line have an option called --select_limit, So how could I configure it globally to limit select count? e.g. in application.properties

spring.sql_select_limit=1000

NikolaB · Accepted Answer · 2015-11-19 12:50:03Z

1

I do not know if there is a global configuration, but i have a relatively neat local solution. On controller method just use annotation @PageableDefaults(pageNumber = 0, value = x) before pageable.

Controller method:

public List<Item> fuzzySearch(Object filter, @PageableDefaults(pageNumber = 0, value = x) Pageable pageable) {}

If page parameters are missing default values specified in annotation will be used.

edit:

If you manage to rename paging parameters from request to size and page and use method signature with Pageable as parameter you can setup page default globally instead of putting PageableDefaults annotation where needed. Just override method in the @Configuration class that extends WebMvcConfigurerAdapter.

@Override
public void addArgumentResolvers(List<HandlerMethodArgumentResolver> argumentResolvers) {
    PageableHandlerMethodArgumentResolver resolver = new PageableHandlerMethodArgumentResolver();
    resolver.setFallbackPageable(new PageRequest(0, 1000));
    argumentResolvers.add(resolver);
    super.addArgumentResolvers(argumentResolvers);
}

edited Nov 19, 2015 at 12:50

answered Nov 17, 2015 at 17:37

NikolaB

4,9863 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zhuguowei Over a year ago

Thanks! But I have no pageable parameter, I directly parse request parameter. public List<Item> fuzzySearch(HttpServletRequest request) {}. If have no pageNumber in request, I'll explicitly assign a pageRequest parameter like this: findAll(spec,new PageRequest(0, 1000));

NikolaB Over a year ago

Ok, but if you have parameters named size and page in the request and you add Pageable as method parameter they will be automatically inferred as declared Pageable. So you could still use my answer.

Collectives™ on Stack Overflow

How to limit select count globally in spring data jpa?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related