29.9 Database User State and Pooling Considerations

Sometimes you may need to invoke stored procedures to initialize database state related to the current user's session. The correct place to perform this initialization is in an overridden prepareSession() method of your application module.

29.9.1 How Often prepareSession() Fires When jbo.doconnectionpooling = false

The default setting for jbo.doconnectionpooling is false. This means the application module instance hangs onto its JDBC connection while it's in the application module pool. This is the most efficient setting because the application module can keep its JDBC prepared statements open across application module checkouts/checkins.The application module instance will trigger its prepareSession() method each time a new user session begins using it.

29.9.2 Setting Database User State When jbo.doconnectionpooling = true

If you set jbo.doconnectionpooling to true, then on each checkout of an application module from the pool, that application module pool will acquire a JDBC connection from the database connection pool and use it during the span of the current request. At the end of the request when the application module is released back to the application module pool, that application module pool releases the JDBC connection it was using back to the database connection pool.

It follows that with jbo.doconnectionpooling set to true the application module instance in the pool may have a completely different JDBC connection each time you check it out of the pool. In this situation, the prepareSession() method will fire each time the application module is checked out of the pool to give you a chance to reinitialize the database state.

29.9.3 Understanding How the SRDemo Application Sets Database State

The SRDemo application includes a simple example of setting database state on a per-user basis. It uses the following PL/SQL package to set and get a package-level variable that holds the name of the currently authenticated web application user.

Example 29-4 SRDemo CONTEXT_PKG PL/SQL Package

create or replace package context_pkg as
  procedure set_app_user_name(username varchar2);
  function app_user_name return varchar2;
end context_pkg;

The WHERE clause of the ServiceHistories view object in the demo references the context_pkg.app_user_name function to illustrate how your application queries might reference the per-user state.

Note: In practice, you will typically create a database CONTEXT namespace, associate a PL/SQL procedure with it, and then use the SYS_CONTEXT() SQL function to reference values from the context. however the simple PL/SQL package above was enough to illustrate the mechanics involved in setting and referencing the user state without further complicating the installation of the SRDemo application, so the demo's authors chose this simpler approach to keep the demo more straightforward.

The SRApplicationModuleImpl framework extension class in the FrameworkExtensions project includes a callStoredProcedure() helper method similar to the ones in Section 25.5.2, "Invoking Stored Procedure with Only IN Arguments". The SRServiceImpl application module class extends this class and defines the setCurrentUserInPLSQLPackage() helper method shown in Example 29-5 that uses the callStoredProcedure() method to invoke context_pkg.set_app_user_name() stored procedure, passing the value of the currently authenticated user as a parameter value.

Example 29-5 Method to Call Context_Pkg.Set_App_User_Name Stored Procedure

// In SRServiceImpl.java
public void setCurrentUserInPLSQLPackage() {
  String user = getUserPrincipalName();
  callStoredProcedure("context_pkg.set_app_user_name(?)",new Object[]{user});
}

With this helper method in place, the SRServiceImpl class then overrides the prepareSession() method as shown in Example 29-6.

Example 29-6 Overridden afterConnect() and prepareSession() to Set Database State

// In SRServiceImpl.java
  protected void prepareSession(Session session) {
    super.prepareSession(session);
    getLoggedInUser().retrieveUserInfoForAuthenticatedUser();     
    setUserIdIntoUserDataHashtable();
   setCurrentUserInPLSQLPackage();      
  }