CODEWITHSUNDEEP
javaspringhibernatespring-data-jpa
GavinF
GavinF

Reputation: 397

CRUD.save() with timestamp from DB

I have a Spring application backed onto H2 and SqlServer. I have set the entity column such as:

@Column(insertable = false, columnDefinition = "bigint default CURRENT_TIMESTAMP")

Now when I save a new entity the timestamp is set correctly. However when I modify the entity the timestamp will be whatever I set on the object pre update. I am aware that if i set updatable = false then it will update to current_timestamp again however I need flexibility so that timestamp is only ever set if there is a significant change, IE if i change a comment field on the entity i do not want the timestamp to change but if i change the owner field I do want the timestamp to be updated.

I have tried setting the value to null but this does nothing. I am not sure how I could use the @PreUpdate method to get the CURRENT_TIMESTAMP from db to use nor am I aware of any way to easily modify the crud.save(). The only alternative I can think of is to add a customSave using @Modifying with a native query but this would be a last resort.

Note: I must use the DB timestamp, accuracy is highly important, rows must be persisted in order with incrementing timestamps and server time drifts introduce race conditions.

Upvotes: 0

Views: 425

Answers (2)

GavinF
GavinF

Reputation: 397

Thank you above for suggestions, I have battled most of today on this with no easy solution, Naros your suggestion of trigger is closest to being workable however it turns out that regardless of the source of the timestamp I found unless I enable read uncommited which is not an option there is an issue that transactions can take up to a few hundred milliseconds and as such there is too big a race condition to trust the timestamp order as it is assigned on save not transaction complete. I have opted for adding a new table which will act like a queue and put the ID from the primary table in there each time the timestamp is updated, less efficient but it is transaction safe with no race conditions.

Upvotes: 0

Naros
Naros

Reputation: 21113

Given your list of requirements, you realistically have 3 options

  1. Use a database trigger on your table to control the update timestamp values.
  2. Use a @PreUpdate callback.
  3. Do this as part of your service when you have access to the update state and existing state.

Option (1) is quite straight forward in that you have both the current state and update state in your database trigger and you can perform whatever operations necessary to determine whether you should set the value or take the existing value at the time of update.

The only downside between (1) and (2) is that (2) will not be based on database time but rather on application time because you'll be doing this on the application server. But (2) has the benefit that your code for this will be database agnostic allowing you to deploy anywhere on any database platform that Hibernate supports or has a functioning dialect for without any concern.

My personal opinion, pick (2) because its also quite Domain-Driven Design focused.

For the implementation of (2), this is quite straight forward

@Entity
public class YourSuperDumperEntity {

  // Clear this value on load
  @PostLoad
  public void postLoadClearFlags() {
    fieldValuesChangedThatRequireTimestampUpdate = false;
  }

  // This handles the setting of the value at update-time
  @PreUpdate
  public void preUpdateCallbackAdjustTimestampOnChanges() {
    if ( fieldValuesChangedThatRequireTimestampUpdate ) {
      updateTimestamp = new Date();
    }
  }


  public void setSomeFieldThatTriggersUpdateTimestamp(String value) {
    // check if the old and new value differ enough to trigger update date
    // if so, set the boolean flag here as follows
    this.fieldValuesChangedThatRequireTimestampUpdate = true;

    // now set the value
    this.someFieldThatTriggersUpdateTimestamp = value;
  }
}

As I indicated earlier, the beauty here is all the logic for this is self contained in a single class where it belongs because what you're talking about is entity-state.

You may need to play with the JPA callbacks a bit more to suit your needs, but the idea remains the same.

If you don't like (2), my suggestion would then be (3) because that maintains the database agnostic support which I find far more important than the timestamp being sourced from the database itself. With proper operating system time synchronization, the actual source of the time should be irrelevant.

Upvotes: 1

Related Questions