CODEWITHSUNDEEP
mysqlspring-bootspring-data-jpa
zhuguowei
zhuguowei

Reputation: 8477

How to limit select count globally in spring data jpa?

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

Upvotes: 2

Views: 1093

Answers (1)

NikolaB
NikolaB

Reputation: 4936

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

Upvotes: 1

Related Questions