Filtering data with Spring boot CrudRepository

Question

I have a simple REST service which access data with Spring boot CrudRepository.

This repository already implements pagination and sorting capabilities like this:

public interface FlightRepository extends CrudRepository<Flight, Long> {
  List<Flight> findAll(Pageable pageable);
}

Calling it:

Sort sort = new Sort(direction, ordering);
PageRequest page = new PageRequest(xoffset, xbase, sort);

return flightRepo.findAll(page);

I would like to add also filtering to this repository (for example return only entities with id > 13 AND id < 27). The CrudRepository does not seem to support this functionality. Is there some way how to achieve this or do I need to use different approach?

Thanks for any tips!

Answer 1

An alternative, which would address your concern in the comments above about having to create query methods for every combination of parameters, is to use the Specification pattern via either the Criteria API or by using QueryDSL.

Both approaches are outlined at the below in response to the concern that:

the number of query methods might grow for larger applications because of - and that’s the second point - the queries define a fixed set of criterias. To avoid these two drawbacks, wouldn’t it be cool if you could come up with a set of atomic predicates that you could combine dynamically to build your query?

https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

I find QueryDSL a bit easier to work with. You need only define one interface method which you can then pass any combination of parameters to as a predicate.

e.g.

public interface UserRepository extends PagingAndSortingRepository<User, Long>, QueryDslPredicateExecutor<User> {
    public List<User> findAll(Predicate predicate);
}

and to query:

repository.findAll(QUser.user.address.town.eq("Glasgow").and(QUser.user.gender.eq(Gender.M)));

repository.findAll(QUser.user.address.town.eq("Edinburgh"));

repository.findAll(QUser.user.foreName.eq("Jim"));

where QUser is a QueryDSL auto-generated class.

http://docs.spring.io/spring-data/jpa/docs/current/api/index.html?org/springframework/data/jpa/repository/support/QueryDslRepositorySupport.html

http://www.querydsl.com/static/querydsl/2.1.0/reference/html/ch02s02.html

Update

From the Gosling release of the Spring Data module there is now support for automatic predicate generation from HTTP parameters in a web application.

https://spring.io/blog/2015/09/04/what-s-new-in-spring-data-release-gosling#querydsl-web-support

Answer 2

Declare the below function in your repository [EDITED]

Page<Flight> findByIdBetween(Long start, Long end, Pageable pageable)

Then you can invoke this function from the instance of repository. Please refer the spring-data reference for further info..