title: Audit Schema Mapping with Hibernate and AOP
body: <p>I am trying to audit the action that the user performed that resulted in changes to corresponding tables. For example if a user were to transfer money between 2 accounts this would generate the following sequence of events:</p>
  
  <ol>
  <li>Insert transfer amount into Transfer table</li>
  <li>Subtract transfer amount from balance in Balance Table for Account 1.</li>
  <li>Add transfer amount to balance in Balance Table for Account 2.</li>
  </ol>
  
  <p>The parent audit message for all tables would be: "User generated transfer for amount XXX"</p>
  
  <p>This is achieved with the following schema:
  <a href="http://img48.imageshack.us/img48/7460/auditloggingiv6.png" rel="nofollow noreferrer">schema</a></p>
  
  <p><a href="http://img48.imageshack.us/img48/7460/auditloggingiv6.png" rel="nofollow noreferrer">alt text http://img48.imageshack.us/img48/7460/auditloggingiv6.png</a></p>
  
  <p>The question is how do I represent this in hibernate? </p>
  
  <p>I have created the following:</p>
  
  <p>In Balance and Transfer's mapping files</p>
  
  <pre><code>&lt;set name="auditRecords" table="TransferAuditRecord" inverse="false" cascade="save-update"&gt;
    &lt;key&gt;
      &lt;column name="AuditRecordID" not-null="true" /&gt;
    &lt;/key&gt;
    &lt;one-to-many class="audit.AuditRecord"/&gt;
  &lt;/set&gt;
  </code></pre>
  
  <p>Transfer and Balance classes then implement IAuditable which has methods </p>
  
  <pre><code>public void setAuditRecords(Set&lt;AuditRecord&gt; auditRecord);
  public Set&lt;AuditRecord&gt; getAuditRecords();
  </code></pre>
  
  <p>In AuditRecord's mapping file I have:</p>
  
  <pre><code>&lt;many-to-one name="parentAuditRecord" lazy="false"
               column="parent_id"
               class="audit.AuditRecord"
               cascade="all" /&gt;
  </code></pre>
  
  <p>Then in Logging class using AOP and Hibernate Interceptors I have:</p>
  
  <pre><code>AuditRecord auditRecord = new AuditRecord();
  auditRecord.setUser(userDAO.findById(
      org.springframework.security.context.SecurityContextHolder.getContext()
          .getAuthentication().getName()));
  
  auditRecord.setParentAuditRecord(getCurrentActiveServiceRecord());
  
  auditable.getAuditRecords().add(auditRecord);
  </code></pre>
  
  <p>Then in the Service Class I call the following method, enclosed in a transaction:</p>
  
  <pre><code>save(balance1);
  save(balance2);
  transfer.setPassed(true);
  update(transfer);
  </code></pre>
  
  <p>The parentAuditRecord is created using AOP with a thread safe stack, and the AuditRecordType_id is set using annotations on the method.</p>
  
  <p>I left out the "passed" column on the transfer table. Previously I call save(transfer) to insert the transfer amount into the Transfer table with passed set to false. (This action is also audited).</p>
  
  <p>My requirements are slightly more complicated than the example above :P</p>
  
  <p>So the sequence of events for the above should be:</p>
  
  <ol>
  <li>Update Transfer Table</li>
  <li>Insert into AuditRecord (Parent)</li>
  <li>Insert into AuditRecord (Child)</li>
  <li>Insert into TransferAuditRecord</li>
  <li>Insert into Balance Table</li>
  <li>Insert into AuditRecord (Child)</li>
  <li>Insert into BalanceAuditRecord</li>
  <li>Insert into Balance Table</li>
  <li>Insert into AuditRecord (Child)</li>
  <li>Insert into BalanceAuditRecord</li>
  </ol>
  
  <p>However the cascade options defined above fail at the update statement. Hibernate refuses to insert a record into the many-to-many table (even if unsaved-value="any" on the AuditRecord Mapping). I always want to insert rows into the many-to-many tables so potentially one Transfer has many Audit Records marking the previous events. However, the latest event determines the message the user wants to see. Hibernate either tries to update the many-to-many table and previous AuditRecord entries or it simply refuses to insert into AuditRecord and TransferAuditRecord, throwing a TransientObjectException.</p>
  
  <p>The Audit Message is retrieved something like this:</p>
  
  <pre><code>msg=... + ((AuditRecord) balance.getAuditRecords().toArray()[getAuditRecords().size()-1])
      .getParentAuditRecord().getAuditRecordType().getDescription() + ...;
  </code></pre>
  
  <p>The message should say something like this:
  "Username set transfer to passed at 12:00 11-Oct-2008" </p>
  
  <p><strong>EDIT</strong> I decided to go with explicitly mapping the many-to-many table (with an associated interface), and then in afterTransactionCompletion, calling save on the parent audit record (which cascades the save to the child audit records) then explicitly saving the interface on all child mapping tables. This isn't a true audit history, rather a non-invasive method of recording user action. I will look into Envers if I need more complete audit history at a later point.</p>