Spring Data JPA and Null Parameters – Spring Data JPA和空参数

最后修改: 2019年 4月 18日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

In this tutorial, we’ll show how to handle null parameters in Spring Data JPA.

在本教程中，我们将展示如何在Spring Data JPA中处理null参数。

In some cases, when we search for records by parameters, we want to find rows with null as the field value. Other times, we want to ignore a null and skip that field in our query.

在某些情况下，当我们通过参数搜索记录时，我们希望找到字段值为null的记录。其他时候，我们希望忽略null，在查询中跳过该字段。

Below we’ll show how to implement each of these.

下面我们将展示如何实现每一项内容。

2. Quick Example

2.快速实例

Let’s say we have a Customer entity:

假设我们有一个Customer实体。

@Entity
public class Customer {

    @Id
    @GeneratedValue
    private long id;
    private String name;
    private String email;

    public Customer(String name, String email) {
        this.name = name;
        this.email = email;
    }

    // getters/setters

}

Also, we have a JPA repository:

另外，我们有一个JPA库。

public interface CustomerRepository extends JpaRepository<Customer, Long> { 

   // method1
   // method2
}

We want to search for customers by name and email.

我们想通过姓名和电子邮件来搜索客户。

For this purpose, we’ll write two methods that handle null parameters differently.

为此，我们将编写两个方法，以不同方式处理null参数。

3. Ways to Handle Null Parameters

3.处理Null参数的方法

First, we’ll create a method that interprets null values of the parameters as IS NULL. Then we’ll create a method that ignores null parameters and excludes them from the WHERE clause.

首先，我们将创建一个方法，将参数的null值解释为IS NULL。然后，我们将创建一个方法，忽略null参数，并将它们从WHERE子句中排除。

3.1. IS NULL Query

3.1. IS NULL 查询

The first method is very simple to create because null parameters in the query methods are interpreted as IS NULL by default.

第一个方法的创建非常简单，因为查询方法中的null参数默认被解释为IS NULL。

Let’s create the method:

我们来创建这个方法。

List<Customer> findByNameAndEmail(String name, String email);

Now if we pass a null email, the generated JPQL will include the IS NULL condition:

现在，如果我们传递一个null电子邮件，生成的JPQL将包括IS NULL条件。

customer0_.email is null

To demonstrate this, let’s create a test.

为了证明这一点，让我们创建一个测试。

First, we’ll add some customers to the repository:

首先，我们要在资源库中添加一些客户。

@Before
public void before() {
    entityManager.persist(new Customer("A", "A@example.com"));
    entityManager.persist(new Customer("D", null));
    entityManager.persist(new Customer("D", "D@example.com"));
}

Now let’s pass “D” as the value of the name parameter and null as the value of the email parameter to our query method.

现在让我们把“D”作为name参数的值，把null作为email参数的值传递给我们的查询方法。

We can see that exactly one customer will be found:

我们可以看到，正好可以找到一个客户。

List<Customer> customers = repository.findByNameAndEmail("D", null);

assertEquals(1, customers.size());

Customer actual = customers.get(0);

assertEquals(null, actual.getEmail());
assertEquals("D", actual.getName());

3.2. Avoid null Parameter With Alternative Methods

3.2.用其他方法避免null参数

Sometimes we want to ignore some parameters and not include their corresponding fields in the WHERE clause.

有时我们想忽略一些参数，不在WHERE子句中包括它们相应的字段。

We can add more query methods to our repository.

我们可以为我们的资源库添加更多的查询方法。

For example, to ignore email, we can add a method that only accepts name:

例如，为了忽略email，我们可以添加一个只接受name的方法。

 List<Customer> findByName(String name);

But this way of ignoring one of our columns scales poorly as the number increases since we would have to add many methods to achieve all the combinations.

但是，这种忽略其中一列的方式随着数量的增加，其扩展性很差，因为我们必须增加许多方法来实现所有的组合。

3.3. Ignoring null Parameters Using the @Query Annotation

3.3.使用@Query注释忽略null参数

We can avoid creating additional methods by using the @Query annotation and adding a small complication to the JPQL statement:

我们可以通过使用 @Query注解来避免创建额外的方法，并在JPQL语句中增加一个小的复杂度。

@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name) and (:email is null"
  + " or c.email = :email)")
List<Customer> findCustomerByNameAndEmail(@Param("name") String name, @Param("email") String email);

Notice that if the email parameter is null, the clause is always true and so doesn’t influence the whole WHERE clause:

注意，如果email参数是null，该子句总是true，所以不会影响整个WHERE子句。

:email is null or s.email = :email

Let’s make sure that this works:

让我们确保这一点是可行的。

List<Customer> customers = repository.findCustomerByNameAndEmail("D", null);

assertEquals(2, customers.size());

We found two customers whose name is “D” ignoring their emails.

我们发现有两个名字为“D”的客户忽略了他们的邮件。

The generated JPQL WHERE clause looks like this:

生成的JPQL WHERE子句看起来像这样。

where (? is null or customer0_.name=?) and (? is null or customer0_.email=?)

With this method, we are putting trust in the database server to recognize the clause regarding our query parameter being null and optimize the execution plan of the query so that it doesn’t have a significant performance overhead. For some queries or database servers, especially involving a huge table scan, there could be a performance overhead.

通过这种方法，我们相信数据库服务器能够识别关于我们的查询参数为null的条款，并优化查询的执行计划，使其不会有明显的性能开销。对于一些查询或数据库服务器，特别是涉及到巨大的表扫描，可能会有性能开销。

4. Conclusion

4.结论

In this article, we demonstrated how Spring Data JPA interprets null parameters in query methods and how to change the default behavior.

在这篇文章中，我们演示了Spring Data JPA如何解释查询方法中的null参数以及如何改变默认行为。

Perhaps in the future, we’ll be able to specify how to interpret null parameters using the @NullMeans annotation. Notice that it’s a proposed feature at this time and is still under consideration.

也许在未来，我们能够使用@NullMeans注解来指定如何解释null参数。请注意，目前这只是一个提议的功能，仍在考虑之中。

To sum up, there are two main ways to interpret null parameters, and they would both be provided by the proposed @NullMeans annotation:

总而言之，有两种解释null参数的主要方式，它们都将由提议的@NullMeans注解提供。

  • IS (is null) – the default option demonstrated in Section 3.1.
  • IGNORED (exclude a null parameter from the WHERE clause) – achieved either by extra query methods (Section 3.2.) or by using a workaround (Section 3.3.)

As usual, the complete source code is available over on GitHub.

像往常一样，完整的源代码可以在GitHub上找到，。