stumbled over a problem: Within One-to-Many relationship it seems that auto-generated primary key from parent entity is NOT fed to child entity.
I've boiled down production code to following example:
- Entities, One-to-Many relationship of Parent and Child, both with DB-generated primary key fields (Kotlin code):
import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.data.annotation.Relation
import io.micronaut.data.annotation.Relation.Cascade.PERSIST
import io.micronaut.data.annotation.Relation.Kind.ONE_TO_MANY
import io.micronaut.data.model.naming.NamingStrategies
@MappedEntity("Parent", namingStrategy = NamingStrategies.Raw::class)
data class Parent(
@GeneratedValue @field:Id val id: Long?,
val name: String,
@field:Relation(ONE_TO_MANY, mappedBy = "parent_id", cascade = [PERSIST])
val children: List<Child>? = emptyList()
) {
// Copy-constructor, used by ORM:
constructor(
name: String,
children: List<Child>?
) : this(
null,
name,
children
)
}
@MappedEntity("Child", namingStrategy = NamingStrategies.Raw::class)
data class Child(
@GeneratedValue @field:Id val id: Long?,
val parent_id: Long?,
val description: String,
) {
// Copy-constructor, used by ORM:
constructor(
description: String,
) : this(null, null, description)
}
- Repository code to persist Parent entities (Kotlin):
import io.micronaut.data.annotation.Repository
import io.micronaut.data.jdbc.annotation.JdbcRepository
import io.micronaut.data.model.query.builder.sql.Dialect.H2
import io.micronaut.data.repository.GenericRepository
import javax.transaction.Transactional
@Repository
@JdbcRepository(dialect = H2)
interface ParentRepository : GenericRepository<Parent, Long> {
@Transactional
fun save(parent: Parent): Parent
}
- Now the test case. It defines DB schema and creates it with in-memory H2 database. It tries to persist a single Parent instance with one Child (written with Spock/Groovy):
import groovy.sql.Sql
import io.micronaut.test.extensions.spock.annotation.MicronautTest
import jakarta.inject.Inject
import spock.lang.Specification
import javax.sql.DataSource
@MicronautTest
class ParentRepositorySpec extends Specification {
@Inject
DataSource dataSource
@Inject
ParentRepository parentRepository
def 'Inserting parent with single child'() {
given:
loadDatabaseSchemaTo(dataSource)
def child = new Child('Single child')
def parent = new Parent('Parent', List.of(child))
when:
def savedParent = parentRepository.save(parent)
then:
savedParent.id != null // Primary Key field gets populated
savedParent.items[0].id != null // Same with primary key field of child item
}
static void loadDatabaseSchemaTo(DataSource dataSource) {
def createTableParent = '''
CREATE TABLE `Parent` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)'''
def createTableChild = '''
CREATE TABLE `Child` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`parent_id` int(5) NOT NULL, -- <= Mandatory foreign key
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`parent_id`) REFERENCES `Parent` (`id`)
)'''
new Sql(dataSource).with { sql ->
sql.execute(createTableParent)
sql.execute(createTableChild)
}
}
}
Child tables foreign key field "parent_id" is defined as MANDATORY: No Child entity without Parent.
But executing test case throws:
io.micronaut.data.exceptions.DataAccessException: SQL Error executing INSERT: SQL error executing INSERT: NULL not allowed for column "PARENT_ID"; SQL statement:
INSERT INTO `Child` (`parent_id`,`description`) VALUES (?,?) [23502-200]
Logging shows that INSERT to table "Parent" is executed followed by "Batch SQL Insert" to table "Child" ... which breaks.
So to me it seems that auto-generated primary key from Parent entity is NOT being propagated to Child entity's field "parent_id" prior to issuing the INSERT statements targeting table "child".
What is missing in my code?
