Why does Hibernate generate a complex subquery for a bidirectional @OneToMany relationship with @JoinTable?

Question

I am working with Spring Boot and Hibernate, managing entities with a bidirectional @OneToMany relationship joined through an intermediary table. Here are my entity definitions:

@Entity
@Table(name = "feedback")
@NamedEntityGraph(name = "full", includeAllAttributes = true)
@Getter
@Setter
@SequenceGenerator(allocationSize = 1, name = "feedback_seq_gen", sequenceName = "feedback_seq")
public class Feedback {

    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "feedback_seq_gen")
    private Long id;

    @Column(name = "text", length = Integer.MAX_VALUE)
    private String text;

    @OneToMany(mappedBy = "feedback", fetch = FetchType.LAZY)
    private Set<Image> images = new HashSet<>();
}

@Entity
@Table(name = "image")
@Getter
@Setter
@SequenceGenerator(allocationSize = 1, name = "image_seq_gen", sequenceName = "image_seq")
public class Image {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "image_seq_gen")
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "url", length = Integer.MAX_VALUE)
    private String url;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinTable(name = "feedback_image",
            inverseJoinColumns = @JoinColumn(name = "feedback_id"),
            joinColumns = @JoinColumn(name = "image_id"))
    private Feedback feedback;

    public void setFeedback(Feedback feedback) {
        feedback.getImages().add(this);
        this.feedback = feedback;
    }
}

The Feedback and Image entities are related through a @OneToMany relationship using a @JoinTable named feedback_image. The reason I use junction table is because I need to upload images before creating feedback.

Repository:

public interface FeedbackRepository extends JpaRepository<Feedback, Long> {

    @EntityGraph("full")
    @Query("select f from Feedback f where f.id = :id")
    Optional<Feedback> getFullFeedbackById(Long id);
}

When testing the relationship and fetching logic, Hibernate generates a complex SQL query that includes an additional join on a subquery:

SELECT f1_0.id, i1_0.feedback_id, i1_1.id, i1_1.url, f1_0.text
FROM feedback f1_0
         LEFT JOIN feedback_image i1_0 ON f1_0.id = i1_0.feedback_id
         LEFT JOIN (image i1_1 LEFT JOIN feedback_image i1_2 ON i1_1.id = i1_2.image_id) ON i1_1.id = i1_0.image_id
WHERE f1_0.id = ?;

However, I noticed that if I switch the relationship to @ManyToMany, Hibernate generates a simpler, more direct query:

SELECT f1_0.id, i1_0.feedback_id, i1_1.id, i1_1.url, f1_0.text
FROM feedback f1_0
         LEFT JOIN feedback_image i1_0 ON f1_0.id = i1_0.feedback_id
         LEFT JOIN image i1_1 ON i1_1.id = i1_0.image_id
WHERE f1_0.id = ?;

But this approach does not align with my conceptual model.

So, my question is: is the additional join subquery generated by Hibernate for the this setup normal, and if so, why does it occur?

Answer 1

Seems your approach is incorrect.

If you need to map these two entities with bidirectional @OneToMany this is the way.

@Entity
@Table(name = "feedback")
@NamedEntityGraph(name = "full", includeAllAttributes = true)
@Getter
@Setter
@SequenceGenerator(allocationSize = 1, name = "feedback_seq_gen", sequenceName = "feedback_seq")
public class Feedback {

    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "feedback_seq_gen")
    private Long id;

    @Column(name = "text", length = Integer.MAX_VALUE)
    private String text;

    @ManyToOne(mappedBy = "feedback", fetch = FetchType.LAZY)
    @JoinColumn(name="IMAGE_ID")
    private Image image;
}


@Entity
@Table(name = "image")
@Getter
@Setter
@SequenceGenerator(allocationSize = 1, name = "image_seq_gen", sequenceName = "image_seq")
public class Image {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "image_seq_gen")
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "url", length = Integer.MAX_VALUE)
    private String url;

    @OneToMany(fetch = FetchType.LAZY,mappedBy="image",cascade=CascadeType.ALL)
    private List<Feedback> feedback = new ArrayList<>();

    public void setFeedback(Feedback feedback) {
        feedback.getImages().add(this);
        this.feedback = feedback;
    }
}

So, no need to use join table,

If you have @ManyToMany relationship and just use both tables id's only for mapping you can use below

   @JoinTable(name = "feedback_image",
            inverseJoinColumns = @JoinColumn(name = "feedback_id"),
            joinColumns = @JoinColumn(name = "image_id"))

But for more complex implementation with @ManyToMany mapping like additional column also needs to involve then it's required to create separate entity class for it