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!

Shanleigh

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.

Osmious

Thanks, James! Very cool the EclipseLink history tracking. But it is not yet what I'm looking for. This feature has 2 problems: I can't add my columns to this history table (username, ip_address, ...); and the history is about only one entity at a time, I want only one history table for all entities so I can know exactly what were the user's actions on a "timeline". Thanks anyway, I'll keep your sugestion in mind. – Angelicaangelico

@Shanleigh can't you use the DescriptorEvents James mentioned and use the data to seed your own history table? – Boito

Answer 2

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 ?

Pallette

Why use mongoDB? I think HBase will be more suitable here. – Badillo

I have the same problem. a large database. There is an idea to make the audit database separately as json objects. Storage-mongo. + option interlayer in the form of MQ. It took 4 years, You managed to implement the option? – Hysell

Answer 3

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.

Edulcorate

Thanks for the idea, Chris! – Angelicaangelico

Hi @Chris, can you post some code in your answer about this Glassfish audit module feature ? Or point me some link about it ? – Interchangeable