Simple Auditing using TopLink JPA

In a forum post on the Oracle TopLink forum on OTN there was a request for an approach to tracking entity history in a separate table. While Oracle TopLink includes support for automatic historical versioning this support does not exist in TopLink Essentials.

The following is a simple example for how you might do historical versioning of your persistent entities using TopLink Essentials and JPA. There are many approaches to how you would design your schema for managing this additional state. I kept my approach simple where the primary data table uses an integer version optimistic locking column and the history table stores a row for each version of the primary table.

The sample model is simply an Employee entity and its history is stored in an EmployeeHistory entity.

@Entity
@NamedQuery(name = "Employee.findAll",
query = "select e from Employee e ORDER BY e.lastName ASC, e.firstName ASC")
@TableGenerator(name = "emp-seq-table", table = "SEQUENCE",
pkColumnName = "SEQ_NAME", valueColumnName = "SEQ_COUNT",
pkColumnValue = "EMP_SEQ", allocationSize = 26)
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.TABLE,
generator = "emp-seq-table")
@Column(name = "EMP_ID", nullable = false)
private int id;

@Column(name = "F_NAME")
private String firstName;

@Column(name = "L_NAME")
private String lastName;

private String gender;

@Version
private long version;

...

@Entity
@IdClass(EmployeeHistory.ID.class)
@Table(name = "EMPLOYEE_HISTORY")
@NamedQuery(name="EmployeeHistory.findAllForEmployee", query="SELECT eh FROM EmployeeHistory eh WHERE eh.employee = :EMP")
public class EmployeeHistory {
@Id
@Column(name = "EMP_ID")
private int id;

@Id
private long version;

@ManyToOne
@JoinColumn(name = "EMP_ID", referencedColumnName = "EMP_ID", insertable=false, updatable=false)
private Employee employee;

@Column(name = "F_NAME")
private String firstName;

@Column(name = "L_NAME")
private String lastName;

private String gender;

/**
*
* @param emp
*/
protected EmployeeHistory(Employee emp) {
this.id = emp.getId();
this.version = emp.getVersion();
this.firstName = emp.getFirstName();
this.lastName = emp.getLastName();
this.gender = emp.getGender();

this.employee = emp;
}

...

Now to have the creation and modification of all Employee instances cause the creation of an EmployeeHistory instance I will use a TopLink Essentials specific event listener.

The descriptor event listener used looks like:

/**
* This is an example of how a TopLink Essentials DescriptorEventListener can be
* used to populate an audit record whenever an update occurs. This class must be
* registered as a persistence unit property in the creation of the
* EntityManagerFactory.
*
* property name="toplink.descriptor.customizer.Employee" value="model.jpa.auditing.EmployeeAuditingHandler"
*
* @since TopLink Essentials v2-Build-41
*/
public class EmployeeAuditingHandler extends DescriptorEventAdapter implements DescriptorCustomizer {

/**
* This is where the real work of this class happens. For any INSERT or UPDATE
* operation an INSERT of a new history object is forced.
*/
private void insertEmployeeHistory(DescriptorEvent event) {
EmployeeHistory empHist = new EmployeeHistory((Employee)event.getSource());
InsertObjectQuery insertQuery = new InsertObjectQuery(empHist);
event.getSession().executeQuery(insertQuery);
}

public void aboutToUpdate(DescriptorEvent event) {
insertEmployeeHistory(event);
}

public void aboutToInsert(DescriptorEvent event) {
insertEmployeeHistory(event);
}

/**
* This method is invoked through this class's registration as a descriptor
* customizer. All it does is register this same object as an event listener
*/
public void customize(ClassDescriptor descriptor) {
descriptor.getEventManager().addListener(this);
}
}

Now with this event listener in place I can proceed in my application using standard JPA coding. Every time I create or modify an Employee entity I will see the associated EmployeeHistory INSERT during the transaction commit.

I hope you find this helpful.

Doug