Spring Boot & JPA: Implementing search queries with optional, ranged criteria

You can achieve complex queries with specifications by JpaSpecificationExecutor in spring data. Repository interface must extend the JpaSpecificationExecutor<T> interface so we can specify the conditions of our database queries by creating new Specification<T> objects.

The trick is in the use of the Specification interface in combination with a JpaSpecificationExecutor. here is the example:

@Entity
@Table(name = "person")
public class Person {

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private Long id;

 @Column(name = "name")
 private String name;

 @Column(name = "surname")
 private String surname;

 @Column(name = "city")
 private String city;

 @Column(name = "age")
 private Integer age;

        ....

}

Then we define our repository:

public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> {

}

As you can see we have extended another interface the JpaSpecificationExecutor. This interface defines the methods to perform the search via a Specification class.

What we have to do now is to define our specification that will return the Predicate containing the constraints for the query (in the example the PersonSpecification is performing the query select * from person where name = ? or (surname = ? and age = ?) ):

public class PersonSpecification implements Specification<Person> {

    private Person filter;

    public PersonSpecification(Person filter) {
        super();
        this.filter = filter;
    }

    public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> cq,
            CriteriaBuilder cb) {

        Predicate p = cb.disjunction();

        if (filter.getName() != null) {
            p.getExpressions()
                    .add(cb.equal(root.get("name"), filter.getName()));
        }

        if (filter.getSurname() != null && filter.getAge() != null) {
            p.getExpressions().add(
                    cb.and(cb.equal(root.get("surname"), filter.getSurname()),
                            cb.equal(root.get("age"), filter.getAge())));
        }

        return p;
    }
}

Now it is time to use it. The following code fragment shows how to use the Specification we just created:

...

Person filter = new Person();
filter.setName("Mario");
filter.setSurname("Verdi");
filter.setAge(25);

Specification<Person> spec = new PersonSpecification(filter);

List<Person> result = repository.findAll(spec);

Here is full example present in github

Also you can create any complex queries using Specification

Almost what you need is already implemented in Spring Data with help of Querydsl and Web support Spring Data extensions.

You should extend your repo as well from QuerydslPredicateExecutor and, if you are using Spring Data REST, you can query your repo data right 'from the box' with base filtering, paging and sorting support:

/profiles?isMale=0&heightMeters=1.7&sort=dob,desc&size=10&page=2

To implement more complex filter you should extend your repo from the QuerydslBinderCustomizer and use its customize method (right in your repo).

For example you can implement 'between' filter for heightMeters and 'like' filter for surname:

public interface ProfileRepository extends JpaRepository<Profile, Long>, QuerydslPredicateExecutor<Profile>, QuerydslBinderCustomizer<QProfile> {

    @Override
    default void customize(QuerydslBindings bindings, QProfile profile) {

      bindings.excluding( // used to exclude unnecessary fields from the filter
          profile.id,
          profile.version,
          // ...
      );

      bindings.bind(profile.heightMeters).all((path, value) -> {

          Iterator<? extends BigDecimal> it = value.iterator();
          BigDecimal from = it.next();
          if (value.size() >= 2) {
              BigDecimal to = it.next();
              return path.between(from, to)); // between - if you specify heightMeters two times
          } else {
              return path.goe(from); // or greter than - if you specify heightMeters one time
          }
      });

      bindings.bind(profile.surname).first(StringExpression::containsIgnoreCase);        
    }
}

Then you can query your profiles:

/profiles?isMale=0&heightMeters=1.4&heightMeters=1.6&surename=doe

i.e. - find all females which height is between 1.4 and 1.6 meters and surename contains 'doe'.

If you are not using Spring Data REST you can implement your own rest controller method with QueryDSL support:

@RestController
@RequestMapping("/profiles")
public class ProfileController {

    @Autowired private ProfileRepository profileRepo;

    @GetMapping
    public ResponseEntity<?> getAll(@QuerydslPredicate(root = Profile.class, bindings = ProfileRepository.class) Predicate predicate, Pageable pageable) {

        Page<Profile> profiles = profileRepo.findAll(predicate, pageable);
        return ResponseEntity.ok(profiles);
    }
}

Note: don't forget to add QueryDSL dependency to you project:

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
</dependency>

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <scope>provided</scope>
</dependency>

<build>
    <plugins>
        <plugin>
            <groupId>com.mysema.maven</groupId>
            <artifactId>apt-maven-plugin</artifactId>
            <version>1.1.3</version>
            <executions>
                <execution>
                    <goals>
                        <goal>process</goal>
                    </goals>
                    <configuration>
                        <outputDirectory>target/generated-sources/annotations</outputDirectory>
                        <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>                                                       
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

Then compile your project (for example mvn compile) to let it make 'Q' classes.