problems with relationships on top of composite keys

[Incanus3]

Introduction

I encountered various problems when working with entities and relationships with composite primary (implemented using @IdClass) and foreign (using @JoinColumns) keys. I realize it's usually a good practice to have a separate issue for each problem, but 1) I don't want to create 5 very similar issues and 2) I have a feeling all these problems have some common cause. Of course, if you really want separate issues for these, I can split this up.

Expected behavior

I believe behavior of entities with composite keys should be consistent with the behavior of entities with simple keys in (almost) all respects. These include

• accessing properties of related entities through @JoinColumn(s) relationships - this is the most pressing problem I encountered
• using of "copies" - entity instances created in memory (and not found in db) - to
  • call Database.beanId()
  • call Database.reference()
  • call Database.refresh()
  • call Database.merge()
  • set relationships

Actual behavior

Entities with simple keys:

• accessing properties of related entities returns expected values
• if I have an entity instance that the persistence context knows about (I've called Database.save() on it, or retrieved it using Database.find() or using a query) and then create a copy of it - a new instance of the entity class with all attributes set to the values of the original entity, this copy behaves (in most ways, except for `Database.beanState() result, but that's logical) the same as the original entity:
  • Database.beanId(copy) returns the id
  • Database.reference(beanClass, Database.beanId(copy)) returns a usable reference
  • Database.refresh(copy) and .merge(copy) don't throw any errors
  • if I set a @ManyToOne property of a referencing entity to this copy and then save the entity, it works as expected

Entities with composite keys:

• accessing properties of related entities (including @Id properties) returns incorrect values, calling Database.beanId(related) works correctly though
• if I create a copy of an entity with composite primary key
  • Database.beanId(copy) returns null
  • Database.reference(beanClass, Database.beanId(copy)) fails, because the beanId is null
  • Database.refresh(copy) throws

java.lang.NullPointerException: The id is null
	at io.ebeaninternal.server.querydefn.DefaultOrmQuery.setId(DefaultOrmQuery.java:1777)
	at io.ebeaninternal.server.core.DefaultBeanLoader.refreshBeanInternal(DefaultBeanLoader.java:207)
	at io.ebeaninternal.server.core.DefaultBeanLoader.refresh(DefaultBeanLoader.java:152)
	at io.ebeaninternal.server.core.DefaultServer.refresh(DefaultServer.java:471)

• Database.merge(copy) throws

java.lang.NullPointerException: The id is null
	at io.ebeaninternal.server.querydefn.DefaultOrmQuery.setId(DefaultOrmQuery.java:1777)
	at io.ebeaninternal.server.persist.MergeHandler.fetchOutline(MergeHandler.java:88)
	at io.ebeaninternal.server.persist.MergeHandler.merge(MergeHandler.java:60)
	at io.ebeaninternal.server.persist.DefaultPersister.merge(DefaultPersister.java:349)
	at io.ebeaninternal.server.core.DefaultServer.lambda$merge$0(DefaultServer.java:824)
	at io.ebeaninternal.server.core.DefaultServer.executeInTrans(DefaultServer.java:2089)
	at io.ebeaninternal.server.core.DefaultServer.merge(DefaultServer.java:824)
	at io.ebeaninternal.server.core.DefaultServer.merge(DefaultServer.java:813)

• saving related entity after setting the @ManyToOne property to the copy throws

io.ebean.DataIntegrityException: Error: NULL not allowed for column "CONN_FROM"; SQL statement: insert into sem_connection (conn_id, conn_network_id, conn_type, conn_label, conn_is_instance, conn_from, conn_to) values (?,?,?,?,?,?,?) [23502-214]
	at app//io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:79)
	at app//io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:212)
	at app//io.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:77)
	at app//io.ebeaninternal.server.persist.dml.DmlBeanPersister.insert(DmlBeanPersister.java:46)
	at app//io.ebeaninternal.server.core.PersistRequestBean.executeInsert(PersistRequestBean.java:1200)
	at app//io.ebeaninternal.server.core.PersistRequestBean.executeNow(PersistRequestBean.java:726)
	at app//io.ebeaninternal.server.core.PersistRequestBean.executeNoBatch(PersistRequestBean.java:770)
	at app//io.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:761)
	at app//io.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:468)
	at app//io.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:418)
	at app//io.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:402)
	at app//io.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1587)
	at app//io.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1579)

Steps to reproduce

// the "primary" entity

@Embeddable
@Suppress("PropertyName")
data class DbConceptId(val conc_id: String = "", val conc_network_id: String = "") : Serializable

@Entity
@DbName(SEMANTIC_DB_NAME)
@Table(name = "sem_concept")
@IdClass(DbConceptId::class)
class DbConcept(
    @Id
    @Column(name = "conc_id", nullable = false)
    override var id: String = UUID.randomUUID().toString(),

    @Id
    @Column(name = "conc_network_id", nullable = false)
    var networkId: String = UUID.randomUUID().toString(),

    // ...
) {
    // @JsonIgnore
    // @OneToMany(mappedBy = "from", fetch = FetchType.LAZY, cascade = [CascadeType.ALL])
    // var outgoingConnections: Set<DbConnection> = emptySet()
    //
    // @JsonIgnore
    // @OneToMany(mappedBy = "to", fetch = FetchType.LAZY, cascade = [CascadeType.ALL])
    // var incomingConnections: Set<DbConnection> = emptySet()

    override fun equals(other: Any?) =
        other is DbConcept && other.id == id && other.networkId == networkId

    override fun hashCode() = 31 * id.hashCode() + networkId.hashCode()
}

// the referencing entity

@Embeddable
@Suppress("PropertyName")
data class DbConnectionId(val conn_id: String = "", val conn_network_id: String = "") : Serializable

@Entity
@DbName(SEMANTIC_DB_NAME)
@Table(name = "sem_connection")
@IdClass(DbConnectionId::class)
class DbConnection(
    @Id
    @Column(name = "conn_id", nullable = false)
    override val id: String = UUID.randomUUID().toString(),

    @Id
    @Column(name = "conn_network_id", nullable = false)
    val networkId: String = UUID.randomUUID().toString(),

    // ...
) {
    @JsonIgnore
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumns(
        JoinColumn(name = "conn_from", referencedColumnName = "conc_id", nullable = false),
        JoinColumn(
            name = "conn_network_id", referencedColumnName = "conc_network_id",
            nullable = false, insertable = false, updatable = false,
        ),
    )
    override var from: DbConcept = DbConcept()

    @JsonIgnore
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumns(
        JoinColumn(name = "conn_to", referencedColumnName = "conc_id", nullable = false),
        JoinColumn(
            name = "conn_network_id", referencedColumnName = "conc_network_id",
            nullable = false, insertable = false, updatable = false,
        ),
    )
    override var to: DbConcept = DbConcept()

    override fun equals(other: Any?) =
        other is DbConnection && other.id == id && other.networkId == networkId

    override fun hashCode() = 31 * id.hashCode() + networkId.hashCode()
}

// the test

@SpringBootTest(classes = [JacksonAutoConfiguration::class])
final class CompositeForeignKeyTest(@Autowired objMapper: ObjectMapper) {
    private val dsConfig = DataSourceConfig().apply {
        username = "sa"
        password = "sa"
        url = "jdbc:h2:mem:semanticdb"
        driver = "org.h2.Driver"
    }

    private val databaseConfig = DatabaseConfig().apply {
        name = SEMANTIC_DB_NAME
        objectMapper = objMapper

        isDdlRun = true
        isDdlGenerate = true
        isDefaultServer = false

        dataSourceConfig = dsConfig.apply {
            addProperty("quoteReturningIdentifiers", false)
        }

        addAll(
            listOf(
                DbConceptId::class,
                DbConnectionId::class,

                DbConcept::class,
                DbConnection::class,
            ).map { it.java },
        )
    }

    private lateinit var database: Database

    @BeforeEach
    fun setUp() {
        database = DatabaseFactory.create(databaseConfig)
    }

    @AfterEach
    fun tearDown() {
        database.shutdown()
    }

    @Test
    fun createConnectionWithCompositeForeignKey() {
        val networkId = "test-network"
        val concept1 = DbConcept(networkId = networkId, id = "concept1")
        val concept2 = DbConcept(networkId = networkId, id = "concept2")

        database.saveAll(concept1, concept2)

        // reference to the original entity - this works, BUT
        val reference = database.reference(DbConcept::class.java, database.beanId(concept1))
        println(reference.id) // this is wrong
        println(reference.networkId) // this is wrong
        println(database.beanId(reference)) // this is correct

        val concept1Copy = DbConcept(id = concept1.id, networkId = concept1.networkId)
        val concept2Copy = DbConcept(id = concept2.id, networkId = concept2.networkId)

        println(database.beanId(concept1Copy)) // this returns null
        // so this fails on the requireNonNull` call
        println(database.reference(DbConcept::class.java, database.beanId(concept1Copy)))
        database.refresh(concept1Copy) // this fails with "id is null"
        database.merge(concept1Copy) // this fails with "id is null"

        val dbConnection = DbConnection(
            id = "test-connection", networkId = networkId,
        ).apply {
            // this works, BUT still behaves incorrectly when we later load the connection from db and access related properties
            from = concept1; to = concept2

            // this fails with NULL not allowed for column "CONN_FROM"
            // from = concept1Copy; to = concept2Copy

            // these work, BUT have the same problem with references - they are new objects,
            // with all properties set to defaults, only `beanId()` results are correct

            // from = database.reference(DbConcept::class.java, database.beanId(concept1))
            // to = database.reference(DbConcept::class.java, database.beanId(concept2))

            // from = database.reference(DbConcept::class.java, database.beanId(concept1Copy))
            // to = database.reference(DbConcept::class.java, database.beanId(concept2Copy))
        }

        database.save(dbConnection)

        database.createQuery(DbConnection::class.java).findEach {
            // these are newly generated DbConcept objects, they have nothing in common with concept 1 and 2
            println(it.from)
            println(it.to)

            // these are wrong - REALLY BAD
            println(it.from.id)
            println(it.from.networkId)

            // these are ok - ids are same as set in the original concepts
            println(database.beanId(it.from))
            println(database.beanId(it.to))
        }
    }
}

The DbConcepts returned by the @ManyToOne properties DbConnection.from and .to are newly created objects that are initialized by the DbConcept() default value of the properties. I understand that it would be better not to initialize them like this, but

• in kotlin there's no (usable) way to not initialize them without making the properties nullable, which isn't really an option here, because they are not optional. there's lateinit, but that doesn't really work well in this context
• this works without any problems for entities with simple keys - we're using this everywhere
• the result of calling database.beanId(connection.from) is correct, so it seems the injection is happening to some extent, just not fully

Some more context

• we're using ebean 13.20.1, java 17, kotlin 1.8.20
• as I said in previous two issues, I may definitely be doing something wrong here, but these annotations are not really documented in the context of ebean, I only found a few mentions, mainly here
• I would understand if the behavior I expect here wasn't supported by ebean, but in that case, I think ebean should throw some explicit exceptions about this, especially for the case where I'm not using copies and the related object (DbConnection.from and .to) "pretends" to be there, but it's a completely wrong objects except when you call beanId on it

[Incanus3]

BTW, if you don't want to support the main case here - not using copies, accessing properties on @ManyToOne related entity - could you please tell me if there's some workaround to make this work? I'm currently doing

    @delegate:Transient
    private val db by lazy { db() }

    @JsonIgnore
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumns(
        JoinColumn(name = "conn_from", referencedColumnName = "conc_id", nullable = false),
        JoinColumn(
            name = "conn_network_id", referencedColumnName = "conc_network_id",
            nullable = false, insertable = false, updatable = false,
        ),
    )
    private var _from: DbConcept = DbConcept()
    override var from
        get() = db.find(DbConcept::class.java, db.beanId(_from))!!
        set(value) { _from = value }

which works, but of course introduces an N+1 query problem.