Find Distinct Rows Using Spring Data JPA

1. Overview

In some situations, we need to fetch unique elements from the database. This tutorial focuses on querying distinct data with Spring Data JPA, examining various methods to retrieve distinct entities and fields.

2. Scenario Setup

Let’s create two entity classes, School and Student, for our illustration:

@Entity
@Table(name = "school")
public class School {
    @Id
    @Column(name = "school_id")
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private int id;

    @Column(name = "name", length = 100)
    private String name;

    @OneToMany(mappedBy = "school")
    private List<Student> students;

    // constructors, getters and setters
}

@Entity
@Table(name = "student")
public class Student {
    @Id
    @Column(name = "student_id")
    private int id;

    @Column(name = "name", length = 100)
    private String name;

    @Column(name = "birth_year")
    private int birthYear;

    @ManyToOne
    @JoinColumn(name = "school_id", referencedColumnName = "school_id")
    private School school;

    // constructors, getters and setters
}

We’ve defined a one-to-many relationship where each school is associated with multiple students.

3. Distinct Entities

Our sample entities are now set. Let’s go ahead and create a repository for retrieving the distinct schools by the student’s birth year. There are different approaches to getting distinct rows using Spring Data JPA. The first one is using derived queries:

@Repository
public interface SchoolRepository extends JpaRepository<School, Integer> {
    List<School> findDistinctByStudentsBirthYear(int birthYear);
}

The derived query is self-explanatory and easy to understand. It finds all distinct School entities by the birth year of the Student. If we call the method, we’ll see the SQL executed by the JPA on the School Entity in the console log. It shows that all fields are retrieved except the relationship:

Hibernate: 
    select
        distinct s1_0.school_id,
        s1_0.name 
    from
        school s1_0 
    left join
        student s2_0 
            on s1_0.school_id=s2_0.school_id 
    where
        s2_0.birth_year=?

If we want the distinct count rather than the whole entity, we can create another derived query by replacing find with count in the method name:

Long countDistinctByStudentsBirthYear(int birthYear);

4. Distinct Fields by Custom Query

In some cases, we don’t need to retrieve every field from an entity. Suppose we want to display search results on an entity in a web interface. The search result may need to show only a few fields from the entity. For such a scenario, we can reduce the retrieval time by limiting the fields we need, especially when the result set is large.

In our example, we’re only interested in the distinct school names. Thus, we’ll create a custom query to retrieve the school names only. We annotate the method with @Query and put the JPQL query within it. We pass the birth year parameter into the JPQL via the @Param annotation:

@Query("SELECT DISTINCT sch.name FROM School sch JOIN sch.students stu WHERE stu.birthYear = :birthYear")
List<String> findDistinctSchoolNameByStudentsBirthYear(@Param("birthYear") int birthYear);

Upon execution, we will see the following SQL generated by the JPA in the console log. It only involves a school name field instead of all fields:

Hibernate: 
    select
        distinct s1_0.name 
    from
        school s1_0 
    join
        student s2_0 
            on s1_0.school_id=s2_0.school_id 
    where
        s2_0.birth_year=?

5. Distinct Fields by Projections

Spring Data JPA query methods usually use the entity as the return type. However, we could apply projections offered by Spring Data as an alternative to the custom query approach. This allows us to retrieve partial fields from an entity rather than all.

As we want to limit the retrieval to the school name field only, we’ll create an interface that contains the getter method of the name field in the School entity:

public interface NameView {
    String getName();
}

The method name in the projection interface must be the same as the getter method name in our target entity. After all, we have to add the following method to the repository:

List<NameView> findDistinctNameByStudentsBirthYear(int birthYear);

Upon execution, we will see the SQL generated is similar to the one generated in the custom query:

Hibernate: 
    select
        distinct s1_0.name 
    from
        school s1_0 
    left join
        student s2_0 
            on s1_0.school_id=s2_0.school_id 
    where
        s2_0.birth_year=?

6. Conclusion

In this article, we explored different approaches to query distinct rows from the database via Spring Data JPA, including distinct entities and distinct fields. We may use different approaches depending on our needs.