Mapping recursive relation via secondary table with Spring Data

Viewed 79

I have database such as this:

CREATE TABLE unit
(
    id INTEGER NOT NULL,
    name VARCHAR,
);

CREATE TABLE unit_composition
(
    parent_id INTEGER NOT NULL,
    child_id INTEGER NOT NULL,
    quantity INTEGER,
CONSTRAINT child_fk FOREIGN KEY (parent_id)
        REFERENCES public.refdse (id) MATCH SIMPLE,
CONSTRAINT parent_fk FOREIGN KEY (parent_id)
        REFERENCES public.refdse (id) MATCH SIMPLE
);

ALTER TABLE unit_composition
    ADD CONSTRAINT composit_pk PRIMARY KEY (parent_id, art_nr);

I have a table of manufactory units. Each unit can have multiple sub-units, and sub-units can have multiple sub-sub-units and so on. Also I have a quantity field that shows how many sub-units are needed to manufactor a single unit. So it is kind of a tree relation.

Now I want to map it to classes with Spring Data. I have a Unit class with an Id and Name:

@Entity
@Table(name = "unit")
class Unit {

    @Id
    @Column(name = "id")
    private int id;

    @Column(name = "name")
    private String name;
...
}

I've created a secondary class Part:

class Part {
    
    private Unit unit;
    private int quantity;
    ...
}

And I need unit class to have a field like List subUnits.

I tried to do it with a @SecondaryTable and @JoinColumn annotations, but I got an error saying "Relation unit_unit does not exist". Also I tried to make Part an @Entity but it has no Id field. Alternatively I tried to make @Embeddable class PartId and insert an instance into Part class like this:

@Embeddable
public class PartId implements Serializable {
    
    private Unit parentUnit;
    private Unit unit;

I'm getting an error in PartId class saying that "Basic type should not be Persistence Entity" because it's Embeddable and don't have a table assign to it.

So how can I make this work being able to get recursivly all sub-units (with sub-sub-units and so on) of a given Unit? I don't quite get it how can I map an entity that is really just links from table to itself.

1 Answers

So my first solution was to make Jdbc template repository and simply build necessary lists manually via SQL-queries. But I found much better and simplier solution that required adding an id column to unit_composition table and therefore making Part class an @Entity with @ManyToOne relationship to a Unit class. And the rest was simply done by Spring Data.

Related