Hi there!
In my kotlin SpringBoot application, I am using an Hibernate Core dependency to handle the persistence for my PostgreSQL db and I am experiencing a weird issue trying to remove an occurrence inside a one-to-many relationship.
Let me explain better…
I have two entities Batch and Order, linked by an one-to-many relationship (so one Batch consists of a list of placed orders, and an Order can belong to a single Batch).
This is how they are built:
@Entity
@Table(name = "batches")
data class Batch(
@Id
@GeneratedValue
val id: UUID? = null,
@LazyCollection(LazyCollectionOption.FALSE)
@OneToMany(cascade = [CascadeType.MERGE], orphanRemoval = true)
@JoinColumn(name = "batchId", nullable = false)
var orders: List<Order>,
...
)
@Entity
@Table(name = "orders")
data class Order(
@Id
@GeneratedValue
val id: UUID? = null,
@Column(insertable = false, updatable = false)
var batchId: UUID? = null,
...
)
This is the SQL script:
CREATE TABLE batches (
id UUID NOT NULL PRIMARY KEY,
...
);
CREATE TABLE orders (
id UUID NOT NULL PRIMARY KEY,
batch_id UUID REFERENCES batches (id),
...
);
CREATE INDEX idx_orders_batch_id ON orders(batch_id);
And the following is the pseudo-code to do that:
fun shortenOrdersList(batch: Batch, ordersToBeRemoved: List<Order>): Batch {
// unlinking orders from batch
existingBatch.orders = existingBatch.orders - ordersToBeRemoved
// updating the batch
val updatedBatch = batchRepository.save(existingBatch)
return updatedBatch
}
Now the issue…
Imagine having a batch B that is composed of 5 orders and then we want to remove an order X from this list, but keeping the order row in its table.
So, what I expect is that:
- the batch’s orders list is shortened and it’s 4 orders long;
- the order X is not related to the batch B anymore but it continues to exist in the Order table with field batchId as null.
Currently, the output of the shortenOrdersList function is a batch with the correct shortened order’s list (so, a list of 4 orders, in our example), but here comes the issue because when I try to fetch again the batch from the db…
val fetchedBatch = batchRepository.findById(batchId)
…I realize that the change has not been reflected into the db (so, in our example, the batch B still has a list of 5 orders and the order X still has the related batchId valued).
I tried to add a more step to the above function: updating each order to be removed with batchId=null but nothing.
The only solution I found is: for each order that needs to be unlinked from the batch, deleting it and storing it again with batchId=null. This works but seems to be an ugly workaround (old order id is lost, new id is created, etc).
Another workaround could be: creating a separate table, made of two columns (batch_id and order_id), where the relationship between batches and orders are handled by myself.
Is this something that any of you has already seen before? It seems very weird that Hibernate cannot allow to shorten a one-to-many relationship, maybe am I doing something wrong?
Thank you all!