JPA count query with WHERE clause

1. Overview

This article explains implementing the count query with WHERE clause in Spring JPA. The JPA module supports defining a query manually as a String using @Query annotation or having it being derived from the method name. The latter derived query is quite convenient for simple use cases.

2. JPA count query with WHERE clause

Here, we will see both custom and derived count queries to retrieve the count of rows available in the table.

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. Repository

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.

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

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

3. Derived count query based on a where clause

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

You can easily do so by using the derived count query available in the CrudRepository since the JpaRepository interface extends the CrudRepositoy.

For example, the following countByNewUser is 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);
    }

The Spring JPA method parser parses the above countByNewUser query method and translates into the query: select count(u) from Customer u where u.new_user = ?1.

4. Count with Query annotation

You can also manually write custom native queries using the @Query annotation. The derived count queries are mostly suitable for simple use cases. However, the method name would get unnecessarily ugly for complex queries. Further, the derived method query does not support operations such as GROUP By clause.

The following code does the same job as the preceding 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 to map the method arguments with the custom 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);

}

5. JPA query count with pagination

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

6. Conclusion

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