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

Question

This is an SSCCE, shows research, isn't a dupe and is on topic!!!

---

Spring Boot REST service and MySQL here. I have the following Profile entity:

@Entity
@Table(name = "profiles")
public class Profile extends BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

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

    @Column(name = "profile_is_male")
    private Integer isMale;

    @Column(name = "profile_height_meters", columnDefinition = "DOUBLE")
    private BigDecimal heightMeters;

    @Column(name = "profile_weight_kilos", columnDefinition = "DOUBLE")
    private BigDecimal weightKilos;

    @Column(name = "profile_dob")
    private Date dob;

    // Getters, setters & ctor down here
}

I also have a ProfileController and I want to expose a GET endpoint that provides a really flexible/robust way to search for Profiles based on a large range of criteria:

# Search for women between 1.2 and 1.8 meters tall.
GET /v1/profiles?isMale=0&heightMeters={"gt": 1.2, "lt": 1.8}

# Search for men born after Jan 1, 1990 who weigh less than 100 kg.
GET /v1/profiles?isMale=1&dob={"gt" : "1990-01-01 00:00:00"}&weightKilos={"lt": 100.0}

etc.

So here's my controller:

@RestController
@RequestMapping("/v1/profiles")
public class ProfileResource {
  @Autowired
  ProfileRepository profileRepository;

  @GetMapping
  public ResponseEntity<Set<Profile>> searchProfiles(@RequestParam(value = "isMale", required = false) String isMaleVal,
                                              @RequestParam(value = "heightMeters", required = false) String heightMetersVal,
                                              @RequestParam(value = "weightKilos", required = false) String weightKilosVal,
                                              @RequestParam(value = "dob", required = false) String dobVal) {

      Integer isMaleVal;
      BooleanCriteria isMaleCriteria;
      if(isMaleVal != null) {
        // Parse the value which could either be "0" for female, "1" for male or something like
        // ?isMale={0,1} to indicate

        // BooleanCriteria would store which values male, female or both) to include in the search
      }

      BigDecimal heighMeters;
      BigDecimalCriteria heightCriteria;
      if(heightMetersVal != null) {
        // Parse the value which like in the examples could be something like:
        // ?heightMeters={"gt" : "1.0"}

        // BigDecimalCriteria stores range information
      }

      BigDecimal heighMeters;
      BigDecimalCriteria weightCriteria;
      if(weightKilosVal != null) {
        // Parse the value which like in the examples could be something like:
        // ?weightKilos={"eq" : "100.5"}

        // BigDecimalCriteria stores range information
      }

      // Ditto for DOB and DateCriteria

      // TODO: How to pack all of these "criteria" POJOs into a
      // CrudRepository/JPQL query against the "profiles" table?
      Set<Profile> profiles = profileRepository.searchProfiles(
        isMaleCriteria, heightCriteria, weightCriteria, dobCriteria);
    }
}

My thinking for, say, BigDecimalCriteria would be something like:

// Basically it just stores the (validated) search criteria that comes in over the wire
// on the controller method
public class BigDecimalCriteria {
  private BigDecimal lowerBound;
  private Boolean lowerBoundInclusive;
  private BigDecimal upperBound;
  private Boolean upperBoundInclusive;

  // Getters, setters, ctors, etc.
}

Since all of these search criteria are optional (and thus can be null), I'm stuck on how to write the JPQL query in the ProfileRepository:

public interface ProfileRepository extends CrudRepository<Profile,Long> {
  @Query("???")
  public Set<Profile> searchProfiles();
}

How can I implement the @Query(...) for ProfileRepository#searchProfiles in such a way that enables all of my search criteria (given all the permissible ranges and criteria values to search for), and allows any criteria to be null/optional?

Of course, if there are any nifty little libraries or if Spring Boot/JPA already has a solution for this, I'm all ears!

Solution 1

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

Solution 2

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.

Solution 3

The answer is quite eaiser and you can use the query-by-example in spring.

and even more you do not need to list all the Profile properties in your controller, you just take the Profile as the parameter, spring will take care of it.

And as you want to validate the request params, here is eaier to integrate with bean validator, take "givenName" as an example. add the NotNull in the entity, and add @Valid in the controller, in case the "givenName" is not in the request params, you will get the "Bad Request" response.

Here are the working codes:

@Entity
@Table(name = "profiles")
public class Profile {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "profile_given_name")
    @NotNull
    private String givenName;

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

    @Column(name = "profile_is_male")
    private Integer isMale;

    @Column(name = "profile_height_meters", columnDefinition = "DOUBLE")
    private BigDecimal heightMeters;

    @Column(name = "profile_weight_kilos", columnDefinition = "DOUBLE")
    private BigDecimal weightKilos;

    @Column(name = "profile_dob")
    private Date dob;
}

ProfileResource

@RestController
@RequestMapping("/v1/profiles")
public class ProfileResource {
    @Autowired
    ProfileRepository profileRepository;

    @GetMapping
    public ResponseEntity<List<Profile>> searchProfiles(@Valid Profile profile) {
        List<Profile> all = profileRepository.findAll(Example.of(profile));
        return ResponseEntity.ok(all);
    }
}

ProfileRepository

public interface ProfileRepository extends JpaRepository<Profile, Long> {
}

Then send the GET /v1/profiles?isMale=0 HTTP method as you wanted.

Solution 4

Check out “query by example” in spring data. Seems to fit the bill for what you need...

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example

View more solutions