Spring Data JDBC: Required identifier property not found for a MappedCollection

Question

class Game(
    @MappedCollection(idColumn = "game")         <---- unnecessary
    var rules: List<Rule> = emptyList(),
){
    @Id
    var id: Long? = null
}

data class Rule(
    @MappedCollection(idColumn = "rule", keyColumn = "rule_key")<---- unnecessary
    val ruleValues: List<RuleValue>
) 

data class RuleValue(val value: String)

and schema is

create table game
(
    id              serial primary key
);

create table rule
(
    id              serial primary key,
    game            long references game (id),
    game_key        integer
);

create table rule_value
(
    id               serial primary key,
    game             long references game (id),
    game_key         integer,
    rule             long references rule (id),
    rule_key         integer,
    value            varchar(256)
);

In brief I have a Game as the root aggregate. A game has a list (order is important) of Rules and a rule has a list (order is important) of rule values. A rule value is just a string for now but may extend in the future.

I have 2 questions:

1) Required identifier property not found : When I try to save a game I get Caused by: java.lang.IllegalStateException: Required identifier property not found for class com...RuleValue! exception message.

2) java.sql.SQLSyntaxErrorException : Why do I need game and game_key columns in the rule_value table? If I don't put them I get Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'game_key' in 'field list'

I am using Spring Boot version 2.2.5.RELEASE with org.springframework.boot:spring-boot-starter-data-jdbc corresponds to spring-data-jdbc version 1.1.5.RELEASE

---

SOLUTION-1: adding ID field into the class

As @chrylis-onstrike- suggested I added @Id fields in all the data classes

class Game(
    @MappedCollection(idColumn = "game")
    var rules: List<Rule> = emptyList(),
){
    @Id
    var id: Long? = null
}

data class Rule(
    @MappedCollection(idColumn = "rule", keyColumn = "rule_key")
    val ruleValues: List<RuleValue>
) {
    @Id
    var id: Long? = null <---- this is new
}

data class RuleValue(val value: String) {
    @Id
    var id: Long? = null  <---- this is new
}

and schema changed to

create table game
(
    id              serial primary key
);

create table rule
(
    id              serial primary key,
    game            long references game (id),
    game_key        integer
);

create table rule_value
(
    id               serial primary key,
#    game             long references game (id), <---- removed this
#    game_key         integer,                   <---- removed this
    rule             long references rule (id),
    rule_key         integer,
    value            varchar(256)
);

---

SOLUTION-2: adding composite primary keys into the script

class Game(
    @MappedCollection(idColumn = "game")
    var rules: List<Rule> = emptyList(),
){
    @Id
    var id: Long? = null
}

data class Rule(
    @MappedCollection(idColumn = "rule", keyColumn = "rule_key")
    val ruleValues: List<RuleValue>
)

data class RuleValue(val value: String) 

and schema changed to

create table game
(
    id              serial primary key
);

create table rule
(
#    id      serial primary key, <---- removed 
    game     integer,
    game_key integer,
    primary key (game, game_key) <---- added 
);

create table rule_value
(
#    id      serial primary key, <---- removed 
    game     integer, <---- added 
    game_key integer, <---- added 
    rule_key integer,
    value    varchar(2048),
    primary key (game, game_key, rule_key) <---- added 
);

---

Conclusion You need to decide whether your objects are value objects or an entity. A value object should probably not have an id field in the class as it's identity is defined by the values it carries. However you need to identify a row in a database table and relate it with it's owner thus you need to create composite primary keys in value objects if you want to persist them on different tables.

Answer 1

1. Required identifier property not found.

   I agree with @chrylis-onstrike- the error tells you to add a field with @Id annotation. But I also agree with you: this shouldn't be the case. I'd need at least a full stack trace to understand what is going on. A reproducer would be even better. Feel free to create an issue on https://jira.spring.io/browse/DATAJDBC

   Further investigation on the provided reproducer showed that the problem is caused by the id column still being present in the database and being an identity column. Therefore JDBC does return a value after the insert and Spring Data JDBC tries to set that as an id on the entity and then fails to set that id with the exception mentioned in the question.

2. Why do I need game and game_key columns in the rule_value table?

   As long as Rule doesn't have a dedicated id its primary key is the combination of game and game_key therefore the reference from rule_value back consists of these two fields.

   They shouldn't be necessary when Rule has an id.