Home > Software engineering >  Implementing hierarchical DB tables with InheritanceType.JOINED and a `tenant_id` column in every ta
Implementing hierarchical DB tables with InheritanceType.JOINED and a `tenant_id` column in every ta

Time:12-15

I'm trying to implement a hierarchical structure using the InheritanceType.JOINED approach to store data in hierarchical DB tables. One caveat is that in our multi-tenant solution, a tenant_id column needs to be present on every table (for security and legal export reasons), even though this is redundant in some cases. This requirement is fixed.

Issue is that when inserting data, the query Hibernate generates does not fill in the tenant_id on the parent- and childtable, causing a constraint error.

The tables in the DB would look like this: enter image description here

Code for the abstract vehicle entity:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
abstract class Vehicle(
    var tenantId: Int,
    var title: String
)

Car entity:

@Entity
class Car(
    override var tenantId: Int,
    override var title: String,
) : Vehicle(tenantId, title) {

    var numberOfDoors: Int
}

Plane entity:

@Entity
class Plane(
    override var tenantId: Int,
    override var title: String,
) : Vehicle(tenantId, title) {

    var numberOfPropellers: Int
}

When inserting a new Car in the database, Hibernate generates 2 queries, where the tenant_id is only added to the abstract Vehicle table:

insert into vehicle (tenant_id, title) values (?, ?)

insert into car (id, number_of_doors) values (?, ?)

Is there a way to instruct Hibernate to fill in the column on both tables?

One hack I've found is to implement a "second" tenantId variable on the class and specify a column explicitly, as such:

@Column(name = "tenant_id")
private val _tenantId: Int = tenantId

But it's not very clean and a neater solution would be nice.

CodePudding user response:

Specifically in my case where the tenant_id column is a database setting, defining a computed default value on the tenant_id db column also works as a workaround:

current_setting('app.current_tenant', true)::INT
  • Related