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

If you don't want to use QueryDSL, you'll have to write your own specifications. First of all, you need to extend your repository from JpaSpecificationExecutor like you did. Make sure to add the generic though (JpaSpecificationExecutor<Some>).

After that you'll have to create three specifications (one for each column), in the Spring docs they define these specifications as static methods in a class. Basically, creating a specification means that you'll have to subclass Specification<Some>, which has only one method to implement, toPredicate(Root<Some>, CriteriaQuery<?>, CriteriaBuilder).

If you're using Java 8, you can use lambdas to create an anonymous inner class, eg.:

 public class SomeSpecs {
     public static Specification<Some> withAddress(String address) {
          return (root, query, builder) -> {
               // ...
          };
     }
 }

For the actual implementation, you can use Root to get to a specific node, eg. root.get("address"). The CriteriaBuilder on the other hand is to define the where clause, eg. builder.equal(..., ...).

In your case you want something like this:

 public class SomeSpecs {
     public static Specification<Some> withAddress(String address) {
          return (root, query, builder) -> builder.equal(root.get("address"), address);
     }
 }

Or alternatively if you want to use a LIKE query, you could use:

public class SomeSpecs {
     public static Specification<Some> withAddress(String address) {
          return (root, query, builder) -> builder.like(root.get("address"), "%" + address + "%");
     }
 }

Now you have to repeat this for the other fields you want to filter on. After that you'll have to use all specifications together (using and(), or(), ...). Then you can use the repository.findAll(Specification) method to query based on that specification, for example:

public List<Some> getSome(String address, String name, Date date) {
    return repository.findAll(where(withAddress(address))
         .and(withName(name))
         .and(withDate(date));
}

You can use static imports to import withAddress(), withName() and withDate() to make it easier to read. The where() method can also be statically imported (comes from Specification.where()).

Be aware though that the method above may have to be tweaked since you don't want to filter on the address field if it's null. You could do this by returning null, for example:

public List<Some> getSome(String address, String name, Date date) {
    return repository.findAll(where(address == null ? null : withAddress(address))
         .and(name == null ? null : withName(name))
         .and(date == null ? null : withDate(date));
}

Answer 2

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.