Spring jpa get count

1. Overview

This article explains all the solutions to get the count of entities available in the table using Spring JPA.

2. Spring jpa get count

Several ways are available to retrieve the count of rows available in the table through spring JPA.

2.1. Entity class

First, let’s create an entity class that represents the Customer table in the database. We specified the primary key of the entity class by annotating the field id with @Id. The newUser flag shows whether that customer is a new user.

@Entity
@AllArgsConstructor
@Data
public class Customer {

    @Id
    private String id;
    private String name;
    private int newUser;

}

Here, we have annotated the Entity class with annotations @AllArgsConstructor and @Data from the Lombok library to generate a constructor with all arguments and getters/setters. However, you can also manually create a constructor and getters/setters rather than using the Lombok library.

2.2. A JpaRepository instance

Now, let’s create a Repository class for the Spring JPA. The annotation @Repository is a specialization of @Component annotation which is used to show that the implementing class provides the mechanism for storage, retrieval, update, delete and search operation on DB objects.

You can also use the JpaRepository interface. The JpaRepository extends PagingAndSortingRepository which in turn extends CrudRepository. Because of this inheritance, JpaRepository will have all the functions of CrudRepository and PagingAndSortingRepository.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {

    List<Customer> findAllByName(String name, Pageable pageable);
}

3. count() repository method

If you want to know the total number of rows available in the entity table, use the count derived method of the CrudRepository interface.

For example, the following getCustomerCount method retrieves the number of entities available in the table using the method count.

@Autowired
CustomerRepository customerRepository;

public Long getCustomerCount() {
    return customerRepository.count();
}

4. Count based on a condition

Often you want to get the count of entities based on a particular condition. Consider you want to get the number of new users available in the Customer table.

You can easily do so by using the derived count query available in the CrudRepository. The following code contains a derived count query.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {
    long countByNewUser(int newUser);
}

The Customer database contains a new_user column that holds either 0 or 1. 1 stands for new user whereas 0 for existing user. To find the number of new users, we are passing 1 to the count repository method.

public Long getCustomerCount() {
        return customerRepository.countByNewUser(1);
    }

5. @Query

You can also @Query annotation to count the number of entities. The following code does the same job as the aforementioned derived count query countByNewUser. It returns the number of new users available in the customer table.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {

    @Query("SELECT COUNT(u) FROM Customer u WHERE u.new_user=?1")
    long getCountOfNewUsers(int newUser);

}

Alternatively, you can use the @Param annotation alongside @Query.

import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {

    @Query("SELECT COUNT(u) FROM Customer u WHERE u.new_user=:new_user")
    long getCountOfNewUsers(@Param("new_user") int newUser);

}

6. Query by example

You can use Query By Example as JpaRepository extends QueryByExampleExecutor. Query by Example (QBE) is a method of query creation that allows us to execute queries based on an example entity instance. So you don’t even need to define custom count methods on your interface.

The below code takes the customer instance as a query example and retrieves the count of new customers.

public Long getNewCustomerCount() {
    Customer customer = new Customer();
    customer.setNew_user(1);
    return customerRepository.count(Example.of(customer));
}

7. Count with pagination

You can also retrieve the count while querying the data with pagination. See this detailed article for the same.

8. Conclusion

To sum up, we have learned to get the count in Spring JPA. You can find the code samples of this article in our GitHub repository.