Eliminating extra join in JPA query by just using the FK column instead of the related entity's ID

Question

I have an entity relationship such that:

STUDENT many-to-one STUDENT_COURSE one-to-many COURSE

Basically, there's a many-to-many relationship between students and their courses. That relationship is represented by the STUDENT_COURSE table.

Assume I have entities set up for STUDENT, STUDENT_COURSE, and COURSE such that:

@Entity
@Table(name = "STUDENT")
public course Student {
    @Id
    @Column(name = "ID", nullable = false)
    private Long id;

    @OneToMany(mappedBy = "student")
    private Set<StudentCourse> studentCoursees;

    // ... other fields and getters and setters
}

@Entity
@Table(name = "COURSE")
public course Course {
    @Id
    @Column(name = "ID", nullable = false)
    private Long id;

    @OneToMany(mappedBy = "course")
    private Set<StudentCourse> studentCourses;

    // ... other fields and getters and setters
}

@Entity
@Table(name = "STUDENT_COURSE")
public course StudentCourse {
    @Id
    @Column(name = "ID", nullable = false)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "STUDENT_ID", referencedColumnName = "ID")
    @NotNull
    private Student student;

    @ManyToOne
    @JoinColumn(name = "COURSE_ID", referencedColumnName = "ID")
    @NotNull
    private Course course;

    // ... other fields and getters and setters
}

Then I have a complicated criteria query I'm creating for search purposes that wants all of the students for a particular course. I have the courseId that I want to add to the restriction. In SQL, I'd do something like this:

select *
from STUDENT, STUDENT_COURSE
where STUDENT.ID = STUDENT_COURSE.STUDENT_ID
and STUDENT_COURSE.COURSE_ID = <courseId>

Notice that I'm only joining STUDENT and STUDENT_COURSE. With criteria and the entities set up as described above, it seems like I'm forced to join STUDENT, STUDENT_COURSE, and COURSE because I don't have a courseId field on STUDENT_COURSE:

Join<Person, PersonCourse> personCourse = root.join("personCourses");
Join<PersonCourse, Course> course = personCourse.join("course");
Predicate onlySpecificCourse = builder.equal(course.get("id"), courseId);

Is this just something where I should have BOTH the @ManyToOne field from StudentCourse to Course AND the courseId field on StudentCourse? It looks like I can do this if I declare the courseId field as:

@Column(name = "USER_ID", insertable = false, updatable = false)
private String userId;

And then the joining becomes:

Join<Person, PersonCourse> personCourse = root.join("personCourses");
Predicate onlySpecificCourse = builder.equal(personCourse.get("courseId"), courseId);

If I do this, are there any gotchas that I should watch out for? In particular, it seems strange to have setters for both courseId and course on the PersonCourse entity.

Answer 1

---

Update

I am updating my answer to offer you a solution, even though I don't like it. :-)

But first, it sounds like you wish to do this in a OOP way, but you don't want to think of the persisted data as an Object Tree, in that Person, PersonCourse and Course are all part of the same object tree, yet for some special cases, you would like to forget that fact. You can only push ORM up to a certain point, after which you will have to fall back on a native SQL.

However, I will offer an ORM solution here which you may not like, so here it goes:

Add a new attribute to PersonCourse entity and map it to the COURSE_ID column in the join table. But you have to ensure that new attribute is not used in inserts and updates.

@Column(name = "COURSE_ID", insertable = false, updatable = false)
private Long courseId;

And now you can just remove the Course Root from the equation and just use the Predicate that you showed above.

---

Original answer

If STUDENT_CLASS table has no other columns besides the IDs for STUDENT and CLASS relations, then just use @ManyToMany between Student and Class entities, instead of @ManyToOne, and you don't need a third entity; Hibernate will take care of it for you.

If the join table does have other columns, for example GRADE or RATING columns, then use a solution like the one described here: Mapping many-to-many association table with extra column(s).