How would I implement Spring Data JDBC Many to Many between 2 tables if I have a third table that contains a foreign key to my bridge table?

Question

I learned that in Spring Data JDBC I need to implement many to many relationships by having a reference to the ID of one entity in the other entity:

public class Student {
  
  @Id
  private Long studentId;
  private String studentName;

  @MappedCollection(idColumn = "student_id", keyColumn = "course_id")
  private Set<CourseRef> courses; 
}

public class Course {

  @Id
  private Long courseId;
  private String courseName;
}

@Table("student_course")
public class CourseRef {

  @Id
  private Long studentCourseId;
  private Long courseId;

  @MappedCollection(idColumn = "student_course_id", keyColumn = "test_score_id")
  private List<TestScore> testScores;
}

public class TestScore {
   
  @Id
  private Long testScoreId;
  private Integer value;
}

public interface StudentRepository implements CrudRepository<Student, Long> {
}

public interface CourseRepository implements CrudRepository<Course, Long> {
}

public class StudentRepositoryTest {

    @Autowired
    StudentRepository repository;

    @Test
    void testAddTestScore() {
        Student student = repository.findById(1L).get();
        assertNotNull(student);

        Set<CourseRef> courses = student.getCourses();
        CourseRef course = courses.stream().filter(c -> c.getCourseId() == 2).findFirst().orElse(null);
        assertNotNull(course);

        courses.remove(course);
        course.addTestScore(TestScore.create(90);
        courses.add(course);
        students.setCourses(courses);
        repository.save(student);
    }
}

With this setup I have a student table, course table, student_course table, and test_score table that has a foreign key to a student_course id. But I'm having trouble adding a new test score. The repository is unable to save the updated student due to a foreign key constraint failure with the student_course_id column. I was wondering, is it possible to add new test scores with this approach, and if so would I need to create a new repository?

Answer 1

You didn't post your schema so I don't know what exactly went wrong, but I started with your code and created a working example from it: https://github.com/schauder/stackoverflow/tree/main/jdbc/three-way-reference

I took the liberty to simplify your property/column names since repeating the entity/table didn't add value for me.

The Domain Model

I put some hints of DDD on it, but didn't go the full 9 yards. But I wanted at least to give some examples on how the logic for adding a TestScore belongs into the aggregate and not out side in the test or service. But this is not relevant for the mapping.

The important changes I made are:

• I dropped the ids of CourseRef and TestScore. Ids in general are not neccesary on inner entities (not aggregate roots).
• I had to reintroduce it to CourseRef due to a bug I found in the progress.
• I simplified the code for adding a TestScore removing and then adding the course again is not necessary.

public class Course {

    @Id
    Long id;
    String name;

    static Course create(String name) {
        Course course = new Course();
        course.name = name;
        return course;
    }
}

class Student {

        @Id
        final Long id;
        String name;

        @MappedCollection(idColumn = "STUDENT_ID", keyColumn = "COURSE_ID")
        Set<CourseRef> courses = new HashSet<>();

    Student(Long id) {
        this.id = id;
    }

    /**
     * quality of life method to create students by name.
     */
    public static Student create(String name) {
        final Student student = new Student(null);
        student.name = name;
        return student;
    }

    /**
     * The aggregate root should take care of whatever logic is necessary to add a course.
     */
    void addCourse(Course course) {
        final CourseRef ref = new CourseRef();
        ref.courseId = AggregateReference.to(course.id);
        courses.add(ref);
    }

    /**
     * The aggregate root should take care of whatever logic is necessary to add a testscore.
     * @param course
     * @param score
     */
    public void addScore(Course course, int score) {
        courses.stream()
                .filter(c -> c.courseId.getId().equals(course.id))
                .findFirst()
                .orElseThrow()
                .testScores.add(TestScore.create(90));
    }
}

@Table("STUDENT_COURSE")
class CourseRef {

    @Id // work around for issue https://github.com/spring-projects/spring-data-jdbc/issues/1139
    Long Id;

    AggregateReference<Course,Long> courseId;

    @MappedCollection(idColumn = "STUDENT_COURSE_ID", keyColumn = "INDEX")
    List<TestScore> testScores;
}

class TestScore {

    private Integer value;

    public static TestScore create(int value) {
        final TestScore testScore = new TestScore();
        testScore.value = value;
        return testScore;
    }
}

Database Schema

I added one:

CREATE TABLE COURSE
(
    ID   INTEGER IDENTITY PRIMARY KEY,
    NAME VARCHAR(200) NOT NULL
);

CREATE TABLE STUDENT
(
    ID   INTEGER IDENTITY PRIMARY KEY,
    NAME VARCHAR(200) NOT NULL
);

CREATE TABLE STUDENT_COURSE
(
    ID INTEGER IDENTITY PRIMARY KEY,
    STUDENT_ID INTEGER NOT NULL,
    COURSE_ID  INTEGER NOT NULL,
    UNIQUE (STUDENT_ID, COURSE_ID),
    FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(ID),
    FOREIGN KEY (COURSE_ID) REFERENCES COURSE(ID)
);

CREATE TABLE TEST_SCORE
(
    STUDENT_COURSE_ID INTEGER,
    INDEX INTEGER,
    VALUE            INTEGER,
    PRIMARY KEY (STUDENT_COURSE_ID, INDEX),
    FOREIGN KEY (STUDENT_COURSE_ID) REFERENCES STUDENT_COURSE(ID)
);

The Test

@DataJdbcTest
class StudentRepositoryTest {

    @Autowired
    StudentRepository students;

    @Autowired
    CourseRepository courses;

    Student jens = null;

    @BeforeEach
    void setup() {

        Course physics = courses.save(Course.create("Physics"));
        Course math = courses.save(Course.create("Math"));
        Course informatics = courses.save(Course.create("Informatics"));

        jens = Student.create("Jens");

        jens.addCourse(physics);
        jens.addCourse(math);
        jens.addCourse(informatics);

        jens = students.save(jens);

    }

    @Test
    void testAddTestScore() {

        Student student = students.findById(jens.id).orElseThrow();
        assertNotNull(student);
        Course math = courses.findByName("Math");
        assertNotNull(math);

        student.addScore(math, 90);

        students.save(student);
    }

}