Hi all,

I'm trying to optimize a query with FETCH joins in order to avoid many selects. I have the following query:

select uepc from UserEnterpriseProfileCourse as uepc JOIN FETCH uepc.uepcPK.uep as uep
JOIN FETCH uep.user as user where uep.company.id = :idCompany
and uepc.uepcPK.course.id = :idCourse

My classes are in the following way:

class UserEnterpriseProfileCourse {
@EmbeddedId
UserEnterpriseProfileCoursePK uepcPK;

/* ... other columns, getters/setters ... */
}

class UserEnterpriseProfileCoursePK implements Serializable {
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "id_user_ent_prof", insertable=true, updatable=true, nullable=false)
private UserEnterpriseProfile uep;

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "id_course", insertable=true, updatable=true, nullable=false)
private Course course;

/* ... other columns, getters/setters ... */
}

class UserEnterpriseProfile implements Serializable {
@Id
@SequenceGenerator(name = "SEQ_USERENTERPRISEPROFILE", sequenceName = "public.user_enterprise_profile_id_user_ent_prof_seq")
@GeneratedValue(strategy = GenerationType.AUTO, generator = "SEQ_USERENTERPRISEPROFILE")
@Column(name = "id_user_ent_prof")
private int id;

@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumn(name = "id_user", insertable = true, updatable = true)
private User user;

@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumn(name = "id_ent", insertable = true, updatable = false)
private Empresa empresa;

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "id_prof", insertable = true, updatable = false)
private Profile profile;

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "id_user_ent_prof", insertable = true, updatable = true)
private Collection<UserEnterpriseProfileCourse> uepcCollection;

/* ... other columns, getters/setters ... */
}

However, the HQL query generates one select for each UserEnterpriseProfile object returned. Does anyone know how I can rewrite it to generate only one query with joins?

Thanks in advance,

Monique Monteiro