Update history table with Old and New values using Spring Boot, JPA

Question

Technologies used: Spring Boot, Spring Data JPA.

Problem Summary: I have a use case to update order_tracking table when ever any status change happen in the purchase_order table's

User Entity:

@Entity
@Access(AccessType.FIELD)
@Table(name = "USER")
public class User extends BaseEntity {
private static final long serialVersionUID = 1L;

  @Id
  @Column(length = 28)
  @GeneratedValue(generator = "USER")
  @SequenceGenerator(name = "USER", sequenceName = "USER")
  @Access(AccessType.PROPERTY)
  private Long id;

  @Column(unique = true)
  private String email;

  private String firstName;

  private String lastName;
}

User Table:

id  email_id       first_name    last_name
------------------------------------------
101 test@eg.com    Test          Example

Purchase Order Entity:

@Entity
@Access(AccessType.FIELD)
@Table(name = "PURCHASE_ORDER")
public class PurchaseOrder extends BaseEntity {
  private static final long serialVersionUID = 1L;

  @Id
  @Column(length = 28)
  @GeneratedValue(generator = "PURCHASE_ORDER")
  @SequenceGenerator(name = "PURCHASE_ORDER", sequenceName = "PURCHASE_ORDER")
  @Access(AccessType.PROPERTY)
  private Long id;

  private Long userId;

  private String trackingNumber;

  private String status;
}

Purchase Order Table:

id   user_id    tracking_number   status
-------------------------------------------
101  101        1001              DELIVERED

For each activity, the status will be updated in the order_tracking table like this

Order Tracking Table:

id  order_id   old_status     new_status      date
-----------------------------------------------------------------
1   101        NULL           CREATED         2017-07-14 10:08:10
2   101        CREATED        REQUESTED       2017-07-14 22:08:10
3   101        REQUESTED      IN_TRANSIT      2017-07-15 12:08:10
4   101        IN_TRANSIT     DELIVERED       2017-07-15 22:18:10

When ever any status change in the purchase_order table, then order_tracking table need to be updated with old and new statuses.

To achieve this, i got suggestions here

But, my use case is to update order_tracking table when ever any status change in purchase_order table.

Can anyone help me the best approach to achieve this.

`public void setStatus(String status) { if (!this.status.equalsIgnoreCase(status) { this.history.add(new OrderTracking(this, this.status, status); } this.status = status; }`. — manish, Aug 03 '17 at 06:23

score 0 · Answer 1 · answered Aug 07 '17 at 13:36

One invasive way is to obviously manage this as part of your bean's logic:

public void setStatus(OrderStatus status) {
  if ( this.status != null && !this.status.equals( status ) ) {
    this.history.add( new OrderStatusTransition( this, this.status, status ) );
  }
  this.status = status;
}

In this use case, you'd audit the OrderStatusTransition entity.

From an Envers perspective, this transition entity and even your order status table are completely unnecessary as you can accomplish representing the same data by using an audit reader query fetching the same information needed.

The benefit then is that avoids you having to build any type of hook or mechanism for tracking when the status changes and simply use the purchase order table and Envers to do that automatically for you.

You then simply build a business method that gets the necessary representation of those changes you need via a AuditReader query at runtime rather than trying to handle this at data manipulation and dirty tracking time.

Update history table with Old and New values using Spring Boot, JPA

1 Answers1