How to audit JPA without Hibernate Envers

Question

I need to make an audit module to my Java Web App. I use EclipseLink, not Hibernate (can't use Envers). I searched a lot some way to get the SQL or JPQL that JPA is executing, so I could log something like this:

System.out.println("User " + user + " from " + ip_address + " executed " + jpa_statement + " at " + new Date());

Actually, I'll save this info into a history database's table. So I can easily retrieve this info any time I want. That's why "SHOW SQL" parameters are not enough to me. I really need the SQL string, so I can manipulate it at my source code.

I found at JPA spec the EntityListener feature and thought it was the perfect place to put my logging code. For example, the postUpdate method could log the time the object was updated. But my problem is that I can't have the SQL the was executed.

Here is an example of what I mean:

public class AuditListener {    
  @PostUpdate
  public void postUpdate(Object object) {
    User user = (User)FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("user");
    String ip_address =  (User)FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("ip_address");
    String jpa_statement = object.getSQL();
    System.out.println("User " + user + " from " + ip_address + " executed " + jpa_statement + " at " + new Date());
  }

}

But "object.getSQL()" doesn't exists. So how can I get the SQL statement?

If anyone could point me into the right direction, I would appreciate!

Answer 1

EclipseLink has full support for history tracking.

See, http://wiki.eclipse.org/EclipseLink/Examples/JPA/History

JPA events do not contain what was changed, only the object.

EclipseLink also supports DesriptorEvents (see DescriptorEventListener) which also define a postUpdate but include an ObjectChangeSet that describe the changes, you also have the UpdateObjectQuery that contains the SQL and DatabaseRecord.

Answer 2

I usually recommend making the application server to the auditing, many have auditing capability. For example I recently did a project where audit data was stored in a separate database on a separate application server. We use the Glassfish audit module feature. Separated the concern of auditing from the application all together.

Answer 3

Envers for auditing is great, but I think, there can be a better solution for auditing. In Envers is a few shortcomings: Two db tables for every table. But second table is used rarely. For our large enterprise project where is 600+ tables it is problem. Envers is solution only for db changes. When I need solve auditing for other application components, I need different auditing solution. Auditing of collections is not ideal. When is changed database schema I must change audit tables too. I can lost some auditing informations or I have a lot of work with update old auditing informations to the new schema. Save auditing informations to the same database is time and space consuming.

I am start working on small auditing library where, informations are stored in MongoDB database: Is used one collection for all hibernate objects. Is stored every value of object. Auditing is done in different thread. Collections (list of ids) are stored too. The same MongoDB database is used for other application logs.

Has somebody some experience with this or similar solution ?