CODEWITHSUNDEEP
javahibernatejpamany-to-many
Nimrod
Nimrod

Reputation: 1140

How to use Hibernate to combine efficient queries on @ManyToMany association?

I have 2 entities with a ManyToMany association between them - FeedbackApp & FeedbackAppProfile and each of them has a tenant-id FK to Tenant entity.

FeedbackApp entity:

public class FeedbackApp {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
       
    @ManyToOne
    @JoinColumn(name = "tenant_id")
    private Tenant tenant;

    /*
        Marked as the owner side.
     */
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "feedbackApp_profile_mapping",
            joinColumns = @JoinColumn(name = "feedbackApp_id"),
            inverseJoinColumns = @JoinColumn(name = "profile_id"))
    Set<FeedbackProfile> profiles;
}

The FeedbackProfile entity:

public class FeedbackProfile {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "tenant_id")
    private Tenant tenant;

    
    @ManyToMany(mappedBy = "profiles", fetch = FetchType.EAGER)
    Set<FeedbackApp> feedbackApps;
}

The feedbackApp_profile_mapping join table has 2 columns and looks like this: enter image description here

My question: I need to create a query that gets all feedback apps for a specific feedback profile and tenant id. Is it possible to get it with Hibernate/JPA OR I have to manually query my join table?

Upvotes: 1

Views: 151

Answers (2)

Nimrod
Nimrod

Reputation: 1140

Thanks to @GabiM direction, I created a join fetch query which did the job for what I needed:

@Query(value = "SELECT f FROM FeedbackApp f JOIN FETCH f.profiles p WHERE p.id = ?1 AND p.tenant.id = ?2")
    Set<FeedbackApp> getFeedbackAppsByProfileId(long profileId, long tenantId);

Upvotes: 0

wi2ard
wi2ard

Reputation: 1545

Let Jpa worry about the optimal Sql query to generate. Your Jpa/criteria/specification query should be something like

select fp.feedbackApps from FeedbackProfile fp LEFT JOIN FETCH fp.feedbackApps where fp.id=:feedback_profile_id and fp.tenant.id=:tenant_id

Since you are asking about efficiency, better remove fetch = FetchType.EAGER from the two many-to-many mappings and use join fetch or named entity graphs to do the joins only when you need to.

Upvotes: 1

Related Questions