1

My web application runs on Tomcat 7.0 and uses Spring 3.2, Hibernate 4.3 and Oracle Database.

The entityManagerFactory is configured as org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean and the transactionManager is org.springframework.orm.jpa.JpaTransactionManager. The dataSource is defined as org.springframework.jndi.JndiObjectFactoryBean and internally refers to a connection pool of type org.apache.tomcat.jdbc.pool.DataSourceFactory.

Each time when a connection is fetched from the pool by the entityManager, I'd like to execute a custom sql statement in that connection. It will only change a property in the Oracle session context, like so:

dbms_session.set_context(context, propName, propValue);

So no dml is done.

Each time when a connection is released back to the pool, a similar statement should be executed to clear the property value.

The property value should be fetched dynamically from the spring-security context, so I can't hardcode this into a connection test statement.

I've also looked into Hibernate interceptors, but I've found no way to execute any sql in the same session from within an interceptor.

Any ideas?

Community
  • 1
  • 1
vadipp
  • 877
  • 1
  • 12
  • 22
  • 1
    Easy way is to just wrap the `DataSource` you get and execute the query in the `getConnection` method and also wrap the returned `Connection` and implement other query in the close method (before calling close on the delegate). A more complex solution would be to implement it using `AOP` for this. – M. Deinum May 18 '17 at 13:55
  • For now we've implemented a custom subclass of `JpaTransactionManager` and inserted the extra statements into its `doCommit` method. However, this only works when working with Hibernate objects. When calling an Oracle stored procedure, the statements are executed after the stored procedure, which is too late :( Still looking for the correct solution. – vadipp Sep 21 '17 at 06:37
  • @M.Deinum thank you, will look into this. – vadipp Sep 21 '17 at 06:38

0 Answers0