Spring Data JDBC Set vs List in One To Many Relationship

Question

My one-to-many relationship works fine as a Set, but if I change to a List then I encounter errors. I'm using an H2 database, but also tried an Oracle DB and saw a similar error.

-- schema.sql
CREATE TABLE JOBS (ID INT AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE JOB_TASKS (JOB INT REFERENCES JOBS(ID));

@Table("JOB_TASKS")
public record Task() {
}

@Table("JOBS")
public record Job(@Id Integer id, @MappedCollection(idColumn = "JOB") Set<Task> tasks) {
}

public interface JobRepository extends ListCrudRepository<Job, Integer> {
}

// Inside the main @SpringBootApplication
@Bean
public CommandLineRunner commandLineRunner(final JobRepository jobRepository) {
    return args -> {
        final Set<Task> tasks = new HashSet<>();
        tasks.add(new Task());
        final var job = new Job(null, tasks);
        jobRepository.save(job);
    };
}

When I change the Set to a List, the insert fails.

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "JOBS_KEY" not found; SQL statement:
INSERT INTO "JOB_TASKS" ("JOB", "JOBS_KEY") VALUES (?, ?) [42122-224]

When I change over to an Oracle DB, I receive a similar error:

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO "JOB_TASKS" ("JOBS_KEY", "JOB") VALUES (?, ?)]
Caused by: java.sql.BatchUpdateException: ORA-00904: "JOBS_KEY": invalid identifier

I tried this with Spring Boot 3.0.2 and 3.3.2.

I noticed that in the @MappedCollection documentation for keyColumn it specifically mentions it's for List or Map collections, so I tried changing to

@Table("JOBS")
public record Job(@Id Integer id, @MappedCollection(idColumn = "JOB", keyColumn = "JOB") List<Task> tasks) {
}

but this resulted in a constraint violation

Caused by: org.h2.jdbc.JdbcBatchUpdateException: Referential integrity constraint violation: "CONSTRAINT_3: PUBLIC.JOB_TASKS FOREIGN KEY(JOB) REFERENCES PUBLIC.JOBS(ID) (0)"; SQL statement:
INSERT INTO "JOB_TASKS" ("JOB") VALUES (?) [23506-224]

Same in Oracle:

Caused by: java.sql.BatchUpdateException: ORA-02291: integrity constraint (...) violated - parent key not found

Removing the db constraint on the H2 side and I do see it inserts a record into JOB_TASKS whose JOB column value is 0 instead of the expected id of 1.

Answer 1

If you use a List for your collection Spring Data JDBC will store the list index. The column for this is by default <table-name-of-the-parent-entity> + "_KEY". So in your case: `JOBS_KEY

The suffix KEY comes from the fact the the list is basically treated as a Map with the index standing in for the key of the map. With @MappedCollection you can control that name and change it to anything you like.

By using @MappedCollection(idColumn = "JOB", keyColumn = "JOB") you are mapping both the back reference to the parent entity and the list index to the same field. I guess it is pretty obvious, that this can't work.

With this explanation, the correct solution should be obvous: Provide a database field for the list index.