Spring JPA adding a collection of entity to many-to-many entity

Question

I need to add multiple entity objects into many-to-many entity.

I have the Component entity:

@Entity
@Table(name = "components", schema = "public", catalog = "inventory_db")
public class Component {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @Column(name = "id", nullable = false)
    private Integer id;

    private Integer qty;

    @OneToMany(mappedBy = "component")
    @ToString.Exclude
    List<PurchaseOrder> purchaseOrders;
}

Also have an User entity:

@Entity
@Table(name = "users")
@Getter
@Setter
@Accessors(chain = true)
@ToString
public class User {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @Column(name = "id", nullable = false)
    private Integer id;

    @OneToMany(mappedBy = "user")
    List<PurchaseOrder> purchaseOrders;
}

And finally I have a PurchaseOrder entity:

@Entity
@Table(name = "purchase_order")
public class PurchaseOrder {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @Column(name = "id", nullable = false)
    private Integer id;

    @Enumerated
    @Column(name = "status")
    private PurchaseOrderStatus purchaseOrderStatus;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "user_id", nullable = false,
            foreignKey = @ForeignKey(name = "FK_PO_USER"))
    private User user;

    //TODO: I need to store here List<Component> - multiple components in one order of the user
    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "component_id", nullable = false,
            foreignKey = @ForeignKey(name = "FK_RENT_BOOK_INFO_BOOK"))
    Component component;

I need an ability to store multiple components (List) in one PurchaseOrder for the User. How can I solve this via Spring JPA ?

Answer 1

You could drop the @JoinColumn annotation, and simply change the relationship to @ManyToMany in both entities. Then in PurchaseOrder wrap component field with List. JPA should then automatically create a table named purchase_order_component that will save ids of both entities.

PurchaseOrder should be something like:

@Entity
@Table(name = "purchase_order")
public class PurchaseOrder {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
@Column(name = "id", nullable = false)
private Integer id;

@Enumerated
@Column(name = "status")
private PurchaseOrderStatus purchaseOrderStatus;

@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
@JoinColumn(name = "user_id", nullable = false, foreignKey = 
@ForeignKey(name = "FK_PO_USER"))
private User user;

@ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
List<Component> component;
}

And Component:

@Entity
@Table(name = "components", schema = "public")
public class Component {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
@Column(name = "id", nullable = false)
private Integer id;

private Integer qty;

@ManyToMany(mappedBy = "component")
@ToString.Exclude
List<PurchaseOrder> purchaseOrders;
}

You could get spring to generate the database schema for you adding this property:

spring.jpa.hibernate.ddl-auto=update

Or here is the SQL equivalent:

CREATE TABLE components (
    id serial4 NOT NULL,
    qty int4 NULL,
    CONSTRAINT components_pkey PRIMARY KEY (id)
);

CREATE TABLE users (
    id serial4 NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

CREATE TABLE purchase_order (
    id serial4 NOT NULL,
    user_id int4 NOT NULL,
    CONSTRAINT purchase_order_pkey PRIMARY KEY (id),
    CONSTRAINT fk_po_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE purchase_order_component (
    purchase_orders_id int4 NOT NULL,
    component_id int4 NOT NULL,
    CONSTRAINT fk_po FOREIGN KEY (purchase_orders_id) REFERENCES purchase_order(id),
    CONSTRAINT fk_component FOREIGN KEY (component_id) REFERENCES components(id)
);