Per-User Connection Pooling for Spring, Hibernate and Oracle VPD (Virtual Private Database)

There are several forum messages about how to handle per-user connection pooling but none of them clearly state what works. It is quite simple. Here is part of my applicationContext.xml.

<!-- this datasource must hande per-user connection pooling -->
<bean id="targetDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close">
  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
  <property name="url" value="jdbc:oracle:thin:@pooz:1521:ACC"/>
</bean>

<!-- this object wraps the original datasource allows change of user when needed -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
  <property name="targetDataSource" ref="targetDataSource"/>
  <property name="username"><value>unknown</value></property>
  <property name="password"><value>unknown</value></property>
</bean>

<!-- this bean does real work, inject the user adapter so it can change the user credentials -->
<bean id="create" class="com.codebits.dao.hibernate.actions.Create">
  <property name="sessionFactory" ref="sessionFactory" />
  <property name="userAdapter" ref="dataSource" />
</bean>

The Java code is also simple, just before you grab the Hibernate session, do the following:

  userAdapter.setCredentialsForCurrentThread("test", "test");