2

I am using simple crud operations with auditing in spring data jdbc. so when ever i insert new record auditing works absolutely fine. but when i am updating my record my createdby and createddate setting to null. is there any way i can avoid modifying these columns?

Note:spring data jdbc is not supported with column(updatable= false) feature.

My audit entity looks like below:

@Data // lombak
public class AuditEntity {
  @JsonIgnore
  @LastModifiedDate
  private String updatedOn;
  @JsonIgnore
  @LastModifiedBy
  private String updatedBy;
  @JsonIgnore
  @CreatedDate
  private String createdBy;
  @JsonIgnore
  @CreatedBy
  private String createdOn;

}

and for configuration using @EnableJdbcAuditing and defining the below bean

@Bean
  AuditorAware<String> auditorAware() {
    return new CustomAuditAware();
  }

my custom audit aware looks like below

public class CustomAuditAware implements AuditorAware {
  @Override
  public Optional getCurrentAuditor() {
    return Optional.ofNullable(
        "System");
  }
}
Ravi
  • 152
  • 1
  • 11

3 Answers3

1

Yeah, as far as I know it is not possible. Since MySQL doesn't restrict the change of a data in any column.

However, developers make a use of TRIGGER.

Triggers basically help you record, observe and validate any change of data(database row).

create trigger check_if_a_column_has_changed on your_table
-- before update
   pull your old column value and check if it has been changed, 
   throw error if data been changed

Catch this exception in your application and show error messages to your clients.

Study database triggers, they will help you. However you'll have to bear the cost of a little bit of lag as the data in your table grows.

Asgar
  • 1,920
  • 2
  • 8
  • 17
  • instead of throwing an exception it should be possible to replace null values with whatever is already in the table. – Jens Schauder May 20 '22 at 09:16
  • Through triggers we can achieve but I am looking into spring way... At least is there any events in spring data jdbc to update this created by with old value only? – Ravi May 21 '22 at 06:13
  • Well in that case, just before updating the entity, why don't you Query the old record from database and check for the corresponding fields and take appropriate actions? – Asgar May 22 '22 at 05:25
  • thats my final option. i am just checking for any common place that we can change this data. since in my above example i am not adding these values myself... spring takes care of that. – Ravi May 23 '22 at 05:30
1

To help others who are still looking,

Alternate way to make a field insertable only (like audit fields: createdDate & createdBy) in spring-data-JDBC, is to use @InsertOnlyProperty

So the properties in original question would become like:

    @JsonIgnore
    @CreatedDate
    @InsertOnlyProperty
    private String createdBy;

    @JsonIgnore
    @CreatedBy
    @InsertOnlyProperty
    private String createdOn;

Reference Link: https://github.com/spring-projects/spring-data-relational/pull/1327

Ayush
  • 21
  • 4
1

To help others who are still looking,

You can also have the updatable property from the @Column to false.

  @CreatedDate
  @Column(updatable = false)
  private Instant createdAt;
Shashank
  • 54
  • 3