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