How is the correct selection of data from related database tables when using JPA?

Question

Java

Good afternoon!
Please help me understand how to properly build the architecture of the project and work with the database.
I explain the essence of the problem.
There is an application written in Java using Spring, Hibernate.
Let's say there is a database for a school, consisting of several tables interconnected:

//пользователи(преподаватели+директора)
CREATE TABLE appuser (
  appuser_id serial NOT NULL,
  fio VARCHAR(50) UNIQUE,
  PRIMARY KEY (appuser_id)
);

CREATE TABLE school (
  school_id serial NOT NULL,
  school_number VARCHAR(10) UNIQUE,
  director INT, /* директор */
  PRIMARY KEY (school_id)
);
ALTER TABLE school ADD CONSTRAINT school_fk0 FOREIGN KEY (director) REFERENCES appuser(appuser_id);

CREATE TABLE subject (
  subject_id serial NOT NULL,
  description VARCHAR(30) UNIQUE,
  PRIMARY KEY (subject_id)
);

//учитель может читать разные предметы в разной школе
CREATE TABLE teacher_school_subject (
  tss_id serial NOT NULL,
  teacher INT,
  school INT,
  subject INT,
  PRIMARY KEY (tss_id)
);
ALTER TABLE teacher_school_subject ADD CONSTRAINT teacher_school_subject_fk0 FOREIGN KEY (teacher) REFERENCES appuser(appuser_id);
ALTER TABLE teacher_school_subject ADD CONSTRAINT teacher_school_subject_fk1 FOREIGN KEY (school) REFERENCES school(school_id);
ALTER TABLE teacher_school_subject ADD CONSTRAINT teacher_school_subject_fk2 FOREIGN KEY (subject) REFERENCES subject(subject_id);

Entities (I give only part of the parameters so as not to clutter up the page):

@Entity  
@Table(name = "teacher_school_subject")
public class TeacherSchoolSubject implements Serializable {
    @JoinColumn(name = "teacher", referencedColumnName = "appuser_id")
    @ManyToOne(fetch = FetchType.EAGER)
    private Appuser teacher;
  
    @JoinColumn(name = "school", referencedColumnName = "school_id")
    @ManyToOne(fetch = FetchType.EAGER)
    private School school;
  
    @JoinColumn(name = "subject", referencedColumnName = "subject_id")
    @ManyToOne(fetch = FetchType.EAGER)
    private Subject subject;
}

@Entity  
@Table(name = "school")
public class School implements Serializable {
    @JoinColumn(name = "director", referencedColumnName = "appuser_id")
    @ManyToOne(fetch = FetchType.EAGER)
    private Appuser director;	
}

public interface TeacherSchoolSubjectRepository 
                extends PagingAndSortingRepository<TeacherSchoolSubject, Integer> {
  @Query(value = "SELECT * FROM teacher_school_subject", nativeQuery = true)
  List<TeacherSchoolSubject> findAllTSS();
}

For example, I want to select all records from teacher_school_subject.
In doing so, I get a list of TeacherSchoolSubject objects.
However, this query invokes many separate lookup queries on each of the tables after the main SELECT * query.
I saw that you can use @SqlResultSetMapping and query like

@Query(value = "SELECT * FROM teacher_school_subject AS tss "
            + "INNER JOIN appuser au ON au.appuser_id=tss.teacher "
            + "INNER JOIN school s ON s.school_id=tss.school "
            + "INNER JOIN subject sub ON sub.subject_id=tss.subject ", nativeQuery = true)
    List<TeacherSchoolSubject> findAllTSSJoin();

Is this the right approach?
Or not to worry about the fact that there are many requests?
How generally it is correct to work to carry out selection of the data so that the connected data from other tables were picked up also?
Is it always worth using FetchType.EAGER in ManyToOne relationships on the Many side?
After all, I actually get the nesting depth

TeacherSchoolSubject.school -> School.director -> Appuser

, but it could be more. And it turns out that when choosing from the top table, Hibernate is forced to make clarifying queries for selecting deeper data for each row.

Answer 1

For convenience, you can use the Criteria API, in fact, you can collect queries quite close to SQL, but by manipulating your entities and their properties, adding arguments and assembly conditions.
- LEFT JOIN: https://www.logicbig.com/tutorials/java-ee-tutoria...
- INNER JOIN: https://www.logicbig.com/tutorials/java-ee-tutoria...
Join the necessary entities, determine how to select certain data. And you can make such selections convenient, through the necessary conditions in your repository.