We are using hibernate with oracle.

To restrict rows access, we have base tables and views defined on top of base tables witha column client_id. The views return data from base tables based on client_id that is fetched from the context. Using one stored procedure you can set the client_id for session and then the view would only return data for the set client_id.

Now we are using hibernate and access database from a java layer. we want to set the client_id of logged in user for a hibernate session. [ yes, filters are available in v3 but lets confine to the above problem for the moment] we have a 2 layer application and we are using open sessions in view pattern, something like,

sf.getCurrentSession().beginTransaction();
chain.doFilter(request, response);
sf.getCurrentSession().getTransaction().commit();


I read on this forum that i can try using my custom connection provider.
[ http://forum.hibernate.org/viewtopic.ph ... hlight=vpd]

so I thought i will extend the c3P0 connection provider and override the getConnection() method like

myConnectionProvider extends C3P0ConnectionProvider{

getConnection(){
Connection conn = super.connection();
// execute the stored procedure to set client_id on conn
return conn ;
}

I want to know if this kind of strategy can work with connection pooling. with the kind of setup we have, we call sf.getCurrentSession() with every request hit, would that call a getConnection() on connection provider every time ?

Second issue is, if i set the client_id in getConnection(), I have to retrieve client_id prior to that. how can i do that ?

Hibernate version:3.0
DB - oracle version 9.2.0.1

Thanks,

Hi.

To perform some set-up operation on a Connection just once upon its being acquired from the database via c3p0, try using the latest c3p0-0.9.1 prerelease, and implement the ConnectionCustomizer interface.

The strategy you propose should work, but you will end up performing your stored procedure upon repeatedly on each Connection, as the connection provider's getConnection() method maps to the check-out of a pooled Connection rather than its acquisition from the underlying DB.

smiles,
Steve