JDBC connection in session using HttpSessionBindingListener

Discussions

EJB design: JDBC connection in session using HttpSessionBindingListener

  1. Hi all,

    Our architechture needs us to store an OCI connection in the HTTPSession once the user logs in.This is because our security model is based around Oracle security(a user account is a direct Oracle9i account...user is authenticated against Oracle,and OraclePolicy functions decide which tables/views this user can touch).This connection is then used for any DB related queries(passed around to workshop controls etc). I know this is against generally recommended practice, but given that we need to do it, i thought of wrapping this connection in an HttpSessionBindingListener ,so that when the Servlet API calls the onValueUnbound(),i can close the internal connection. However, it has come to my notice that the
    valueUnbound method is not called each and every time a browser window is closed or a session times out. Does my wrapper object NEED to implement Serializable?Even if it does, the internal OracleOCIConnection is still unserializable. Is there any way i can close such a connection reliably when session is closed/invalidated/expired?
     
    This seems like a perfect example for a Statefull session bean, but i want to get some feel on how storing the connection in the session will scale.
    Thanks.
    Vik.
  2. valueUnbound method is not called each and every time a browser window is closed or a session times out


    Its very strange! This has to happen. What app server do you use? Did you verify that you are implementing the right interface and right methods? (I'm sure you would have verified this thousand times :))

    >>>Does my wrapper object NEED to implement Serializable?Even if it does, the internal OracleOCIConnection is still unserializable.
    You don't have to make your wrapper serializable unless you are making your application distributable. It still should receive valueBound() and valueUnBound() notifications.

    >>>Is there any way i can close such a connection reliably when session is closed/invalidated/expired?

    As I said, the server is required to notify HttpSessionBindingListener if it is J2EE1.3 compliant. Its very strange that your server does not do this :(

    >>> This seems like a perfect example for a Statefull session bean, but i want to get some feel on how storing the connection in the session will scale.

    This looks clean, beacause you are shielding your web components from maintaining ugly JDBC information. You don't have to pass 'connection' reference across tiers every time you invoke some business services. Wrapping connection information in a stateful bean keeps your code clean. But since OCIConnection is unserializable you may have problem when the stateful EJB is passivated. You stateful bean simple cannot passivate and you end up loosing connection information.
    So looks like you have to manage with 'connection-stored-in-session' approach.

    One other option I can think of is... Instead of OCIConnection in session, can you store userId and password in session? You may then pass userId and password, instead of connection, to business tier and establish a connection there. I'm not sure if this will meet your requirement. If it does, you may also use stateful session for realizing the same. Just my thoughts.
  3. Instead of OCIConnection in session, can you store userId and password in session? You may then pass userId and password, instead of connection, to business tier and establish a connection there. I'm not sure if this will meet your requirement. If it does, you may also use stateful session for realizing the same.


    Your stateful session should hold userId and password state. In ejbCreate() you may establish connection to oracle. Make sure you release the connection ii ejbPassivate() and re-acquire the same in ejbActivate().
  4. Instead of OCIConnection in session, can you store userId and password in session? You may then pass userId and password, instead of connection, to business tier and establish a connection there. I'm not sure if this will meet your requirement. If it does, you may also use stateful session for realizing the same.

    >
    > Your stateful session should hold userId and password state. I think it's enough. Maybe ,i cann't understand perfectly