Spring JPA count distinct

1. Overview

This article points out all the solutions to get the distinct count of entities using Spring JPA.

2. Spring JPA count distinct

The COUNT DISTINCT function in a DB query returns the number of unique values in the column or expression. You can get this information easily from Spring JPA by either using the derived or custom query.

2.1. Spring Jpa entity class

First, let’s create an entity class that represents the Feedback table in the database. The Feedback table contains feedbackId, userId, title, and comments. We specified the feedbackId as the primary key of the entity class by annotating it with @Id.

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Feedback {
    @Id
    private String feedbackId;
    private String userId;
    private String title;
    private String comments;
}

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. Spring Jpa repository

Now, let’s create a Repository class for the Spring JPA that implements the mechanism for storage, retrieval, update, delete and search operation on DB objects.

You can use the JpaRepository interface to create a repository. 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 FeedbackRepository extends JpaRepository<Feedback, String>

3. Distinct count based on a condition

Often you want to get the distinct count of entities based on a where clause. Assume you have an application that collect user feedbacks about a product. Some users may mistakenly update the same remarks more than once in your system. You can use the distinct count query to get the number of feedbacks provided by a particular user removing the duplicates.

The derived count query is available in the CrudRepository interface.

For example, the following derived query gets the count of unique comments provided by a particular user.

@Repository
public interface FeedbackRepository extends JpaRepository<Feedback, String> {

    long countDistinctCommentsByUserId(String userId);
}

The user 1236 has updated the same "Email validation failed" comment more than once in the table. There are totally 2 entries for user_id 1236 without duplicates.

If you execute the below code, it prints 2 as expected.

System.out.println(feedbackRepository.countDistinctCommentsByUserId("1234"));

5. Custom query to get a distinct count

You can also @Query annotation to count the number of entities. The following code does the same job as the previously mentioned derived count query countDistinctCommentsByUserId. It returns the number of unique feedbacks available for the specified user_id in the Feedback table.

@Repository
public interface FeedbackRepository extends JpaRepository<Feedback, String> {

    @Query("SELECT COUNT(u) FROM Feedback u WHERE u.userId=?1")
    long getDistinctCountByUserId(String userId);
}

You can also use the @Param annotation alongside @Query to map the method argument with the query.

@Repository
public interface FeedbackRepository extends JpaRepository<Feedback, String> {

    @Query("SELECT COUNT(u) FROM Feedback u WHERE u.userId=:userId")
    long getDistinctCountByUserId(@Param String userId);
}

7. Count with Spring 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 distinct count in Spring JPA. You can find the code samples of this article in our GitHub repository.