SQL and control over the relationship with link entity

1. Main criterion — SQL

Now we no longer need to prove again that @ManyToMany hides the real relationship row. That is already clear. Here the debate is settled not by the number of annotations, but by which SQL goes to PostgreSQL after flush().

To make the comparison fair, we need the same experiment: one transaction, one scenario (for example, delete one category from a product), a forced flush() — and we look at the SQL trace. At this point Hibernate starts to look like a cash register: you can argue about “what was intended”, but the receipt will show what actually happened.

A small thinking diagram worth keeping in mind:

flowchart TD
    A[Java code: we change a collection] --> B[Hibernate: dirty checking collections]
    B --> C["flush()"]
    C --> D[SQL: INSERT/DELETE/UPDATE]
    D --> E[PostgreSQL: actual write to the table]

A small example we will repeat today (it is not about “how to do it perfectly”, it is about “how to see SQL now”):

import jakarta.persistence.EntityManager;
import org.springframework.transaction.annotation.Transactional;

@Transactional
public void demoFlush(EntityManager entityManager) {
    // ... change relationships/fields in managed entities (inside a transaction)
    // Important: before flush() Hibernate may buffer changes in the persistence context and not send SQL immediately.
    entityManager.flush(); // force SQL to be sent so we can see it in the logs
}

The key idea: if you did not make Hibernate perform flush(), you are sometimes comparing “dreams” instead of reality. And in ORM, dreams are a poor data source.

2. Naive @ManyToMany: deletion and the join table

Let us first look at the scenario “how it would work in a naive model”. We have a Product, it has a list of categories, and we call remove().

A small mapping that looks innocent (and that is exactly why it is dangerous — it looks too convincingly “simple”):

import jakarta.persistence.Entity;
import jakarta.persistence.JoinTable;
import jakarta.persistence.ManyToMany;
import java.util.ArrayList;
import java.util.List;

@Entity
public class Product {

    // Naive many-to-many: the join table exists in the DB,
    // but in the Java model the relationship row has no separate entity/identity.
    @ManyToMany
    @JoinTable(name = "product_category_assignment")
    private List<Category> categories = new ArrayList<>();
}

Below I intentionally use short lab snippets. We assume that Product and Category are already managed in the current transaction: this makes it easier to isolate the SQL profile of the relationship itself without mixing it with entity loading code.

And the service code, which from the outside also looks “correct”:

import jakarta.persistence.EntityManager;
import org.springframework.transaction.annotation.Transactional;

@Transactional
public void removeCategoryNaive(Product product, Category category, EntityManager em) {
    // At the Java level: “we just removed an element from the collection.”
    product.getCategories().remove(category);

    // We force Hibernate to materialize the changes into SQL
    // so we can see the real behavior (instead of guessing).
    em.flush();
}

At the Java level this looks like deleting a single relationship. But for @ManyToMany, Hibernate manages the join table as an internal collection and therefore often chooses a coarse synchronization strategy: delete all rows for the product and insert back the ones that remain.

A typical SQL trace (simplified for readability):

-- Hibernate may do this:
delete from product_category_assignment
where product_id = ?;

insert into product_category_assignment(product_id, category_id)
values (?, ?);

insert into product_category_assignment(product_id, category_id)
values (?, ?);

You deleted one category, but SQL may have rewritten the product’s entire relationship set. Even if Hibernate turns out to be more precise in a specific case, the model problem does not go away: the relationship row still has no dedicated object, its own fields, or a clear lifecycle.

3. Link entity: targeted DELETE

Now let us repeat the same scenario, but in the “adult” model: Product contains not categories, but categoryAssignments, and deleting a relationship means deleting a specific assignment object.

A small mapping on the product side:

import jakarta.persistence.CascadeType;
import jakarta.persistence.Entity;
import jakarta.persistence.OneToMany;
import java.util.ArrayList;
import java.util.List;

@Entity
public class Product {

    // Product controls the lifecycle of assignment objects:
    // - cascade = ALL: assignment changes go along with the product
    // - orphanRemoval = true: removed assignment from the collection -> removed row in the DB
    @OneToMany(mappedBy = "product", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ProductCategoryAssignment> categoryAssignments = new ArrayList<>();
}

And here is the key difference: we have a relationship entity, which means Hibernate sees the table row as an object with its own id.

Below is a shortened version of the same ProductCategoryAssignment. The sortOrder, assignedAt, constraints, and helper methods are still there; for the DELETE itself, we only care about the owning references and the record identity right now.

import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;

@Entity
public class ProductCategoryAssignment {

    // A separate relationship identifier is exactly the “control”:
    // the row in the table becomes a full-fledged object.
    @Id
    @GeneratedValue
    private Long id;

    // The owning references are what matter here;
    // the other link entity fields do not participate in the DELETE itself.
    @ManyToOne(fetch = FetchType.LAZY)
    private Product product;

    @ManyToOne(fetch = FetchType.LAZY)
    private Category category;
}

Now the service code does not “poke at a category collection”; instead, it calls a model method that removes an assignment:

import jakarta.persistence.EntityManager;
import org.springframework.transaction.annotation.Transactional;

@Transactional
public void removeCategoryExplicit(Product product, Long categoryId, EntityManager em) {
    // In the domain model we remove the relationship (assignment), not Category as a reference entity.
    product.unassignCategory(categoryId);

    // Materialize changes immediately to verify the SQL trace in the logs.
    em.flush();
}

What does Hibernate see on flush()? It sees that a managed ProductCategoryAssignment object was in the collection, now it is gone, and orphanRemoval = true means: “delete the orphan from the DB.” And the SQL becomes targeted and predictable.

A simplified SQL trace looks like this:

delete from product_category_assignment
where id = ?;

Sometimes this will be preceded by a SELECT (for example, to initialize the assignments collection if it is lazy and has not been loaded yet). But the key difference still remains: the delete operation does not trigger a rebuild of the entire relationship, and you see exactly the mechanism you expected as an engineer: “delete one relationship row”.

And this is where the sense of control appears. Not “Hibernate somehow deleted it”, but “I understand which entity is deleted and why exactly this SQL appears.”

4. Adding a category: a clear INSERT

Deletion is the most dramatic part, but addition is also very revealing. In @ManyToMany, adding a category is just “insert a pair of FK values into the join table.” That is all. You have no relationship data, and you cannot add it (unless you start using complicated tricks that in most projects end in sadness and technical debt).

In link entity, adding a category is creating an assignment entity. And that looks like “more code”, but in fact it is more meaning.

A small helper method example on the product side (short, without all the surrounding boilerplate):

import java.time.Instant;

public void assignCategory(Category category, int sortOrder) {
    // Create a relationship object (assignment) that can store relationship attributes:
    // order, assignment date, etc.
    ProductCategoryAssignment a =
            new ProductCategoryAssignment(this, category, sortOrder, Instant.now());

    // Keep the graph consistent in memory:
    // add the assignment both to the product collection and (if needed) to the category collection.
    categoryAssignments.add(a);
    category.addAssignment(a);
}

What does this give us in SQL? Now the INSERT into the relationship table contains not only product_id and category_id, but also relationship fields:

insert into product_category_assignment
(id, product_id, category_id, sort_order, assigned_at)
values (?, ?, ?, ?, ?);

Yes, the SQL got “wider”. But there is nothing wrong with that. On the contrary: now the SQL honestly reflects the model, and the model honestly reflects the domain. And you gain the ability to change sortOrder without circus tricks and to store assignedAt where it logically belongs.

Put very simply: before, you told the database “these two are now together”, and now you say “these two are together, and here is when it started, and what order they are in the list.” That is no longer just a “relationship”; it is part of the data.

5. Updating relationship data: a normal UPDATE

This is where link entity starts to look especially nice. In @ManyToMany you cannot “change the category order for a product”, because order is not a property of Category and not a property of Product. It is a property of the pair Product + Category. And there is no pair in the model — which means there is nothing to change.

In link entity everything is straightforward: sortOrder is a field of the assignment entity. We change it like a normal managed entity field, and dirty checking does the rest.

Small example:

public void changeSortOrder(int newSortOrder) {
    // Simple guard for relationship invariants: negative order is forbidden.
    // This is convenient precisely because the relationship has become an object with behavior.
    if (newSortOrder < 0) {
        throw new IllegalArgumentException("sortOrder must be >= 0");
    }

    // Ordinary managed-entity field update:
    // on flush() Hibernate will perform an UPDATE on one row.
    this.sortOrder = newSortOrder;
}

If ProductCategoryAssignment is in the persistence context (and it will be there when you load the product along with its assignments inside a transaction), then on flush() you will see exactly one update statement:

update product_category_assignment
set sort_order = ?
where id = ?;

Notice the engineering beauty of the moment. We are not updating “the entire collection”. We are not deleting and reinserting everything. We are not doing anything like “rebuild the whole join table somehow”. We are doing exactly what we wanted to do: changing a field in one relationship row.

To capture the difference “on paper”, here is a compact comparison table (this is not about performance benchmarks, but about predictability):

And yes, this is one of those cases where “more objects in Java” gives fewer surprises in SQL.

6. “Relationship control” in practice

When we say “relationship control”, it is easy to slip into abstract words. So let us be concrete: what exactly do we get after moving to link entity if we look not at architectural beauty, but at everyday development and debugging.

First, the relationship row gets entity identity. It has an id, it can be logged, you can set a breakpoint on it, and you can delete it as an object. This sharply reduces the feeling that the join table is “ORM black magic”. It is no longer magic: it is an ordinary table of an ordinary entity.

Second, cascade and orphanRemoval become semantically honest. We can say: “Product controls the lifecycle of assignment objects”, and enable cascade = ALL and orphanRemoval = true only for assignments, not for Category. This is an important detail: a category is a shared reference entity, and the product should not “destroy” it when the product removes the relationship. And with link entity it becomes almost impossible to mix that up, because it is the assignment that gets deleted.

Third, invariants such as “the unique pair product + category” become easier to support both at the model level and at the schema level. In Java you can add a guard (do not create duplicates), and in PostgreSQL you can add a unique constraint so the database does not allow accidental garbage to be written. And this is no longer “theory”, but practical protection against bugs in service code.

For example, a table annotation (shown very briefly, just the idea, not the full class):

import jakarta.persistence.Table;
import jakarta.persistence.UniqueConstraint;

@Table(
    name = "product_category_assignment",
    // Uniqueness of the FK pair protects against duplicates at the DB level:
    // the same category must not be assigned to the same product twice.
    uniqueConstraints = @UniqueConstraint(
        name = "uk_product_category",
        columnNames = {"product_id", "category_id"}
    )
)
public class ProductCategoryAssignment { }

And finally, link entity makes the SQL trace more “readable” for a human. When you see delete from product_category_assignment where id = ?, you understand that the relationship itself is being deleted. When you see update ... set sort_order, you understand that the order itself is being updated. That sounds banal, but in real projects “banally clear” is a competitive advantage.

7. SQL profiles in Commerce Persistence Lab

To avoid leaving everything at the level of “somewhere in the logs it will look nice”, let us imagine how we usually inspect behavior in our lab project. We start the application with SQL trace enabled (you have already done this in the early days of the course), execute one service method, and inside the transaction call flush() so we do not have to guess when Hibernate decides to send SQL.

To focus on SQL, this service snippet is also intentionally short: we assume that Product is already managed, and the code for loading by id does not affect the actual relationship deletion profile.

An example of a catalog service in the style of our project (minimal):

import jakarta.persistence.EntityManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ProductCategoryService {

    private final EntityManager entityManager;

    public ProductCategoryService(EntityManager entityManager) {
        // We need EntityManager here to force flush()
        // and immediately see SQL in the logs.
        this.entityManager = entityManager;
    }

    @Transactional
    public void unassignCategory(Product product, long categoryId) {
        // Lab scenario: Product is already managed, we are interested only in the SQL trace of the relationship deletion.
        product.unassignCategory(categoryId);

        // Forced flush is the key to “control by SQL”.
        entityManager.flush();
    }
}

If the model is still on @ManyToMany, you may see a “coarse” rebuild in the logs. If the model is already on link entity, you will almost certainly see a targeted DELETE by assignment row (sometimes with a preliminary SELECT, if the collection is not loaded yet).

And this is where a practical criterion for success in the day 12 refactoring emerges: your code should lead to SQL that says the same thing as your business meaning. “Remove a category from a product” should look like “delete the assignment record”, not like “rebuild all of the product’s assignments from scratch”.

Yes, Hibernate is not required to be “maximally economical” in every edge case. But after link entity you get the ability to make the behavior predictable. And predictability is the foundation for any next step: whether it is constraints, ordering, change history, or simply a developer’s peaceful sleep.

8. Common mistakes when working with link entity

Mistake #1: comparing solutions by the number of annotations rather than by SQL.
This is the most common “engineering illusion”: it seems that a concise mapping automatically means “it just works”. In practice @ManyToMany often means Hibernate will do SQL in a way that is convenient for collection management, not in a way that is convenient for relationship control. The right habit is: after any relationship operation, call flush() in the lab and inspect the generated SQL.

Mistake #2: after moving to link entity, leaving the old categories collection next to categoryAssignments.
This creates two sources of truth. In application memory, you may accidentally update categories, while in the DB you will expect assignments to be updated (or vice versa). As a result, you get an unsynchronized graph and very strange SQL. In a project like this, the relationship must have one control path: either the naive @ManyToMany (before refactoring) or link entity (after).

Mistake #3: deleting Category when you meant to delete the assignment.
When Product, Category, and ProductCategoryAssignment appear in the domain, the mind sometimes gets confused: “well, I am deleting the category from the product... so I am deleting the category.” No. The category is a reference entity, a shared entity. The relationship record is deleted. If you call categoryRepository.delete(...) instead of deleting the assignment from the collection, you change the meaning of the operation and risk losing reference data.

Mistake #4: no orphanRemoval, so the relationship “disappeared in memory” but remained in the DB.
If you removed an assignment from the Product.categoryAssignments collection but did not configure orphanRemoval = true, Hibernate is not required to delete the row from the relationship table. It will see that the object is no longer associated with the parent, but it will not receive the command “this is an orphan, delete it.” As a result, you will get a mismatch between the model in memory and the data in the product_category_assignment table.

Mistake #5: updating only one side of a bidirectional relationship.
When you create or delete an assignment, you need to maintain consistency on both the Product side and the Category side (if the category has an assignments collection for navigation). If you added the assignment only to product.categoryAssignments, but not to category.productAssignments, you already have a “half-synchronized” graph in memory. Hibernate, of course, eventually relies on the owning side in the DB, but you will make your own life harder: debugging, logging, and simple checks in code will start behaving inconsistently.