Java Spring 数据 JPA 和可以为 null 的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30219486/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring data JPA and parameters that can be null
提问by Sebastian S.
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 的所有行.
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 种不同的查询方法。
采纳答案by Laurent B
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 CriteriaQueryto handle your case.
在您的情况下，我建议您编写存储库实现并使用自定义CriteriaQuery来处理您的情况。
Also you can use the @Queryannotation with the is nullsyntax :
@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 data jpa 2.0 开始，spring 现在支持 @Nullable 注释。这有助于处理传递的空参数。
From the documentation:
从文档：
@Nullable – to be used on a parameter or return value that can be null.
@Nullable – 用于可以为 null 的参数或返回值。
回答by mvmn
It seems Query by Examplemight 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/findAllthat take an instance of org.springframework.data.domain.Exampleas 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>接口，其通过扩展org.springframework.data.jpa.repository.JpaRepository<T, ID extends Serializable>接口。
In short, all JpaRepositoryinstances now have these methods.
简而言之，所有JpaRepository实例现在都有这些方法。
回答by GMsoF
Today as of Jun 2018, by looking at https://jira.spring.io/browse/DATAJPA-121, the query will automatically form is nullif your parameter is null.
今天截至 2018 年 6 月，通过查看https://jira.spring.io/browse/DATAJPA-121，is 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:
如果参数为空：
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:
如果参数不为空：
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] - [[email protected]]
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.
然后说到一个有趣的问题，如果查询中的参数为空，一些开发人员希望更好地控制忽略该参数，这仍在https://jira.spring.io/browse/DATAJPA-209中进行调查。
回答by fvukovic
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:
那么它可以为空，并且在查询中您将具有以下条件：
(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.
如果值为空，它会自动返回真，如果不是空，它会在表中搜索该值。
回答by Ashishkumar Chougule
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 为空的所有情况。
@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);
回答by ganaraj
While this has been answered and the accepted answer is relevant to the current question but there is another way to handle your null parameters in a JpaRespository. Posting this here as this can be leveraged when someone wants to query by ignoring fields when null and have dynamic query built. The below code sample should demonstrate the same
虽然这已经得到回答并且接受的答案与当前问题相关，但还有另一种方法可以处理 JpaRespository 中的空参数。在这里发布这个，因为当有人想要通过忽略 null 时的字段进行查询并构建动态查询时，可以利用它。下面的代码示例应该演示相同的
public class User{
private String firstName;
private String lastName;
}
import javax.persistence.criteria.Predicate;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User,Long>{
public Page<AppUser> findAll(Specification<AppUser> user,Pageable page);
public default Page<AppUser> findAll(User user,Pageable page){
return findAll(search(user),page);
}
static Specification<User> search(User entity) {
return (root, cq, cb) -> {
//To ensure we start with a predicate
Predicate predicate = cb.isTrue(cb.literal(true));
if(entity.getFirstName() != null && !entity.getFirstName().isBlank()) {
Predicate _predicate = cb.like(cb.lower(root.get("firstName")), "%"+entity.getFirstName().toLowerCase()+"%");
predicate = cb.and(predicate,_predicate);
}
if(entity.getLastName() != null && !entity.getLastName().isBlank()) {
Predicate _predicate = cb.like(cb.lower(root.get("lastName")), "%"+entity.getLastName().toLowerCase()+"%");
predicate = cb.and(predicate,_predicate);
}
return predicate;
}
}
}
