Spring Data JpaRepository "JOIN FETCH" returns duplicates

user_13280508 0
Viewed 1742

I'm writing a simple Spring Data JPA application. I use MySQL database. There are two simple tables:

  • Department
  • Employee

Each employee works in some department (Employee.department_id).

@Entity
public class Department {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    private Long id;

    @Basic(fetch = FetchType.LAZY)
    @OneToMany(mappedBy = "department")
    List<Employee> employees;
}

@Entity
public class Employee {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    private Long id;

    @ManyToOne
    @JoinColumn
    private Department department;
}

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
    @Query("FROM Department dep JOIN FETCH dep.employees emp WHERE dep = emp.department")
    List<Department> getAll();
}

The method getAll returns a list with duplicated departments (each department is repeated as many times as there are employees in this department).

Question 1: Am I rigth that this is a feature related not to Spring Data JPA, but to to Hibernate?
Question 2: What is the best way to fix it? (I found at least two ways: 1) use Set<Department> getAll(); 2) use "SELECT DISTINCT dep" in @Query annotation)

spring-data-jpa spring sql java
4 Answers

FROM Department dep JOIN FETCH dep.employees emp expression generates native query which returns plain result Department-Employee. Every Department will be returned dep.employees.size() times. This is a JPA-provider expected behavior (Hibernate in your case).

Using distinct to get rid of duplicates seems like a good option. Set<Department> as a query result makes it impossible to get the ordered result.

user_102055140

You probably want to use LEFT JOIN FETCH as well as DISTINCT. Normal JOIN FETCH uses an inner join so Departments without any employees will not be returned by your query. LEFT JOIN FETCH uses an outer join instead.

user_182722970

I'm not sure about using @Query here. If you mapped these 2 entities via annotations @OneToMany and @ManyToOne this join is redundant. And second moment here, why is @Basic(fetch = FetchType.LAZY) here? Maybe you should set lazy init in @OneToMany ? Check out this resource, I hope you'll find it helpfull

user_133147170

With my knowledge, the use case you have mentioned, you don't need to define your own method and instead use repository.findAll which is inherited from PagingAndSortingRepository which extends CrudRepository and implemented by SimpleJpaRepository. See here

So just leave the repository interface blank as below,

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {

}

Then inject and use it wherever you need, e.g. lets say with DepartmentService

public interface DepartmentService {
    
    List<Link> getAll();    
}

@Component
public class DepartmentServiceImpl implements DepartmentService {

    private final DepartmentRepository  repository;    

    // dependency injection via constructor
    @Autowired
    public DepartmentServiceImpl(DepartmentRepository repository) {
        this.repository = repository;
    }

    @Override
    public List<Department> getAll() {  
        return repository.findAll();
    }
}
user_138780230
Related