Incorrect mapping between Father and Child tables

Question

I'm trying to model a Father/Children relationship in Spring Data JDBC (version 1.1.5.RELEASE). So far i've managed to get the insert operation working, but i'm struck with an error when reading the data.

Entities look like this

@Table("FATHER")
data class FatherDao(
    @Id
    val id: Long?, 
    @MappedCollection(idColumn="father_id")
    val childrens: Set<ChildrenDao>
)

@Table("CHILDREN")
data class ChildrenDao(
    @Id 
    val id: Long?,
    val father: FatherDao?
)

Schema is the following:

CREATE TABLE father (
   id INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1)
   PRIMARY KEY (id)
);

CREATE TABLE children (
    id INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1),
    father_id INT
    PRIMARY KEY (id)
);

For now i've omitted Foreign Key on character_id.

Repository is simply

@Repository
interface FatherRepository : CrudRepository<CharacterDao, Long>

For saving children entites associated to the father i do not call a ChildrenRepository but instead proceed to save the father entity.

The creation goes smoothly, but when tring to get the created entity i recieve the following exception

user lacks privilege or object not found: FATHER.CHILDREN_DAO in statement [SELECT CHILDREN.id AS id, father.id AS father_id FROM CHILDREN LEFT OUTER JOIN FATHER AS father ON father.children_dao = CHILDREN.id WHERE CHILDREN.father_id = ?]

My main problem is that i can't undestand where the FATHER.CHILDREN_DAO came from. I'm fairly sure it's caused by a bad mapping in the FatherDao entity, but even looking in the Spring Data JDBC docs regarding Sets i cannot find a clue.

Answer 1

You seem to try to create a bidirectional relationship between FatherDao and ChildrenDao. This is not supported by Spring Data JDBC. The two connections are considered independent and Spring Data JDBC tries to load the father referenced by a children which according to the mapping rules would require a a CHILDREN_DAO on the father.

There are two variants to solve the problem depending on your domain. The important question is: Are children part of the father aggregate or are they an aggregate on their own?

If children are part of the father aggregate, either remove the father reference or mark it with @Transient. In the later case you might set the value in the constructor of FatherDao or in an AfterLoadCallback

If children are their own aggregate both the childrens and the father reference should be removed and instead an id (or set of entities containing an id) should be used. Instead of the bidirectional relationship you would have query method to load the father of a child or vice versa.

The Article Spring Data JDBC, References, and Aggregates explains the reasoning behind this design decision and also has an example how to implement it.