Spring Data JPA
  1. Spring Data JPA
  2. DATAJPA-209

Improve handling of null query method parameter values

    Details

    • Type: Improvement Improvement
    • Status: Investigating
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.1 RC1
    • Fix Version/s: None
    • Component/s: None

      Description

      In 1.1.0.RC1 and prior, query methods expect non-null values. If a null value is passed in to a query method, the JPQL generated includes an "= NULL" condition, which is always false.

      SD JPA supports query method keyword IsNull, which allows for testing explicitly whether a value is null. This is ok, but fails to meet our requirement of using null parameter values to indicate that that parameter should be ignored and not included in the query.

      Here's an example. Suppose I have a Foo that references a Bar and a Goo, and I want to create a query that finds me any Foo instances that reference a given Bar and/or Goo. The query method would look like this:

      public interface FooRepository extends JpaRepository<Foo> {
       
        List<Foo> findByBarAndGoo(Bar bar, Goo goo);
      }

      If this method is called with a non-null values for both parameters, everything works fine. However, if you pass null for either parameter, no Foo instances are found because = NULL is always false. One alternative is for the author to write custom, boilerplate method implementations that handle null instances as desired. Another alternative is to write a collection of methods representing all of the permutations of the nullable parameters, which doesn't really scale well past two or three parameters:

      public interface FooRepository extends JpaRepository<Foo> {
       
        List<Foo> findByBarAndGoo(Bar bar, Goo goo);
        List<Foo> findByBar(Bar bar);
        List<Foo> findByGoo(Goo goo);
      }

      This issue represents a request to improve this situation.

      Consider a new enum & annotation:

      public enum NullBehavior {
      	EQUALS, IS, IGNORED
      }
       
      @Retention(RetentionPolicy.RUNTIME)
      @Target({ElementType.TYPE, ElementType.METHOD, ElementType.PARAMETER})
      public @interface NullMeans {
      	NullBehavior value() default NullBehavior.EQUALS;
      }

      With this annotation, SD JPA would let the author decide how to behave when null parameters are encountered. In the absence of the annotation, the current default behavior (= NULL) would apply. If the author uses @NullMeans(IS), then SD JPA will produce an IS NULL clause. If they use @NullMeans(IGNORED), then SD JPA does not include a clause for the given parameter.

      Now, reconsider the Foo example. I now have a flexible way of specifying the queries I want.

      public interface FooRepository extends JpaRepository<Foo> {
       
        List<Foo> findByBarAndGoo(@NullMeans(IGNORED) Bar bar, @NullMeans(IGNORED) Goo goo);
      }

      This also scales well:

      public interface BlazRepository extends JpaRepository<Blaz> {
       
        @NullMeans(IGNORED) // applies to all parameters unless overriden by @NullMeans on parameter(s)
        List<Blaz> findByFooAndGooAndHooAndKooAndLoo(Foo foo, Goo goo, Hoo hoo, Koo koo, @NullMeans(IS) Loo loo);
      }

      I've also allowed @NullMeans to be placed on the interface as well, which would provide a default for all parameters on all query methods defined in the interface. I would imagine that many folks would use @NullMeans(IGNORED) at the interface level since it's so practical.

        Issue Links

          Activity

          Hide
          Michael Lubavin added a comment -

          Hi, any update on this feature? I am just starting with Spring Data JPA, and it seems strange that I have to implement JPQL just to be able to have optional parameters (and I still haven't wrapped my head around optional IN clauses).

          Show
          Michael Lubavin added a comment - Hi, any update on this feature? I am just starting with Spring Data JPA, and it seems strange that I have to implement JPQL just to be able to have optional parameters (and I still haven't wrapped my head around optional IN clauses).
          Hide
          Wojciech Krak added a comment -

          I also find this feature useful. It would be nice if it would be working in every spring-data subproject(now I'm using spring-data-mongo)

          Show
          Wojciech Krak added a comment - I also find this feature useful. It would be nice if it would be working in every spring-data subproject(now I'm using spring-data-mongo)
          Hide
          James King added a comment -

          We can simply generate the query based on the query method name and the passed in parameters and ignore the case if the query is generated based on @Query.

          Show
          James King added a comment - We can simply generate the query based on the query method name and the passed in parameters and ignore the case if the query is generated based on @Query.
          Hide
          Jason Fung added a comment -

          Agree with Wojeciech, can you make this available for all spring-data subproject.

          Show
          Jason Fung added a comment - Agree with Wojeciech, can you make this available for all spring-data subproject.
          Hide
          Nick Vanderhoven added a comment - - edited

          I agree and was strongly suprised this is not the default behaviour. Every mainstream project has a search form with multiple fields that can be left empty. First we get a lot of advantages and concise code and then we have to throw them away and go write everything ourselves. This would be a big enabler for adopters.

          Show
          Nick Vanderhoven added a comment - - edited I agree and was strongly suprised this is not the default behaviour. Every mainstream project has a search form with multiple fields that can be left empty. First we get a lot of advantages and concise code and then we have to throw them away and go write everything ourselves. This would be a big enabler for adopters.

            People

            • Assignee:
              Oliver Gierke
              Reporter:
              Matthew T. Adams
              Last updater:
              Nick Vanderhoven
            • Votes:
              31 Vote for this issue
              Watchers:
              26 Start watching this issue

              Dates

              • Created:
                Updated: