Multi-Column Search with Spring JPA Specifications

Question

I want to create a multi field search in a Spring-Boot back-end. How to do this with a Specification<T> ?

Environment

Springboot
Hibernate
Gradle
Intellij

The UI in the front end is a Jquery Datatable. Each column allows a single string search term to be applied. The search terms across more than one column is joined by a and.

I have the filters coming from the front end already getting populated into a Java object.

Step 1 Extend JPA Specification executor

public interface SomeRepository extends JpaRepository<Some, Long>, PagingAndSortingRepository<Some, Long>, JpaSpecificationExecutor {

Step2 Create a new class SomeSpec

This is where I am lost as to what the code looks like it and how it works.

Do I need a method for each column? What is Root and what is Criteria Builder? What else is required?

I am rather new at JPA so while I don't need anyone to write the code for me a detailed explanation would be good.

UPDATE It appears QueryDSL is the easier and better way to approach this. I am using Gradle. Do I need to change my build.gradle from this ?

Answer 1

You could consider using Spring Data's support for QueryDSL as you would get quite a lot without having to write very much code i.e. you would not actually have to write the specifictions.

See here for an overview:

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

Although this approach is really convenient (you don’t even have to write a single line of implementation code to get the queries executed) it has two drawbacks: first, 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?

So essentially your repository becomes:

public interface SomeRepository extends JpaRepository<Some, Long>,
     PagingAndSortingRepository<Some, Long>, QueryDslPredicateExecutor<Some>{

}

You can also get request parameters automatically bound to a predicate in your Controller:

See here:

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

SO your Controller would look like:

  @Controller
  class SomeController {

    private final SomeRepository repository;

    @RequestMapping(value = "/", method = RequestMethod.GET)
    String index(Model model,
                 @QuerydslPredicate(root = Some.class) Predicate predicate,
                 Pageable pageable) {

      model.addAttribute("data", repository.findAll(predicate, pageable));
      return "index";
    }
  }

So with the above in place it is simply a Case of enabling QueryDSL on your project and the UI should now be able to filter, sort and page data by various combinations of criteria.