Search code examples
javamysqlauditing

MySQL/Java Auditing of specific fields

I have few tables in which there could be an update on some of the fields.

I would like to keep a history of changes of different fields. Different fields however have different types, however I wouldn't want to create many different columns in my "auditing" table.

So I though should I use serialization (I used it often in PHP), I'm not sure however how efficient it would be in Java/MySQL (I'm not using any data frameworks etc). If serialization, then to what? XML/JSON?

I thought two tables:

  • AuditEntityProperty (Id (PK), EntityId (FK), PropertyName, From, To, DateTime, Username)
  • AuditEntity (EntityId (PK), EntityName)

From/To are my current problems that I'm not sure how to address. I'm leaning towards XML and using TEXT as datatype for both. Ideas?

Solution

  • I would suggest XML.

    My table structure:

    Audit_id, operation, preImage, postImage, userId, datetime, entityName, timestamp

    Then I would map this to say an AuditBean, I would have used JPA, hibernate if I would have an option of, Use Spring AOP to update the audit table when table structure is updated.

    One table insertion which would have preImage and postImage.

    For XML generation I think XStream is one of the most easy use.

    All the table which needs to be Audit, I would have marked them as XMLConvertable (So that I can easily get Class Alias, and other alias fields for XStream) and Auditable (So that my AOP code can extract the preImage from say database)