title: SQL Request if criteria is not null
body: <p>I am trying to write a query that performs a search according to several criteria.</p>
  
  <p>My current JPA request:</p>
  
  <pre><code>@Query("SELECT c FROM Client c " +
      "INNER JOIN c.entite e " +
      "WHERE e.numeroLicence in (:numerosLicence) " +
      "AND (UPPER(c.nom) LIKE CONCAT('%',UPPER(:nom),'%') " +
      "AND (c.prenom IS NOT NULL AND UPPER(c.prenom) LIKE CONCAT('%',UPPER(:prenom),'%')) " +
      "AND (c.dateCreation IS NOT NULL AND c.dateCreation &gt; :dateCreation) " +
      "AND (c.dateCreation IS NOT NULL AND c.dateModification &gt; :dateModification) " +
      "AND (c.dateCreation IS NOT NULL AND c.dateSuppression &gt; :dateSuppression)) ")
  Page&lt;Client&gt; recherche(@Param("numerosLicence") ArrayList numerosLicence,
                         @Param("nom") String nom,
                         @Param("prenom") String prenom,
                         @Param("dateCreation") ZonedDateTime dateCreation,
                         @Param("dateModification") ZonedDateTime dateModification,
                         @Param("dateSuppression") ZonedDateTime dateSuppression,
                         Pageable pageable);
  </code></pre>
  
  <p>The problem is that it automatically excludes rows where a column contains NULL.</p>
  
  <p>I'll need the selection to be done on columns with data only, it will not try to execute the clause if the field value is NULL</p>
  
  <p>For example, if I have a row whose <code>firstname</code> column is NULL, then this line is automatically excluded from the result whereas I would like it not to test this column if it is NULL</p>
  
  <p>Do you have an idea?
  I'm looking for a solution to avoid creating as many queries as possible combination</p>