How to use JDBC ClientInfo with JPA and EJB3

Question

We are migrating a JDBC based application to JPA and EJB3. Our old application used the Connect#setClientInfo API to record the current user name as part of the client info: http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#setClientInfo%28java.lang.String,%20java.lang.String%29

We need to do something similar in the EJB3 project. How?

We can use EJB3 interceptors around the EJB service calls in order to capture the current user and set it as info on the datasource. However, I see problems with this. I think there is no guarantee when the JPA flush() occurs. If you set the client info in an interceptor, make some updates, and then return, the flush() and actual database write may not occur until well after your bean (and interceptor) are out of scope. Is this correct?

I believe JPA and EntityManagers are abstractions over the connection, and you cannot reliable set the client info on the connection. True or false?

Answer 1

What JPA provider are you using?

EclipseLink has support for user based connection, Oracle proxy connections and VPD. EclipseLink also defines Session and connection level events that allow you to set configuration on the JDBC connection.

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