How to map to an entity which is not directly related

Question

I am using spring-data-jdbc and I have following tables -

table course_details {course_id, course_name}

Example - {1, 'Primary Education'}, {2, 'Secondary Education'}

table course_year {year_id, year_name, course_id}

Example - {1, 'Year 1', 1}, {2, 'Year 11', 2}

table subject {subject_id, subject_name}

Example - {1, 'Mathematics'}, {2, 'Political Science'}

table topic {topic_id, topic_name, related_subject_id, related_year_id}

Example - {1, 'Adding 2 numbers', 1, 1}, {2, 'South Asian Politics', 2, 2}

Entity classes, corresponding to these tables are -

class CourseDetail {
    int id;
    String name;
    List<CourseYear> years;
}

class CourseYear {
    int id;
    String name;
    List<Subjects> subjects; --> No relation to subject, still need this
}

class Subject {
    int id;
    String name;
    List<Topics> topics;
}

and

class Topic {
    int id;
    String name;
    int relatedSubjectId;
}

Now question here is -

How to map subjects thing in CourseYear class, so that I can get a courseGroupById(say 2) and I then get this info -

Course id = 2 (CourseDetail.getId())
Course name = Secondary Education (CourseDetail.getname())
Year = 11 year (CourseDetail.getYears().get(0).getName())
Subject name = Political Science (CourseDetail.getYears().get(0).getSubjects().get(0).getName())
Topic name = South Asian Politics (CourseDetail.getYears().get(0).getSubjects().get(0).getTopics().get(0).getName())

Answer 1

There is no direct way to map something like this in Spring Data JDBC. A very fundamental idea of Spring Data JDBC is to map aggregates to trees of relational tables. Your scenario does not describe this situation at all.

You should not have subjects as an attribute in CourseYear. Instead you should have a method in the SubjectRepository giving you all the Subject instances for a given CourseYear

If your model is intended to be read only you could alternatively create a view in the database to map CourseYear to which contains the Subject information.

Another alternative is to make the attribute transient and load it in an AfterConvertCallback or AfterConvertEvent