title: Spring Boot & JPA: Implementing search queries with optional, ranged criteria
body: <p><strong>This is an <a href="https://sscce.org" rel="noreferrer">SSCCE</a>, shows research, isn't a dupe and is on topic!!!</strong></p>
  
  <hr>
  
  <p>Spring Boot REST service and MySQL here. I have the following <code>Profile</code> entity:</p>
  
  <pre><code>@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 &amp; ctor down here
  }
  </code></pre>
  
  <p>I also have a <code>ProfileController</code> and I want to expose a GET endpoint that provides a really flexible/robust way to search for <code>Profiles</code> based on a large range of criteria:</p>
  
  <pre><code># Search for women between 1.2 and 1.8 meters tall.
  GET /v1/profiles?isMale=0&amp;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&amp;dob={"gt" : "1990-01-01 00:00:00"}&amp;weightKilos={"lt": 100.0}
  </code></pre>
  
  <p>etc.</p>
  
  <p>So here's my controller:</p>
  
  <pre><code>@RestController
  @RequestMapping("/v1/profiles")
  public class ProfileResource {
    @Autowired
    ProfileRepository profileRepository;
  
    @GetMapping
    public ResponseEntity&lt;Set&lt;Profile&gt;&gt; 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&lt;Profile&gt; profiles = profileRepository.searchProfiles(
          isMaleCriteria, heightCriteria, weightCriteria, dobCriteria);
      }
  }
  </code></pre>
  
  <p>My thinking for, say, <code>BigDecimalCriteria</code> would be something like:</p>
  
  <pre><code>// 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.
  }
  </code></pre>
  
  <p>Since all of these search criteria are optional (and thus can be <code>null</code>), I'm stuck on how to write the JPQL query in the <code>ProfileRepository</code>:</p>
  
  <pre><code>public interface ProfileRepository extends CrudRepository&lt;Profile,Long&gt; {
    @Query("???")
    public Set&lt;Profile&gt; searchProfiles();
  }
  </code></pre>
  
  <p><strong>How can I implement the <code>@Query(...)</code> for <code>ProfileRepository#searchProfiles</code> 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?</strong></p>
  
  <p>Of course, if there are any nifty little libraries or if Spring Boot/JPA already has a solution for this, I'm all ears!</p>