Count the Number of Rows in Spring Data JPA

最后修改:2024年6月4日

by Suraj Mishra

1. 概述

Spring Data JPA 实现提供了对 Jakarta Persistence API 的支持，用于管理和对象关系映射，以及相关功能。本文将探讨使用 JPA 进行表中行数计数的不同方法。

2. 实体类

以 Account 实体为例，它与 Permission 实体之间存在一对一的关系。

@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 , setters
}

Permission 属于 Account 实体。

@Entity
@Table(name="PERMISSIONS")
public class Permission {
    @Id
    @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "permissions_id_sq")
    @SequenceGenerator(name = "permissions_id_sq", sequenceName = "permissions_id_sq", allocationSize = 1)
    private int id;

    private String type;

    // getters , setters
}

3. 使用 JPA 存储库

Spring Data JPA 提供了一个可以扩展的存储库接口，它提供了一套预先定义好的查询方法，如 findAll()、findBy()、save()、saveAndFlush()、count()、countBy()、delete() 和 deleteAll() 等。

我们将定义一个扩展自 JpaRepository 的 AccountRepository 接口，从而可以访问 count 方法。

如果我们需要根据一或多个条件（如 countByFirstname()、countByPermission() 或 countByPermissionAndCredtedOnGreaterThan()）进行计数，只需要在 AccountRepository 接口中定义相应方法名，查询衍生功能会自动为我们生成合适的 SQL 语句。

public interface AccountRepository extends JpaRepository<Account, Integer> { 
    long countByUsername(String username);
    long countByPermission(Permission permission); 
    long countByPermissionAndCreatedOnGreaterThan(Permission permission, Timestamp ts)
}

在下面的例子中，我们将在逻辑类中使用 AccountRepository 来执行计数操作。

3.1. 计算表中的所有行数

我们会在逻辑类中注入 AccountRepository，对于简单的行计数操作，只需使用 accountRepository.count()，就能得到结果。

@Service
public class AccountStatsLogic {
    @Autowired
    private AccountRepository accountRepository;

    public long getAccountCount(){
        return accountRepository.count();
    }
}

3.2. 根据单一条件计数结果行

如前所述，AccountRepository 中包含诸如 countByPermission 和 countByUsername 等方法，Spring Data JPA 的查询衍生功能会为这些方法生成相应的查询。

因此，在逻辑类中，我们可以使用这些方法基于特定条件进行计数，并获取结果。

@Service
public class AccountStatsLogic {
    @Autowired
    private AccountRepository accountRepository;

    @Autowired
    private PermissionRepository permissionRepository;
    
    public long getAccountCountByUsername(){
        String username = "user2";
        return accountRepository.countByUsername(username);
    }
    
    public long getAccountCountByPermission(){
        Permission permission = permissionRepository.findByType("reader");
        return accountRepository.countByPermission(permission);
    }
}

3.3. 根据多个条件计数结果行

我们也可以在查询衍生中包含多个条件，如下所示，包括 Permission 和 CreatedOnGreaterThan。

@Service
public class AccountStatsLogic {
    @Autowired
    private AccountRepository accountRepository;

    @Autowired
    private PermissionRepository permissionRepository;
    
    public long getAccountCountByPermissionAndCreatedOn() throws ParseException {
        Permission permission = permissionRepository.findByType("reader");
        Date parsedDate = getDate();
        return accountRepository.countByPermissionAndCreatedOnGreaterThan(permission, new Timestamp(parsedDate.getTime()));
    }
}

4. 使用 CriteriaQuery

在 JPA 中计数行数的另一种方法是使用 CriteriaQuery 接口。这个接口允许我们以面向对象的方式编写查询，这样就不必了解如何编写原始 SQL 查询了。

我们需要构建一个 CriteriaBuilder 对象，然后使用它来构建 CriteriaQuery。一旦 CriteriaQuery 准备就绪，可以从 entityManager 中调用 createQuery 方法执行查询并获取结果。

4.1. 计算所有行数

当使用 CriteriaQuery 构建查询时，我们可以定义一个计数查询，如下所示。

public long getAccountsUsingCQ() throws ParseException {
    // creating criteria builder and query
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
    Root<Account> accountRoot = criteriaQuery.from(Account.class);
     
    // select query
    criteriaQuery.select(builder.count(accountRoot));
     
     // execute and get the result
    return entityManager.createQuery(criteriaQuery).getSingleResult();
}

4.2. 根据单一条件计数结果行

我们还可以扩展计数查询，加入 WHERE 条件以根据某些条件过滤查询。可以在构建器实例上添加一个 Predicate 并传递给 where 子句。

public long getAccountsByPermissionUsingCQ() throws ParseException {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
    Root<Account> accountRoot = criteriaQuery.from(Account.class);
        
    List<Predicate> predicateList = new ArrayList<>(); // list of predicates that will go in where clause
    predicateList.add(builder.equal(accountRoot.get("permission"), permissionRepository.findByType("admin")));

    criteriaQuery
      .select(builder.count(accountRoot))
      .where(builder.and(predicateList.toArray(new Predicate[0])));

    return entityManager.createQuery(criteriaQuery).getSingleResult();
}

4.3. 根据多个条件计数结果行

在我们的 Predicate 中，可以在查询上添加多个条件。构建器实例提供了如 equal() 和 greaterThan() 等条件方法来支持查询条件。

public long getAccountsByPermissionAndCreateOnUsingCQ() throws ParseException {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
    Root<Account> accountRoot = criteriaQuery.from(Account.class);
    
    List<Predicate> predicateList = new ArrayList<>();
    predicateList.add(builder.equal(accountRoot.get("permission"), permissionRepository.findByType("reader")));
    predicateList.add(builder.greaterThan(accountRoot.get("createdOn"), new Timestamp(getDate().getTime())));

    criteriaQuery
      .select(builder.count(accountRoot))
      .where(builder.and(predicateList.toArray(new Predicate[0])));

    return entityManager.createQuery(criteriaQuery).getSingleResult();
}

5. 使用 JPQL 查询

进行计数的另一种方法是使用 JPQL。JPQL 查询针对实体而不是直接针对数据库工作，它们看起来像 SQL 查询，但更面向对象。在 JPA 中，我们始终可以编写一个能计算行数的 JPQL 查询。

5.1. 计算所有行数

entityManager 提供了一个 createQuery() 方法，它接受 JPQL 查询作为参数，并在数据库上执行。

public long getAccountsUsingJPQL() throws ParseException {
    Query query = entityManager.createQuery("SELECT COUNT(*) FROM Account a");
    return (long) query.getSingleResult();
}

5.2. 根据单一条件计数结果行

在 JPQL 查询中，我们可以像使用原始 SQL 一样在 WHERE 子句中包含条件，以过滤查询并计数返回的行。

public long getAccountsByPermissionUsingJPQL() throws ParseException {
    Query query = entityManager.createQuery("SELECT COUNT(*) FROM Account a WHERE a.permission = ?1");
    query.setParameter(1, permissionRepository.findByType("admin"));
    return (long) query.getSingleResult();
}

5.3. 根据多个条件计数结果行

在 JPQL 查询中，我们可以在 WHERE 子句中包含多个条件，就像在原始 SQL 中一样，以过滤查询并计数返回的行。

public long getAccountsByPermissionAndCreatedOnUsingJPQL() throws ParseException {
    Query query = entityManager.createQuery("SELECT COUNT(*) FROM Account a WHERE a.permission = ?1 and a.createdOn > ?2");
    query.setParameter(1, permissionRepository.findByType("admin"));
    query.setParameter(2, new Timestamp(getDate().getTime()));
    return (long) query.getSingleResult();
}

6. 总结

在这篇教程中，我们学习了在 JPA 中计数行数的不同方法。诸如 CriteriaBuilder 和 Spring Data JPA 查询衍生这样的特性帮助我们轻松地编写带有不同条件的计数查询。

虽然 CriteriaQuery 和 Spring Data JPA 查询衍生可以帮助我们构建无需了解原始 SQL 的查询，但在某些情况下，如果它们不能满足需求，我们仍可以使用 JPQL 编写原始 SQL 查询。

如往常一样，示例代码可以在 GitHub 上找到。

« 上一篇： YugabyteDB 快速入门指南
» 下一篇： Java Weekly, 第498期