Context

Our PUT endpoint essentially does an UPSERT by first calling DELETE on all the entities, and then calls INSERT INTO.

However, when debugging, I see that there are some UPDATE calls being issued by JPA too, and I'm trying to avoid that for performance reasons. I want there to be only 2 INSERT calls: one for the Parent, and one for all the Child.

This seems to only happen for the @OneToMany entities.

If it can help, we use EclipseLink as our JPA provider, and batch-writing is activated. The application is built with Spring Data.

Model

@Entity
@Table
@Data // org.lombok
@IdClass(ParentPrimaryKey.class)
public class Parent {

    @Id
    @Column(name = "product", updatable = false)
    private String product;

    @Id
    @Column(name = "source", updatable = false)
    private String source;

    @Column
    private String whatever;


    @OneToMany(cascade = {CascadeType.ALL}, orphanRemoval = true)
    @JoinColumns({
            @JoinColumn(name = "product", referencedColumnName = "product"),
            @JoinColumn(name = "source", referencedColumnName = "source")
    })
    private List<Child> details;
}

@Entity
@Table
@Data // org.lombok
@IdClass(ChildPrimaryKey.class)
public class Child {

    @Id
    @Column(name = "product", updatable = false)
    private String product;

    @Id
    @Column(name = "source", updatable = false)
    private String source;

    @Id
    @Column(name = "extra", updatable = false)
    private String extra;

    @Column
    private String foo;

    @Column
    private String bar;
}

Associated current logs

INSERT INTO PARENT (product, source, whatever) VALUES (?, ?, ?)
    bind => [A, A, A]
    bind => [B, B, A]

INSERT INTO CHILD (product, source, extra, foo, bar) VALUES (?, ?, ?, ?, ?)
    bind => [A, A, 1, B, B]
    bind => [A, A, 2, C, C]
    bind => [A, A, 3, D, D]
    bind => [B, B, 1, B, B]
    bind => [B, B, 2, C, C]
    bind => [B, B, 3, D, D]

UPDATE CHILD SET product = ?, source = ? WHERE ((product = ?) AND (source = ?) AND (extra = ?))
    bind => [A, A, A, A, 1]
    bind => [A, A, A, A, 2]
    bind => [A, A, A, A, 3]
    bind => [B, B, B, B, 1]
    bind => [B, B, B, B, 2]
    bind => [B, B, B, B, 3]

Attempted fix

Based on this answer, I tried adding nullable=false :

public class Child {

    @Id
    @Column(name = "product", updatable = false, nullable = false)
    private String product;

    @Id
    @Column(name = "source", updatable = false, nullable = false)
    private String source;

    @Id
    @Column(name = "extra", updatable = false, nullable = false)
    private String extra;

    // the rest is identical...
}

Resulting logs

As can be seen, this indeed removes the UPDATE call, but it increases the amount of total queries that will be issued to our DB, and thus means the performance will actually be worse (it will go from a total of 3 requests, to 2N):

INSERT INTO PARENT (product, source, whatever) VALUES (?, ?, ?)
    bind => [A, A, A]
INSERT INTO CHILD (product, source, extra, foo, bar) VALUES (?, ?, ?, ?, ?)
    bind => [A, A, 1, B, B]
    bind => [A, A, 2, C, C]
    bind => [A, A, 3, D, D]


INSERT INTO PARENT (product, source, whatever) VALUES (?, ?, ?)
    bind => [B, B, A]
INSERT INTO CHILD (product, source, extra, foo, bar) VALUES (?, ?, ?, ?, ?)
    bind => [B, B, 1, B, B]
    bind => [B, B, 2, C, C]
    bind => [B, B, 3, D, D]

CodePudding user response：

Two options that may work better for you.

First option

Keeping everything else unchanged:

@Entity
@Table
@Data // org.lombok
@IdClass(ParentPrimaryKey.class)
public class Parent {

    @Id
    @Column(name = "product", updatable = false)
    private String product;

    @Id
    @Column(name = "source", updatable = false)
    private String source;

    @Column
    private String whatever;


    @OneToMany(cascade = {CascadeType.ALL}, orphanRemoval = true)
    @JoinColumns({
            @JoinColumn(name = "product", referencedColumnName = "product", insertable = false, updatable = false),
            @JoinColumn(name = "source", referencedColumnName = "source", insertable = false, updatable = false)
    })
    private List<Child> details;
}

This will ensure that the foreign keys in the Child table are controlled only by their respective @Id mappings.

Second option

Alternatively, you can go the other way around and have JPA set the values in the child entity instance entirely for you with what is known as a derived ID:

@Entity
@IdClass(ChildPrimaryKey.class)
public class Child {
    @Id
    @JoinColumns({
            @JoinColumn(name = "product", referencedColumnName = "product"),
            @JoinColumn(name = "source", referencedColumnName = "source")
    })
    Parent parent;

    @Id
    @Column(name = "extra")
    private String extra;

    // the rest is identical...
}

public class ChildPrimaryKey {
    ParentPrimaryKey parent;
    String extra;
}

This will ensure the source and product columns are set in the Child table based on the Parent instance's values. The Parent's details mapping then can remove the join columns definition and just state it is mapped by the Child's parent relationship:

@OneToMany(mappedBy = "parent",cascade = {CascadeType.ALL}, orphanRemoval = true)
private List<Child> details;