0

I am using JPA with Hibernate as the provider on WebSphere.

Is there any way to fire a SQL statement when a new session is opened against the database. Ideally I would want to say when a new Connection is taken out of the pool and given to a thread to use I want to fire a statement.

The basic use case is it's a SET VARIABLE command which should be fired per logged on user (it's passing the userid of the user to the database, rather than the technical user which WebSphere's datasource is authenticating with).

So the SQL statement needs the logged in user id from the application. The statement cannot be configured to fire on the Application Server itself. All of my EntityManager interaction looks the same i.e.

@Stateless
@LocalBean
@Resource(name = "jdbc/ANY", type = javax.sql.DataSource.class, 
authenticationType = AuthenticationType.CONTAINER, shareable = true)
public class MyRepository {

    @PersistenceContext
    private EntityManager entityManager;
planetjones
  • 12,469
  • 5
  • 50
  • 51
  • See accepted answer for similar question here. Not exactly the same but the general appraoch should work http://stackoverflow.com/questions/30864579/is-it-possible-to-have-connection-preparation-with-spring-jpa-persistency/30886058#30886058 – Alan Hay Apr 29 '16 at 14:37
  • Yeah problem is that's using Spring - I'm just using Java EE (i.e. CDI). It doesn't seem to be as flexible as Spring in this regard, unless someone knows different. – planetjones Apr 29 '16 at 14:42
  • I'm not sure that Spring/no Spring is relevant. Apply an Aspect at some point where you can get a handle on the EntityManager and proceed from there. May be you can post some code. – Alan Hay Apr 29 '16 at 14:46
  • Is the user id available globally i.e. bound to a ThreadLocal like Spring Security. If so I am thinking you can specify a custom EntityManagerFactory and override createEntityManager(); – Alan Hay Apr 29 '16 at 15:11
  • You can use [javax.interceptor.Interceptors](http://docs.oracle.com/javaee/7/api/javax/interceptor/Interceptors.html) and friends to achieve the same thing that is described in @AlanHay 's link. – Steve C Apr 29 '16 at 15:46
  • I am not sure how to specify a custom EntityManagerFactory. I don't think the Interceptors can give the same behaviour, as all are 3rd party classes which I can't set an interceptor on. I have a ref to EntityManager, but not sure how that helps at all ? I want to run the SQL once per user session with the DB. – planetjones May 02 '16 at 14:46

0 Answers0