Transaction Interception

[x24m]

Hi all,

I have a stored procedure that needs to be called after every connection is made to grant a role to the user if their IP address is found in a security table, essentially certifying that the database user connected from the app server. This works fine in a unit test but trying to work out where to put it in the web appliation context.

We're using JPA with hibernate 3, spring 2, annotations (spring tx and hibernate), oracle 10g.

Essentially the stored proc must be called from the same entity manager that makes the calls, to give the user access to tables that are otherwise not present in their profile.

The appContext.xml file has in it a transactionInterceptor for the Spring tx annotations, would there be someway to decorate that class to do our work for us? or should we look at modifying the entityManagerFactory proxy etc. first thought was to extend the OpenSessionInViewFilter but some code is called at startup that is before the first request.

Also, I'd ideally like to only call the proc when a new connection is made, but don't know how to determine that

Any help much appreciated...

Chris.

[karldmoore]

Couldn't you wrap your DataSource to achieve this?
http://www.springframework.org/docs/...ataSource.html

[x24m]

many thanks karldmoore,

That was a great solution....

For anyone else that's interested, by extending the DelegatedDataSource, and overriding the getConnection method, we can call the stored proc every time the connection is retrieved.

To add the new delegated data source to the config something like the following is required:

<bean id="delagatingDataSource" class="xxx.DelegatingDataSource">
<property name="targetDataSource" ref="dataSource"/>
</bean>

Then use the delegatingDataSource bean anytime you'd have previously used the dataSource.....

hth, and thanks again karldmoore

Chris.

[karldmoore]

Not a problem Chris, glad to help! Nice to know it was easy enough to implement.