1. Overview
The Spring Data JPA implementation provides repository support for the Jakarta Persistence API for managing persistence and object-relational mapping and functions.
In this article, we will 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(), deleteAll().
We will 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 countByFirstname(), countByPermission(), or countByPermissionAndCredtedOnGreaterThan(), all we need is the name of the method in the AccountRepository interface and 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 will use AccountRepository in the logic class to perform the count operation.
3.1. Count All the Rows in the Table
We will define a logic class where we inject AccountRepository, and for simple row count() operation, we can just 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 such as countByPermission and countByUsername, and Spring Data JPA query derivation will derive the query for these methods.
So we can use these methods for the conditional count in the logic class, and we will 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 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, as shown below.
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 on which we’d like to filter 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. Conclusion
In this tutorial, we learned a different approach to counting the number of rows in JPA. The specifications, such as CriteriaBuilder and Spring Data JPA Query derivation, help us to write count queries easily with different conditions.
While CriteriaQuery & Spring Data JPA query derivation helps us build queries that don’t require raw SQL knowledge, in some use cases, if it doesn’t serve the purpose, we can always write raw SQL using JPQL.
As always, the example code is available over on GitHub.
