Spring数据JPA和可以为null的参数

My understanding is, that with Spring data JPA I cannot have a query method to fetch all rows where a column equals a given non-null method parameter and use the same method to fetch all rows where this column is NULL when the method parameter is null.

我的理解是,使用Spring数据JPA我不能有一个查询方法来获取列等于给定的非null方法参数的所有行,并且当method参数为null时,使用相同的方法获取此列为NULL的所有行。

Is that correct?

那是对的吗?

So I have to distinguish this in my JAVA code and I must use a separate query method explicitly asking for null values, like in the example below?

所以我必须在我的JAVA代码中区分它,我必须使用一个单独的查询方法显式询问空值,如下例所示?

// Query methods
List<Something> findByParameter(Parameter parameter);
List<Something> findByParameterIsNull();

...

List<Something> result = new ArrayList<>();

if (parameter == null)
  result = findByParameterIsNull();
else
  result = findByParameter(parameter);

That's bad, if I have 4 parameters which could be null and would have to code 16 different query methods.

这很糟糕,如果我有4个参数可以为null并且必须编码16种不同的查询方法。

5 个解决方案

#1

You are right.

你是对的。

A request has been made to support better handling of null parameters. https://jira.spring.io/browse/DATAJPA-121

已经发出请求以支持更好地处理空参数。 https://jira.spring.io/browse/DATAJPA-121

In your case, i would advise you to write your repository implementation and to use a custom CriteriaQuery to handle your case.

在您的情况下,我建议您编写存储库实现并使用自定义CriteriaQuery来处理您的案例。

Also you can use the @Query annotation with the is null syntax :

您还可以使用带有is null语法的@Query注释:

@Query("[...] where :parameter is null"
public List<Something> getSomethingWithNullParameter();

EDIT

Since Spring data jpa 2.0, spring now supports @Nullable annotation. This can be helpful to handle null parameters passed.

从Spring数据jpa 2.0开始,spring现在支持@Nullable注释。这有助于处理传递的null参数。

From the documentation :

从文档:

@Nullable – to be used on a parameter or return value that can be null.

@Nullable - 用于参数或返回值,可以为null。

#2

It seems Query by Example might be what you need.

似乎Query by Example可能就是您所需要的。

Query by Example is a new feature in Spring Data (since version Hopper, out April 2016), which allows one to create simple dynamic queries with a code like this

Query by Example是Spring Data中的一个新功能(自版本Hopper出版,2016年4月),它允许用这样的代码创建简单的动态查询

Person person = new Person();                          
person.setFirstname("Dave");                           

ExampleMatcher matcher = ExampleMatcher.matching()     
  .withIncludeNullValues();                        

Example<Person> example = Example.of(person, matcher);

personRepository.count(example);
personRepository.findOne(example);
personRepository.findAll(example);

Methods count/findOne/findAll that take an instance of org.springframework.data.domain.Example as a parameter (and some of them also take sorting/pagination parameters) are coming from org.springframework.data.repository.query.QueryByExampleExecutor<T> interface, which is extended by org.springframework.data.jpa.repository.JpaRepository<T, ID extends Serializable> interface.

方法count / findOne / findAll将org.springframework.data.domain.Example的实例作为参数(其中一些也采用排序/分页参数)来自org.springframework.data.repository.query.QueryByExampleExecutor < T> interface,由org.springframework.data.jpa.repository.JpaRepository interface。扩展,id扩展serializable>

In short, all JpaRepository instances now have these methods.

简而言之,所有JpaRepository实例现在都具有这些方法。

#3

i found something...if u put the parameter in the jpa method like this

我找到了一些东西......如果你把参数放在像这样的jpa方法中

@Param("value") String value,

then it can be null and in the query you will have this condition:

然后它可以为null,在查询中您将具有以下条件:

(table.value = :value OR :value IS NULL)

if the value is null it will automatically return true and if is not null, it will search that value in the table.

如果值为null,它将自动返回true,如果不为null,它将在表中搜索该值。

#4

Today as of Jun 2018, by looking at https://jira.spring.io/browse/DATAJPA-121, the query will automatically form is null if your parameter is null.

今天截至2018年6月,通过查看https://jira.spring.io/browse/DATAJPA-121,如果您的参数为null,则查询将自动形成为null。

I did that in my project, it is true:

我在我的项目中做到了,这是真的:

compile group: 'org.springframework.data', name: 'spring-data-jpa', version: '2.0.7.RELEASE'

public interface AccountDao extends CrudRepository<T, ID> {

    //this can accept null and it will become isNull
    public List<MyAccount> findByEmail(String email);

}

if parameter is null:

如果参数为null:

select
        myaccount0_.id as id1_0_,
        myaccount0_.email as email2_0_,
        myaccount0_.password as password3_0_,
        myaccount0_.user_id as user_id4_0_ 
    from
        my_account myaccount0_ 
    where
        myaccount0_.email is null

if parameter is not null:

如果参数不为null:

select
        myaccount0_.id as id1_0_,
        myaccount0_.email as email2_0_,
        myaccount0_.password as password3_0_,
        myaccount0_.user_id as user_id4_0_ 
    from
        my_account myaccount0_ 
    where
        myaccount0_.email=?
11:02:41.623 [qtp1507181879-72] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [testing@hotmail.com]

Then it comes to an interesting question, some developers want better control to ignore the parameter in query if it is null, this is still being under investigating in https://jira.spring.io/browse/DATAJPA-209.

然后它涉及一个有趣的问题,一些开发人员希望更好的控制来忽略查询中的参数,如果它是null,这仍然在调查https://jira.spring.io/browse/DATAJPA-209。

#5

In my case membershipNumber is nullable, and I have handled it this way. This will handle all the cases where table.membershipNumber is null too.

在我的情况下,membershipNumber是可以为空的,我已经用这种方式处理了它。这将处理table.membershipNumber也为null的所有情况。

      @Query(value = "SELECT pr FROM ABCTable pr " +
            "WHERE LOWER(pr.xyz) = LOWER(:xyz) " +
            "and LOWER(pr.subscriptionReference) = LOWER(:subscriptionReference) " +
            "and pr.billId = :billId " +
            "and ((pr.membershipNumber = :membershipId) or (pr.membershipNumber = null and :membershipId = null))")
    List<PaymentRequest> getSomething (@Param("xyz") String xyz,
                                                 @Param("subscriptionReference") String subscriptionReference,
                                                 @Param("billId") Integer billId,
                                                 @Param("membershipId") String membershipNumber);

#1