Duplicate data in the child object

Question

I use spring boot 2.1 with spring data jpa and hibernate

@Entity
public class Factories{
    @Id
    @SequenceGenerator(name = "factories_id_seq", sequenceName = "factories_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "factories_id_seq")
    private Integer id;

    @OneToMany(mappedBy = "factory", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    private List<Machines> machines = new ArrayList<>();

    @ElementCollection(fetch = FetchType.LAZY)
    private Set<String> emails = new HashSet<>();

}   

In my machine entity, i have an equals and hashCode method.

Query done

SELECT distinct f from Factories f "
        + "LEFT JOIN FETCH f.machines "
        + "Left JOIN FETCH f.emails "
        + "Left JOIN FETCH f.cities "
        + "where f.id=:id

I tried with distinct and without

My factory with id 1

have 3 machine and 3 emails

Instead of loading factory with 3 machines and 3 emails, I get 9 machines. In the database there are only 3.

It's like a cartesian product is done.

Any idea

Answer 1

You just hit the MultipleBagFetch issue with Hibernate or any JPA. And yes you are right, what are you seeing is the result of Cartesian Product but that's business as usual. There are more posts on SO discussing over the same. To resolve your issue you have 3 options:

a) Split the fetch into two JOIN queries

SELECT distinct f from Factories f "
        + "LEFT JOIN FETCH f.machines "
        + "Left JOIN FETCH f.cities "
        + "where f.id=:id

SELECT distinct f from Factories f "
        + "LEFT JOIN FETCH f.machines "
        + "Left JOIN FETCH f.emails "
        + "where f.id=:id

b) Use Hibernate specific solutions:

@LazyCollection(LazyCollectionOption.FALSE)

OR

@Fetch(value = FetchMode.SUBSELECT)

Reference