How To Use findBy() With Multiple Columns in JPA – 如何在 JPA 中对多列使用 findBy()

1. Introduction

1.导言

Spring Dat;u JPA provides a query derivation feature, using which we can derive queries automatically by just following the method name conventions.

Spring Dat;u JPA 提供了查询派生功能，我们只需遵循方法名称约定，就能自动派生查询。

In this article, we’ll use the query derivation feature to find entities by one or more columns.

在本文中，我们将使用查询派生功能，通过一列或多列查找实体。

2. Example Setup

2.设置示例

For example purposes, we’ll use an Account entity which contains properties related to the user account:

例如，我们将使用一个 Account 实体，其中包含与用户账户相关的属性：

@Entity
@Table(name = "ACCOUNTS")
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "accounts_seq")
    @SequenceGenerator(name = "accounts_seq", sequenceName = "accounts_seq", allocationSize = 1)
    @Column(name = "user_id")
    private int userId;
    private String username;
    private String password;
    private String email;
    private Timestamp createdOn;
    private Timestamp lastLogin;

    @OneToOne
    @JoinColumn(name = "permissions_id")
    private Permission permission;
    
    // getters and setters
}

For demo purposes, we’ll also add some sample data to the Accounts table:

出于演示目的，我们还将在 Accounts 表中添加一些示例数据：

3. Query Derivation

3.查询推导

Query derivation allows the developer to define method names in the repository interface that follow a naming convention, and the framework generates an appropriate query based on that method name.

查询推导允许开发人员在存储库界面中定义遵循命名约定的方法名称，然后框架会根据该方法名称生成适当的查询。

For example, if we want to search the accounts table by email address, then our method in AccountRepository would look like below:

例如，如果我们想通过电子邮件地址搜索 accounts 表，那么 AccountRepository 中的方法将如下所示：

public interface AccountRepository extends JpaRepository<Account, Integer> {
    Account findByEmail(String email);
}

If we execute findByEmail() on AccountRepository, Spring Data generates the SQL below and executes it against the accounts table:

如果我们在 AccountRepository 上执行 findByEmail() ，Spring Data 会生成下面的 SQL 并在 accounts 表中执行：

select a1_0.user_id,a1_0.created_on,a1_0.email,a1_0.last_login,a1_0.password,a1_0.permissions_id,a1_0.username
from accounts a1_0 
where a1_0.email=?

Our test verifies the working of findByEmail() :

我们的测试验证了 findByEmail() 的工作情况：

@Test
void givenAccountInDb_whenPerformFindByEmail_thenReturnsAccount() {
    String email = "test@test.com";
    Account account = accountRepository.findByEmail(email);
    assertThat(account.getEmail()).isEqualTo(email);
}

4. findBy() With Multiple Columns

4.findBy() 具有多个列

We can extend the query derivation feature to add a combination of conditions to get an appropriate result.

我们可以扩展查询推导功能，添加条件组合以获得合适的结果。

Let’s use the AccountRepository interface and write another method for finding Accounts with usernames and emails:

让我们使用 AccountRepository 接口，编写另一种方法来查找带有 usernames 和 emails 的账户：

public interface AccountRepository extends JpaRepository<Account, Integer> {
    Account findByUsernameAndEmail(String username, String email);
}

Here’s the generated SQL for the defined method:

下面是为已定义方法生成的 SQL：

select a1_0.user_id,a1_0.created_on,a1_0.email,a1_0.last_login,a1_0.password,a1_0.permissions_id,a1_0.username
from accounts a1_0
where a1_0.username=? and a1_0.email=?

Our test verifies the working of findByUsernameAndEmail():

我们的测试验证了 findByUsernameAndEmail() 的工作：

@Test
void givenAccountInDb_whenPerformFindByUsernameAndEmail_thenReturnsAccount(){
    String email = "test@test.com";
    String username = "user_admin";
    Account account = accountRepository.findByUsernameAndEmail(username, email);
    assertThat(account.getUsername()).isEqualTo(username);
    assertThat(account.getEmail()).isEqualTo(email);
}

We can also use the OR operator to combine two conditions. For example, we can search by either username or email:

我们还可以使用 OR 操作符来组合两个条件。例如，我们可以使用 username 或 email 进行搜索：

public interface AccountRepository extends JpaRepository<Account, Integer> {
    Account findByUsernameOrEmail(String username, String email);
}

Let’s see the generated SQL:

让我们看看生成的 SQL：

select a1_0.user_id,a1_0.created_on,a1_0.email,a1_0.last_login,a1_0.password,a1_0.permissions_id,a1_0.username
from accounts a1_0
where a1_0.username=? or a1_0.email=?

Now, let’s verify the working of findByUsernameOrEmail():

现在，让我们来验证 findByUsernameOrEmail() 的工作情况：

@Test
void givenAccountInDb_whenPerformFindByUsernameOrEmail_thenReturnsAccount(){
    String email = "test@test.com";
    String username = "user_editor";
    Account account = accountRepository.findByUsernameOrEmail(username, email);
    assertThat(account.getUsername()).isNotEqualTo(username);
    assertThat(account.getEmail()).isEqualTo(email);
}

We can use the collection of input in findBy() method. For example, to find all accounts that exist in the list of emails or list of usernames, we can write a method in AccountRepository:

我们可以在 findBy() 方法中使用输入集合。例如，要查找电子邮件列表或用户名列表中存在的所有帐户，我们可以在 AccountRepository 中编写一个方法：

public interface AccountRepository extends JpaRepository<Account, Integer> {
    List<Account> findByUsernameInOrEmailIn(List<String> usernames, List<String> emails);
}

Let’s check out the generated SQL:

让我们看看生成的 SQL：

select a1_0.user_id,a1_0.created_on,a1_0.email,a1_0.last_login,a1_0.password,a1_0.permissions_id,a1_0.username 
from accounts a1_0 
where a1_0.username in (?,?) or a1_0.email in (?,?,?)

Our test confirms the working of findByUsernameInOrEmailIn():

我们的测试证实了 findByUsernameInOrEmailIn() 的工作：

@Test
void givenAccountInDb_whenPerformFindByUsernameInOrEmailIn_thenReturnsAccounts(){
    List<String> emails = List.of("test@test.com", "abc@abc.com", "pqr@pqr.com");
    List<String> usernames = List.of("user_editor", "user_admin");
    List<Account> byUsernameInOrEmailIn = accountRepository.findByUsernameInOrEmailIn(usernames, emails);
    assertThat(byUsernameInOrEmailIn.size()).isEqualTo(1);
    assertThat(byUsernameInOrEmailIn.get(0).getEmail()).isEqualTo("test@test.com");
}

5. Conclusion

5.结论

In this tutorial, we discussed the query derivation feature of Spring Data and used it to find entities in a table. We also explored the usage of various input parameters for finding entities with conditions such as AND and OR.

在本教程中，我们讨论了 Spring Data 的查询推导功能，并用它查找表中的实体。我们还探讨了使用 AND 和 OR 等条件查找实体的各种输入参数的用法。

As always, the example code is available over on GitHub.

在 GitHub 上提供了示例代码。