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
          Matthew T. Adams added a comment -

          JIRA jacked up code formatting. BlazRepository should be:

          public interface BlazRepository extends JpaRepository<Blaz>

          { @NullMeans(IGNORED) List<Blaz> findByFooAndGooAndHooAndKooAndLoo(Foo foo, Goo goo, Hoo hoo, Koo koo, @NullMeans(IS) Loo loo); }
          Show
          Matthew T. Adams added a comment - JIRA jacked up code formatting. BlazRepository should be: public interface BlazRepository extends JpaRepository<Blaz> { @NullMeans(IGNORED) List<Blaz> findByFooAndGooAndHooAndKooAndLoo(Foo foo, Goo goo, Hoo hoo, Koo koo, @NullMeans(IS) Loo loo); }
          Hide
          Oliver Gierke added a comment -

          Are you sure you can reproduce this (SD JPA using = NULL) against 1.1.0.RC1? We had DATAJPA-121 reported and resolved for 1.1.0.RC1 so that SD JPA should now correctly use IS NULL for the query if null is provided as parameter. So I don't think we create invalid queries for null parameters anymore.

          Beyond that I'd like to see whether there's community feedback and votes on this one as this would introduce quite a bit of complexity to the configuration and implementation actually.

          Show
          Oliver Gierke added a comment - Are you sure you can reproduce this (SD JPA using = NULL ) against 1.1.0.RC1? We had DATAJPA-121 reported and resolved for 1.1.0.RC1 so that SD JPA should now correctly use IS NULL for the query if null is provided as parameter. So I don't think we create invalid queries for null parameters anymore. Beyond that I'd like to see whether there's community feedback and votes on this one as this would introduce quite a bit of complexity to the configuration and implementation actually.
          Hide
          Matthew T. Adams added a comment - - edited

          FYI, I'm unable to repro the "= NULL" v. "IS NULL" bug so far using the showcase in the SD JPA example after updating its POMs to look similar to mine. I'm digging further into my POMs to see what might be going on.

          Show
          Matthew T. Adams added a comment - - edited FYI, I'm unable to repro the "= NULL" v. "IS NULL" bug so far using the showcase in the SD JPA example after updating its POMs to look similar to mine. I'm digging further into my POMs to see what might be going on.
          Hide
          Matthew T. Adams added a comment -

          Assuming that the "IS NULL" parameter handling is fixed, then that would warrant the removal of the EQUALS value in the proposed NullBehavior enum, leaving only IS and IGNORED.

          Show
          Matthew T. Adams added a comment - Assuming that the "IS NULL" parameter handling is fixed, then that would warrant the removal of the EQUALS value in the proposed NullBehavior enum, leaving only IS and IGNORED.
          Hide
          Matthew T. Adams added a comment -

          Ok, just confirmed that I'm indeed seeing the behavior that was fixed as a result of DATAJPA-121. I must've first seen this behavior with a version of SD JPA prior to 1.1.0.RC1 (probably 1.1.0.M1 or M2).

          Show
          Matthew T. Adams added a comment - Ok, just confirmed that I'm indeed seeing the behavior that was fixed as a result of DATAJPA-121 . I must've first seen this behavior with a version of SD JPA prior to 1.1.0.RC1 (probably 1.1.0.M1 or M2).
          Hide
          Oliver Gierke added a comment -

          Thanks for confirmation, Adam. Just wanted to make sure we don't have a glitch preventing the fix for DATAJPA-121 working. You proposal is definitely adding value on top of the fix, so I'll keep this one around.

          Show
          Oliver Gierke added a comment - Thanks for confirmation, Adam. Just wanted to make sure we don't have a glitch preventing the fix for DATAJPA-121 working. You proposal is definitely adding value on top of the fix, so I'll keep this one around.
          Hide
          tejo a kusuma added a comment -

          this is definitely a very important feature to sd-jpa, because I think that this will simplify complex work regarding dynamic query. so please consider to add this feature to spring data jpa

          Show
          tejo a kusuma added a comment - this is definitely a very important feature to sd-jpa, because I think that this will simplify complex work regarding dynamic query. so please consider to add this feature to spring data jpa
          Hide
          s.selvakumar added a comment - - edited

          Hi All,
          I don't to whether right way to ask here.
          In query I have passed my object itself in Criteria, "where key" as a "is" condition. Its dig the object and get the value but when object field value is "null" field, its making query as "null" values instead of skip query parameter, which leads to wrong query. Is it possible to over come this problem, or can expect as a improvement.

          Show
          s.selvakumar added a comment - - edited Hi All, I don't to whether right way to ask here. In query I have passed my object itself in Criteria, "where key" as a "is" condition. Its dig the object and get the value but when object field value is "null" field, its making query as "null" values instead of skip query parameter, which leads to wrong query. Is it possible to over come this problem, or can expect as a improvement.
          Hide
          Uwe Allner added a comment -

          It is important to note, that you must not annotate your finder with a @Query. Because in that case null values are still inserted as "field = null" into the generated SQL. By just using the name of the finder to generate the query it all works fine...

          Show
          Uwe Allner added a comment - It is important to note, that you must not annotate your finder with a @Query. Because in that case null values are still inserted as "field = null" into the generated SQL. By just using the name of the finder to generate the query it all works fine...

            People

            • Assignee:
              Oliver Gierke
              Reporter:
              Matthew T. Adams
            • Votes:
              22 Vote for this issue
              Watchers:
              16 Start watching this issue

              Dates

              • Created:
                Updated: