Count the Number of Rows in Spring Data JPA

1. Overview

The Spring Data JPA implementation provides repository support for the Jakarta Persistence API for managing persistence, as well as object-relational mapping and functions.

In this tutorial, we’ll explore different ways to count the number of rows in a table using the JPA.

2. Entity Classes

For our example, we’ll use the Account entity, which has a one-to-one relation with the Permission entity:

@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 belongs to the account entity:

@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. Using the JPA Repository

Spring Data JPA provides a repository interface that can be extended, which offers out-of-the-box query methods and derived query methods, such as findAll(), findBy(), save(), saveAndFlush(), count(), countBy(), delete(), and deleteAll().

We’ll define the AccountRepository interface that extends the JpaRepository interface, so that will get access to the count methods.

If we need to count based on one or more conditions, such as countByFirstname(), countByPermission(), or countByPermissionAndCredtedOnGreaterThan(), all we need is the name of the method in the AccountRepository interface, and then query derivation will take care of defining the appropriate SQL for it:

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

In the below examples, we’ll use AccountRepository in the logic class to perform the count operation.

3.1. Count All the Rows in the Table

We’ll define a logic class where we inject AccountRepository. For a simple row count() operation, we can use accountRepository.count(), and we’ll get the result:

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

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

3.2. Count Result Rows Based on the Single Condition

As we defined above, AccountRepository contains the method names countByPermission and countByUsername, so Spring Data JPA query derivation will derive the query for these methods.

We can use these methods for the conditional count in the logic class, and we’ll get the result:

@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. Count Result Rows Based on the Multiple Conditions

We can also include multiple conditions in our query derivation, like the one below, where we include Permission and 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. Using CriteriaQuery

The next approach to counting the rows in JPA is to use the CriteriaQuery interface. This interface allows us to write queries in an Object oriented way, so that we can skip the knowledge of writing raw SQL queries.

It requires us to construct a CriteriaBuilder object, which then helps us construct CriteriaQuery. Once CriteriaQuery is ready, we can use the createQuery method from entityManager to execute the query and get the result.

4.1. Count All the Rows

Now when we construct the query using CriteriaQuery, we can define the select query to count:

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. Count Result Rows Based on the Single Condition

We can also extend the select query to include where conditions to filter our query on certain conditions.. We can add a Predicate to our builder instance and pass it to the where clause:

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. Count Result Rows Based on the Multiple Conditions

In our predicate, we can add multiple conditions for filtering our query. The builder instance provides conditional methods, such as equal() and greaterThan(), to support conditions on queries:

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. Using JPQL Query

The next approach to performing count is to use JPQL. JPQL queries work against entities, instead of databases directly, that more or less look like SQL queries. We can always write a JPQL query that can count rows in JPA.

5.1. Count All the Rows

The entityManager provides a createQuery() method that takes the JPQL query as an argument, and executes that on the database:

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

5.2. Count Result Rows Based on the Single Condition

In JPQL query, we can include WHERE conditions, as we do in raw SQL, to filter queries and count the returned rows:

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. Count Result Rows Based on the Multiple Conditions

In JPQL query, we can include multiple conditions in the WHERE clause, as we do in raw SQL, to filter queries and count the returned rows:

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. Count the Number of Rows With Hibernate Criteria

For applications using older versions of Hibernate or cases where the Criteria API is still in place, Hibernate offers its own Criteria interface for building dynamic queries. This can be a helpful alternative for counting rows in cases where JPA’s CriteriaQuery API isn’t being used.

Although Hibernate’s Criteria API is deprecated in favor of the CriteriaQuery API, it remains useful in many legacy applications.

In this example, we’ll use two entities, Foo and Bar, which are associated through a many-to-one relationship. Each Foo object is linked to a specific Bar object, and we’ll count the number of Foo objects based on conditions applied to both the Foo and its associated Bar.

6.1. Count All Rows in the Table

Using the Hibernate Criteria API, we can count all rows in a table by setting a Projection on the Criteria. In this example, we’ll configure a Projections.rowCount() to retrieve a count of rows in the Foo entity:

public long countAllRowsUsingHibernateCriteria() {
    Session session = entityManager.unwrap(Session.class);
    Criteria criteria = session.createCriteria(Foo.class);
    criteria.setProjection(Projections.rowCount());
    Long count = (Long) criteria.uniqueResult();
    return count != null ? count : 0L;
}

In the countAllRowsUsingHibernateCriteria() method, we first obtain a Hibernate Session from the EntityManager by calling unwrap() method. Then, we create a Criteria instance for our entity, using Projections.rowCount() to specify that we want a count of all rows. Finally, we call uniqueResult() to retrieve the single count result from the query.

6.2. Count Rows Based on a Single Condition

To count rows based on a single condition, we can add a restriction to the Criteria to filter the results. In this example, we count the number of Foo objects based on a specific Bar object name:

public long getFooCountByBarNameUsingHibernateCriteria(String barName) {
    Session session = entityManager.unwrap(Session.class);
    Criteria criteria = session.createCriteria(Foo.class);
    criteria.createAlias("bar", "b");
    criteria.add(Restrictions.eq("b.name", barName));
    criteria.setProjection(Projections.rowCount());
    return (Long) criteria.uniqueResult();
}

In this code, we use createAlias() to create an alias “b” for the bar field. Then, we add a restriction using Restrictions.eq(), setting it to filter rows by the specified barName. Finally, we use Projections.rowCount() to indicate that we want a count of rows that match this condition and call uniqueResult() to retrieve the result.

6.3. Count Rows Based on Multiple Conditions

We can add several Restrictions to the Criteria to count rows based on multiple conditions. In this example, we count Foo rows where the associated Bar‘s name is a specific value, and the Foo‘s name matches a specified value:

public long getFooCountByBarNameAndFooNameUsingHibernateCriteria(String barName, String fooName) {
    Session session = entityManager.unwrap(Session.class);
    Criteria criteria = session.createCriteria(Foo.class);
    criteria.createAlias("bar", "b");
    criteria.add(Restrictions.eq("b.name", barName));
    criteria.add(Restrictions.eq("name", fooName));
    criteria.setProjection(Projections.rowCount());
    return (Long) criteria.uniqueResult();
}

Similar to the previous example, we use createAlias() to create an alias for the bar field. This time, we add two restrictions: one with Restrictions.eq() to filter rows by bar.name, and another with Restrictions.eq() to filter rows where the Foo object’s name matches the specified value. We then set Projections.rowCount() to count the rows that match these conditions and call uniqueResult() to retrieve the result.

7. Conclusion

In this article, we learned different approaches to counting the number of rows in JPA. The specifications, such as CriteriaBuilder and Spring Data JPA Query derivation, help us write count queries easily with different conditions.

While CriteriaQuery & Spring Data JPA query derivation help us build queries that don’t require raw SQL knowledge, in some use cases, we may want to write raw SQL using JPQL.

As always, the example code is available on GitHub. The code for Section 6 can be found in a different module, available here.