Spring Boot JPA how to handle child table updates

Question

I've come from a background in my previous role of doing all data persistence with all custom SQL and JDBC. Just started a new job for a small business where I'm the only developer and have decided to develop the businesses systems using Spring Boot with JPA for data persistence.

I don't know how I should be handling updates on child tables. I'm currently using the Spring Crud Repository for basic tables but have got to my first parent child relationship.

Given the below example table structure, how should I be managing updates?

  +-------------------------+
  | Order                   |
  +-------------------------+
  | orderNumber      | PK   |
  +-------------------------+

  +-------------------------+
  | OrderLine               |
  +-------------------------+
  | orderNumber      | PK   |
  +-------------------------+
  | lineNumber       | PK   |
  +-------------------------+

The users could update or delete existing order lines.

When doing an update, I could delete all of the existing orderLines first and recreate them but not sure if this is bad practice?

What is the normal approach with Spring Boot and JPA?

Should I be using some kind of cascade settings on the parent entity?

score 1 · Accepted Answer · answered Apr 10 '20 at 05:37

When doing an update, I could delete all of the existing orderLines first and recreate them but not sure if this is bad practice?

updating only the orderlines that need an update is the best practice.

I could delete all of the existing orderLines first and recreate them

That would cause to make a lot of delete queries and lot of insert queries which is bad. The autogenrated id of orderlines would also increase rapidly which is not what you want.

What is the normal approach with Spring Boot and JPA? Should I be using some kind of cascade settings on the parent entity?

Yes, you should. Here is an example:

Order.java

@Entity
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long orderNumber;

    @Version
    private Long version = 0L;

    @OneToMany(mappedBy="order", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<OrderLine> orderLines;

    // equals() and hashcode() implementation
    // getter() and setter() implementation
}

OrderLine.java

@Entity
public class OrderLine {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long lineNumber;

    @ManyToOne
    @JoinColumn(name="order_id", nullable=false)
    private Order order;

    @Version
    private Long version = 0L;

    // equals() and hashcode() implementation
    // getter() and setter() implementation
}

And finally an update example:

public boolean update(Order order, long orderLineId, OrderLine updatedOrderLine) {
    if(order == null || updatedOrderLine == null)
        return false;

    if(order.getOrderLines() == null)
        return false;

    Optional<OrderLine> optTargetOrderLine = order.getOrderLines().stream()
.filter(orderline -> orderLine.getLineNumber() == orderLineId).findFirst();

    if(!optTargetOrderLine.isPresent())
        return false;

    OrderLine targetOrderLine = optTargetOrderLine.get();

    // now implement the update function
    boolean status = update(targetOrderLine, updatedOrderLine);

    // if status is true, you also have to call save() on your OrderRepository
    return status;
}

My parent table sometime doesn't update:https://stackoverflow.com/questions/73091178/data-inconsistancy-parent-table-is-not-updated-while-child-table-is-updated — Hacke, Jul 26 '22 at 07:19

score 0 · Answer 2 · answered Apr 10 '20 at 04:49

0

Could you replace orderNumber with orderId ? The orderLine table reference to the Order table via foreign key orderId has more meaning for your code. If you could, using join column and cascade config, instead of deleting all orderLine and recreate them, let JPA do it for you.

answered Apr 10 '20 at 04:49

logbasex

1,688
1
16
22

The field names don't really matter this was just an example table I made up. But should have mentioned that yes orderLine.OrderNumber is a foreign key which I just assumed was implied by them having the same field names in my example. I would apply FK when creating table. – mike8787 Apr 10 '20 at 05:10

score 0 · Answer 3 · answered Apr 10 '20 at 04:54

0

You can do the following mapping in Spring Data JPA for your entities to replicate the above requirement. What you are trying is a composition relationship between Order and OrderLine where former is the owner not a parent/child relationship.

@Entity
@Table(name="Order")
class Order {
    @Id
    private Long orderNumber;

    @OneToMany(mappedBy="order")
    private List<OrderLine> orderLines;

}

@Entity
@Table(name="OrderLine")
class OrderLine {
    @ManyToOne
    @JoinColumn(name="orderNumber", nullable=false)
    private Order order;

    private Long lineNumber;

}

answered Apr 10 '20 at 04:54

shazin

21,379
3
54
71

So for this example, would I just do a repository.save(order) and the saving would flow through persisting all tables based on the relationship? The main reason I am considering deleting all OrderLine data first is that if a line was deleted, say OrderLine 2 then the data would now only contain OrderLine 1 and 3 and there would be no orderLine 2 in the data. Where as deleting all keeps the data cleaner. Probably a separate issue to my original question. – mike8787 Apr 10 '20 at 05:07
@mike8787: I've been through your situation. I think to keep database cleaner a bit is not really necessary. When your application scale-up with too large data, you will be getting tired if you trying to take care of your DB word by word. What you only need is one query and data will return exactly as you expected. – logbasex Apr 10 '20 at 05:22

Spring Boot JPA how to handle child table updates

3 Answers3