Spring Data JPA 使用 findby 定义多个条件列-工具盒子

1、简介 {#1简介}

Spring Data JPA 提供了查询推导功能（派生查询），只需遵循方法名称约定就能自动推导出查询。

本文将带你了解如何使用查询推到功能，通过一列或多列查找实体。

2、设置 {#2设置}

定义一个 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;
    
    // getter / setter
}

为了演示，在 ACCOUNTS 表中添加一些示例数据：

| userId | username | password | email | createdOn | lastLogin | permission | |--------|--------------|----------|----------------|---------------------|---------------------|------------| | 1 | user_admin | - | test@test.com | 2024-02-08 21:26:30 | 2024-02-09 21:26:30 | editor | | 2 | user_admin_1 | - | test1@test.com | 2024-02-06 21:26:30 | 2024-02-07 21:26:30 | editor | | 3 | user_admin_2 | - | test2@test.com | 2024-02-04 21:26:30 | 2024-02-06 21:26:30 | editor |

3、查询推导 {#3查询推导}

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

例如，如果我们想按 email 检索 accounts 表，那么 AccountRepository 中的方法如下所示：

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

如果我们在 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=?

测试如下：

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

4、findBy() 多个条件列 {#4findby-多个条件列}

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

使用 AccountRepository 接口，编写另一个方法来查找具有指定 username 和 email 的账户：

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

上述方法生成的 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=?

测试方法如下:

@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);
}

还可以使用 OR 运算符来组合两个条件。例如，可以通过 username 或 email 进行检索：

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

生成的 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=?

测试方法如下：

@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);
}

可以在 findBy() 方法中使用集合。例如，要查找在 email 集合或 username 集合中存在的所有账户，可以在 AccountRepository 中编写如下方法：

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

生成的 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 (?,?,?)

测试方法如下：

@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");
}