spring-data-jpa: Improve handling of null query method parameter values [DATAJPA-209]
Matthew T. Adams opened DATAJPA-209 and commented
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
Affects: 1.1 RC1
Issue Links:
-
DATACMNS-1319 Suggestion: change interpretation of Optionals as parameters in Spring Data JPA repositories interfaces (“is duplicated by”)
-
DATAJPA-121 Query parameter is null,it still use equals(=) to compare
-
DATACMNS-490 Add support for optional query method parameters
77 votes, 72 watchers
About this issue
- Original URL
- State: closed
- Created 12 years ago
- Reactions: 25
- Comments: 39 (2 by maintainers)
Is the suggested @NullMeans annotation still under consideration. I believe it will be super helpful for multiple condition derived query statements.
Is this plan still being updated? I also encountered this problem. Multiple parameters may be NULL but what I want is to ignore this parameter instead of generating IS NULL.
Due to the complexity of dealing with
nullbeing a signal to exclude a field from a query and having Query by Example as a feasible means to do the same, we have updated our decision, choosing not to implement this feature.Would Query by Example also work for lists ( SQL IN )?
e.g.: All parameters are optional ( can be null) + logical AND
I couldn’t find how I can do a SQL IN match with Query by Example which easily works with method names. eg: findByTypeIn
Full query with method names would not work will null parameters:
There is also many thing that QBE by design can’t support :
If you need to optionally exclude certain fields from a query based upon that entry being
null(like a filter box on a web page), why not use Query by Example?https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example
Query by Example, by default, will ignore any fields that are
nulland EXCLUDE them from the query. This avoids having to deal withOptionalfields, distinguishing betweenIS NULLscenarios. To top it off, we even have special handlers (#541) that if they seenullentered into a field, switch toIS NULLin the generated JPQL.Jens Schauder commented
As described in the last comment we decided that we want this to get implemented. But we have no plan on the timing
Jens Schauder commented
I came across this https://stackoverflow.com/questions/44376354/how-to-use-oracle-nvl-function-in-spring-data-repository-nativequery Stackoverflow question and others having similiar problems sometimes with exactly the kind of query I proposed above. Makes me wonder if this actually would work
@gregturn, it seems that Query by Example falls short in resolving the issue because it supports only EQUALS conditions.
Users may also use other conditions like GREATER, LESS, CONTAINS, STARTING_WITH, ENDS_WITH in their query methods.
Consider the following code snippet as an example:
With both
afterandbeforeset, I would like the method to return all Transactions within that time frame. Withbeforeset tonull, I would like the method to return all Transactions afteraftertimestamp.when this feature “@NullMeans” will be implemented or it exists yet