Hello,
Apologies for the long question.
One-liner (vague)
How do I configure my entities to share a piece of the composite primary key with all foreign keys?
More Detail
In my application, we segregate users (and their data) in “working sets”.
This means that every entity has a composite key (workingSetId, entityId).
Currently, for foreign keys, the working set id column is duplicated, and this works.
For example:
CREATE TABLE manager(
working_set_id UUID,
entity_id UUID,
....
);
CREATE TABLE department(
working_set_id UUID,
entity_id UUID,
manager_working_set_id UUID,
manager_entity_id UUID,
....
);
I was wondering if there is a way to reuse the same working_set_id column instead of having a manager_working_set_id column.
This is not so much to save data but for the guarantees that come from it:
no cross-working set references shall exist.
The Problem
Unfortunately:
- a column can only be insertable/updatable for one field in the entity;
- on composite foreign keys, either all columns or no column must be insertable/updatable.
The only way I found of resolving this was to create a field managerEntityId which is insertable/updatable and a field manager that is not.
I am writing exploratory tests to verify this behaves as I intended, but I hit a wall: my update to the manager does not persist (see test below).
Q1: Can you help me understand what is happening behind the scenes?
Q2: If I edit the class field so that the setter explicitly updates the managerEntityId field, then the test passes. Is there a way configure the entities such that Hibernate takes care of this?
Q3: Can you show how I could achieve the same on a @ManyToMany field? As in, how can I, in the many-to-many table, re-use the same working_set_id field for both sides (e.g. SQL below)? I tried the same trick, making all join columns not insertable/updatable and both working set id join columns referring to the same name, but I get an error:
...
insert into public.department_employees (department_entity_id, working_set_id, employee_entity_id) values (?, ?, ?)
binding parameter [1] as [OTHER] - [1fdcdcb6-0212-45fc-b82c-6d3939849855]
binding parameter [2] as [OTHER] - [f362045d-18df-4d8f-821b-da0a33f7eeea]
binding parameter [3] as [OTHER] - [2261a579-4ce4-4198-8157-4cd9e3277e75]
binding parameter [4] as [OTHER] - [f362045d-18df-4d8f-821b-da0a33f7eeea]
The column index is out of range: 4, number of columns: 3.
CREATE TABLE employee(
working_set_id UUID,
entity_id UUID,
....
);
CREATE TABLE department_employees(
working_set_id UUID,
department_entity_id UUID,
employee_entity_id UUID,
....
)
As a difficulty bonus, my setup is to have an abstract BaseEntity which stores the ID and is extended by all entity classes.
The Code
This is my test (Kotlin):
@Tag("exploratory-test")
@SpringBootTest(
webEnvironment = SpringBootTest.WebEnvironment.NONE,
classes = [
EmployeeRepository::class,
ManagerRepository::class,
DepartmentRepository::class,
]
)
@EntityScan(basePackages = ["mypackage.exploratory.sharedcolumns"])
@EnableJpaRepositories(basePackages = ["mypackage.exploratory.sharedcolumns"])
@TestPropertySource(properties = ["spring.jpa.hibernate.ddl-auto=create-drop", "spring.flyway.enabled=false"])
@ActiveProfiles(resolver = ProfileResolver::class)
@EnableAutoConfiguration
class HibernateSharedWorkingSetIdColumnTest {
@Autowired
lateinit var employeeRepository: EmployeeRepository
@Autowired
lateinit var managerRepository: ManagerRepository
@Autowired
lateinit var departmentRepository: DepartmentRepository
// THIS TEST PASSES
@Test
fun `class with shared workingSetId updates referenced entity when updating referenced entity id`() {
val workingSetId = randomUUID()
val manager1 = Manager(workingSetId, "manager1")
val manager2 = Manager(workingSetId, "manager2")
val department = Department(workingSetId, "department", managerEntityId = manager1.id!!.entityId)
managerRepository.save(manager1)
managerRepository.save(manager2)
departmentRepository.save(department)
department.managerEntityId = manager2.id!!.entityId
departmentRepository.save(department)
val reFetchedDepartment = departmentRepository.findById(department.id!!).orElseThrow()
assertAll(
{ assertEquals(manager2.id!!.entityId, reFetchedDepartment.managerEntityId) },
{ assertEquals(manager2.id, reFetchedDepartment.manager!!.id) },
)
}
// BOTH ASSERTIONS FAIL WITHOUT THE EXPLICIT SETTER
@Test
fun `class with shared workingSetId updates referenced entity when updating referenced entity`() {
val workingSetId = randomUUID()
val manager1 = Manager(workingSetId, "manager1")
val manager2 = Manager(workingSetId, "manager2")
val department = Department(workingSetId, "department", managerEntityId = manager1.id!!.entityId)
managerRepository.save(manager1)
managerRepository.save(manager2)
departmentRepository.save(department)
department.manager = manager2
departmentRepository.save(department)
val reFetchedDepartment = departmentRepository.findById(department.id!!).orElseThrow()
assertAll(
{ assertEquals(manager2.id!!.entityId, reFetchedDepartment.managerEntityId) },
{ assertEquals(manager2, reFetchedDepartment.manager) },
)
}
}
@Embeddable
data class MyCompositeId @JvmOverloads constructor(
@NotNull
@Column(nullable = false)
@GenericGenerator(name = "uuid2", strategy = "uuid2")
@Type(type = "pg-uuid")
var workingSetId: UUID? = null,
@NotNull
@Column(nullable = false)
@GenericGenerator(name = "uuid2", strategy = "uuid2")
@Type(type = "pg-uuid")
var entityId: UUID? = null
) : Serializable
@MappedSuperclass
abstract class BazeEntity(
@Id
@Access(AccessType.PROPERTY)
var id: MyCompositeId? = null
) : Serializable
@Entity
class Department (
id: MyCompositeId? = null,
@Column(nullable = false)
@field:NotNull
@NotBlank
var code: String? = null,
@Column(name = "manager_entity_id", nullable = false)
var managerEntityId: UUID? = null,
@Column(name = "vice_manager_entity_id")
var viceManagerEntityId: UUID? = null,
@NotNull
@ManyToMany(fetch = FetchType.LAZY, cascade = [CascadeType.REFRESH])
@JoinTable(
name = "department_employees",
joinColumns = [
JoinColumn(name = "department_entity_id", referencedColumnName = "entityId"),
JoinColumn(name = "department_working_set_id", referencedColumnName = "workingSetId")
],
inverseJoinColumns = [
JoinColumn(name = "employee_entity_id", referencedColumnName = "entityId"),
JoinColumn(name = "employee_working_set_id", referencedColumnName = "workingSetId")
]
)
var employees: MutableSet<Employee> = HashSet()
) : BazeEntity(id) {
@JoinColumn(name = "workingSetId", referencedColumnName = "workingSetId", insertable = false, updatable = false)
@JoinColumn(name = "manager_entity_id", referencedColumnName = "entityId", insertable = false, updatable = false)
@ManyToOne(fetch = FetchType.LAZY, cascade = [CascadeType.REFRESH])
var manager: Manager? = null
// Without this, changes are not persisted
set(value) {
field = value
viceManagerEntityId = value?.id?.entityId
}
@JoinColumn(name = "workingSetId", referencedColumnName = "workingSetId", insertable = false, updatable = false)
@JoinColumn(name = "vice_manager_entity_id", referencedColumnName = "entityId", insertable = false, updatable = false)
@ManyToOne(fetch = FetchType.LAZY, cascade = [CascadeType.REFRESH])
var viceManager: Manager? = null
// Without this, changes are not persisted
set(value) {
field = value
viceManagerEntityId = value?.id?.entityId
}
constructor(
workingSetId: UUID? = null,
code: String? = null,
managerEntityId: UUID? = null,
viceManagerEntityId: UUID? = null,
employees: MutableSet<Employee> = HashSet()
) : this(MyCompositeId(workingSetId, randomUUID()), code, managerEntityId, viceManagerEntityId, employees)
}
@Entity
class Manager @JvmOverloads constructor(
id: MyCompositeId? = null,
@Column(nullable = false)
@field:NotNull
@NotBlank
var code: String? = null
) : BazeEntity(id) {
constructor(
workingSetId: UUID? = null,
code: String? = null,
) : this(MyCompositeId(workingSetId, randomUUID()), code)
}
@Entity
class Employee @JvmOverloads constructor(
id: MyCompositeId? = null,
@Column(nullable = false)
@field:NotNull
@NotBlank
var code: String? = null
) : BazeEntity(id) {
constructor(
workingSetId: UUID? = null,
code: String? = null,
) : this(MyCompositeId(workingSetId, randomUUID()), code)
}
@Repository
@Transactional
interface DepartmentRepository : JpaRepository<Department, MyCompositeId>
@Repository
@Transactional
interface EmployeeRepository : JpaRepository<Employee, MyCompositeId>
@Repository
@Transactional
interface ManagerRepository : JpaRepository<Manager, MyCompositeId>
Thanks!
Your help is greatly appreciated.