Uploaded image for project: 'Spring Data JPA'
  1. Spring Data JPA
  2. DATAJPA-209

Improve handling of null query method parameter values

    Details

    • Type: Improvement
    • Status: Investigating
    • Priority: 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
          ismaximum Mohammad added a comment -

          I think this is a must... almost more than 50 pc of our queries in our application has this clause as '(x = :x or :x is null) and ....' For this reason currently we have to handcraft all these queries

          Show
          ismaximum Mohammad added a comment - I think this is a must... almost more than 50 pc of our queries in our application has this clause as '(x = :x or :x is null) and ....' For this reason currently we have to handcraft all these queries
          Hide
          sniff Mikhail Mikhaylenko added a comment -

          Maybe there is a possibility to add Optional keyword to query derivation mechanism?
          If this keyword is present before parameter name, then null value will force skip of adding conditions with this parameter to query.

          public interface FooRepository extends JpaRepository<Foo> {
            List<Foo> findByOptionalBarAndGoo(Bar bar, Goo goo);
            // or even like this:
            List<Foo> findByOptionalBarAndGoo(Optional<Bar> optBar, Goo goo); // which maybe a bad practice
          }
          

          Show
          sniff Mikhail Mikhaylenko added a comment - Maybe there is a possibility to add Optional keyword to query derivation mechanism? If this keyword is present before parameter name, then null value will force skip of adding conditions with this parameter to query. public interface FooRepository extends JpaRepository<Foo> { List<Foo> findByOptionalBarAndGoo(Bar bar, Goo goo); // or even like this: List<Foo> findByOptionalBarAndGoo(Optional<Bar> optBar, Goo goo); // which maybe a bad practice }
          Hide
          olivergierke Oliver Gierke added a comment -

          Optional is already supported but in the way it's intended to be used: instances of Optional must never be null themselves, Optional.empty() is basically treated like null downstream.

          Show
          olivergierke Oliver Gierke added a comment - Optional is already supported but in the way it's intended to be used: instances of Optional must never be null themselves, Optional.empty() is basically treated like null downstream.
          Hide
          miroki John Day added a comment -

          This feature would be of great help of a particular situation: processing query conditions. Say, there are a number of filter conditions in a personnel query form, like name, department, office location and etc, you got the picture. Users are not required to fill each and every input box, in fact, when the input box is left empty, it has a specific syntax meaning: not applying that filter in the query. Any field and any combination of fields can be empty, so it'd be an exhausting job to iterate every situation. Under a limited circumstances, Query By Example may be of help. However, when facing more variable query conditions like BETWEEN, LIKE and etc, Spring Data JPA is really weak and unproductive. At the moment, the only promising solution is Specification. I'll try to come up with a working example.

          Show
          miroki John Day added a comment - This feature would be of great help of a particular situation: processing query conditions. Say, there are a number of filter conditions in a personnel query form, like name, department, office location and etc, you got the picture. Users are not required to fill each and every input box, in fact, when the input box is left empty, it has a specific syntax meaning: not applying that filter in the query. Any field and any combination of fields can be empty, so it'd be an exhausting job to iterate every situation. Under a limited circumstances, Query By Example may be of help. However, when facing more variable query conditions like BETWEEN, LIKE and etc, Spring Data JPA is really weak and unproductive. At the moment, the only promising solution is Specification. I'll try to come up with a working example.
          Hide
          olivergierke Oliver Gierke added a comment -

          Have you looked at the Querydsl support? Query methods are not means to execute flexible predicates but well-known-in-advance queries. They trade off the flexibility to combine predicates (non-existant) with the ease you can create those. If you want to freely combine predicates, query methods is simply not the feature you want to use but — as you already realized — specifications or — less JPA bound — Querydsl.

          Show
          olivergierke Oliver Gierke added a comment - Have you looked at the Querydsl support? Query methods are not means to execute flexible predicates but well-known-in-advance queries. They trade off the flexibility to combine predicates (non-existant) with the ease you can create those. If you want to freely combine predicates, query methods is simply not the feature you want to use but — as you already realized — specifications or — less JPA bound — Querydsl.

            People

            • Assignee:
              olivergierke Oliver Gierke
              Reporter:
              matthewadams Matthew T. Adams
              Last updater:
              Oliver Gierke
            • Votes:
              44 Vote for this issue
              Watchers:
              43 Start watching this issue

              Dates

              • Created:
                Updated: