Wrong and strange SQL when persisting an entity with composite id in Spring JPA

Question

I have an entity with composite id:

@Entity
@Table(name = "fs_metadata")
@IdClass(StoreFileMetadataId::class)
open class StoreFileMetadata(
    @Id
    @Column(name = "file_id", nullable = false)
    open var fileId: Long,

    @Id
    @Column(name = "key", nullable = false)
    open var key: String
) {

    @Column(name = "val_string")
    open var string: String? = null

    @Column(name = "val_int")
    open var number: Long? = null

    @Column(name = "val_boolean")
    open var boolean: Boolean? = null

    @Column(name = "val_date")
    open var date: LocalDateTime? = null
}

where id class is:

open class StoreFileMetadataId(var fileId: Long = 0L, var key: String = "") : Serializable {

    override fun toString() = "${fileId}.$key"
    override fun hashCode(): Int = Objects.hash(fileId, key)
    override fun equals(other: Any?): Boolean {
        if (this === other) return true
        if (other == null || Hibernate.getClass(this) != Hibernate.getClass(other)) return false

        other as StoreFileMetadataId

        return fileId == other.fileId &&
                key == other.key
    }

    companion object {
        private const val serialVersionUID = 5029017897184733803L
    }
}

I use StoreFileMetadata in entity StoreFile:

@Entity
@Table(name = "fs_file")
open class StoreFile {
    @Id
    @Column(name = "id", nullable = false)
    open var id: Long? = null

    // ...other fields

    @OneToMany(cascade = [CascadeType.ALL], orphanRemoval = true, fetch = FetchType.LAZY)
    @JoinColumn(name = "file_id")
    open var metadata: MutableSet<StoreFileMetadata> = mutableSetOf()
}

I create a new instance of StoreFile, add some metadata to it and then try to persist it using the StoreFileRepository.save():

@Repository
interface StoreFileRepository: CrudRepository<StoreFile, Long> 

I get a strange error:

could not execute statement [No value specified for parameter 2.] [update fs_metadata set file_id=null where file_id=? and file_id=? and key=?]

Here this sql is strange as (1) it compares file_id twice, (2) it tries to null the file_id, (3) it has no meaning as orphanRemoval is true and anyway I don't delete.

Some notes:

• I also tried to use the @EmbeddedId approach and got the same error.
• I try to avoid using a meaningless sequence-based, simple id, as this table will be huge and it would need to one more index been managed which would never been used.

What do I do wrong?

Answer 1

I don't exactly know why the above doesn't work, but I figured out how to fix.

Originally I used @JoinColumn to refer the StoreFileMetadata entity from StoreFile. It caused the above mentioned error.

First I changed the reference to:

open class StoreFile {
  // ...other fields

  // Still not good as file_id is not refering to an StoreFile entity
  @OneToMany(cascade = [CascadeType.ALL], orphanRemoval = true, fetch = FetchType.LAZY)
    @JoinColumn(columnDefinition = "file_id", name = "file_id")
    open var metadata: MutableSet<StoreFileMetadata> = mutableSetOf()
}

So I had to make the back mapping in StoreFileMetadata as well to be able to refer to:

open class StoreFileMetadata {
    // ...other fields
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "file_id", insertable = false, updatable = false)
    open var storeFile: StoreFile? = null
}

It is still a lazy and read only reference, but it helps Hibernate to create the correct SQL. Now I was able to refer to it in StoreFile

open class StoreFile {
  // ...other fields

  @OneToMany(mappedBy = "storeFile", cascade = [CascadeType.ALL], orphanRemoval = true, fetch = FetchType.LAZY)
    open var metadata: MutableSet<StoreFileMetadata> = mutableSetOf()
}

And this allows to persist the StoreFile cascading the metadata as well.