Sharing uPortal's Connection Pool with Portlets on Tomat 5.5

Summary

Portlets, being their own application on Tomcat, do not inherently have access to uPortal's connection to the database. Fortunately, however, you can move uPortal's database connection context to a global context which is visible to your portlets. Many thanks to Chris Holdorph form UNICON for his insight, and Gregg Lagnese, MicroDeveloper, Inc. for his online Oracle JNDI reference. As a note, these modifications were made on uPortal 2.5.2 and Tomcat 5.5. I'm uncertain how uPortal's inherent support of portlets in v3 will affect database context visibility for individual portlets, or how much these steps differ for Tomcat 5.0 and other versions of uPortal.

There are several steps involved in obtaining uPortal's database context within your portlet. At a high level, these steps include:

Get the Commons JARs
Move the JDBC drivers to Tomcat
Modify Tomcat's server.xml File
Modify Tomcat's context.xml File
Modify uPortal's context.xml and uPortal55.xml Files
Modify Portlet's web.xml File
Code Database Connection in the Portlet

Step 1: Get the Commons' JARs

You might need to obtain the following Commons JARs for this to work:

If you do require these JARs, they would be put into <$TOMCAT_HOME>/commons/lib

Step 2: Move the JDBC drivers to Tomcat

You will need to move the JDBC drivers out of your <$TOMCAT_HOME>/webapps/uPortal/WEB-INF/lib and place it into <$TOMCAT_HOME>/commons/lib. Note that you should remove the driver from the existing lib folder in WEB-INF, otherwise you might experience some anomolies. If you also have this driver in your portlet's WEB-INF/lib folder, you'll need to remove it from that location as well.

Step 3: Modify Tomcat's server.xml File

Tomcat's server.xml contains an XML block for Global JNDI Resources. Within this block, a reference needs to be made for the database connection. All you need to do is copy the <resource name="jdbc/Portaldb"...> tag within <$TOMCAT_HOME>/webapps/uPortal/META-INF/context.xml, into the <GlobalNamingResources> of <$TOMCAT_HOME>/conf/server.xml. Server.xml should look similar to this:

...
  <!-- Global JNDI resources -->
  <GlobalNamingResources>

    <!-- Test entry for demonstration purposes -->
    <Environment name="simpleValue" type="java.lang.Integer" value="30"/>

    <Resource name="jdbc/PortalDb" auth="Container" type="javax.sql.DataSource"
              username="<user>" password="<password>"
              driverClassName="com.mysql.jdbc.Driver" url="<jdbc datasource>"
              maxActive="100" maxIdle="30" maxWait="10000"/>

    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users -->
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
       description="User database that can be updated and saved"
           factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
          pathname="conf/tomcat-users.xml" />

  </GlobalNamingResources>
  ...

Step 4: Modify Tomcat's context.xml File

Tomcat's context.xml (<$TOMCAT_HOME>/conf/context.xml) must be modified to provide a link to this resource for other web applications. You simply need to add one line to this file, so your context.xml should look similar to:

<!-- The contents of this file will be loaded for each web application -->
<Context>

    <!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->

    <ResourceLink global="jdbc/PortalDb" name="jdbc/PortalDb" type="javax.sql.DataSource" />
</Context>

Step 5: Modify uPortal's context.xml and uPortal55.xml Files

Since we've added the uPortal datasource reference to Tomcat's server.xml file, we no longer need it in uPortal's context. Modify <$TOMCAT_HOME$>/webapps/uPortal/META-INF/context.xml and comment out the resource as such (As a note, uPortal's context.xml file for Tomcat 5.5 is built from your uPortal's source folder under properties/uPortal55.xml. To make the change perminent, modify this file as well to comment out the resource, otherwise future builds may replace the context file with the version which includes the resource.):

<Context path="/uPortal" docBase="uPortal" crossContext="true">

    <!-- Maximum number of dB connections in pool. Set to 0 for no limit.-->
    <!-- Maximum number of idle dB connections to retain in pool. Set to 0 for no limit.-->
    <!-- Maximum time to wait for a dB connection to become available in ms, in this example-->
    <!--   10 seconds. An Exception is thrown if this timeout is exceeded.  Set to -1 to wait indefinitely. -->

    <!--
    <Resource name="jdbc/PortalDb" auth="Container" type="javax.sql.DataSource"
                  username="<user>" password="<password>"
                  driverClassName="com.mysql.jdbc.Driver" url="<jdbc datasource>"
                  maxActive="100" maxIdle="30" maxWait="10000"/>
    -->

    <Resource name="jdbc/PersonDb" auth="Container" type="javax.sql.DataSource"
        username="<user>" password="<password>"
        driverClassName="com.mysql.jdbc.Driver" url="<jdbc datasource>"
        maxActive="100" maxIdle="30" maxWait="10000"/>

    <!-- Disables restart persistence of sessions -->
    <Manager pathname=""/>

</Context>

Step 6: Modify Portlet's web.xml File

At this point, if you restart Tomcat, uPortal should start using the new global datasource reference (you should see no different in how uPortal loads). Now you can start working with your portlet to also use the global datasource reference. The first step is to add a reference to your portlet's web.xml (note: you do not need to make this addition to uPortal's web.xml because it already exists):

<web-app>
    .
    .
    .
    <resource-ref>
        <description>uPortal DataSource Reference</description>
        <res-ref-name>jdbc/PortalDb</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
</web-app>

Step 7: Code Database Connection in the Portlet

Now you can reference this datasource context within your Java code. For brevity, I'm excluding all of the necessary try/catch blocks, so make sure you catch your errors and close your connections properly:

import javax.naming.Context;
import javax.naming.InitialContext;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement; //optional
import java.sql.PreparedStatement;
import javax.sql.DataSource;

        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");

            if(envContext != null){
                DataSource ds = null;
                ds = (DataSource) envContext.lookup("jdbc/PortalDb");

                if(ds != null){
                    Connection conn = ds.getConnection();
                    pStmt = conn.prepareStatement("SELECT * FROM UP_USER WHERE USER_ID = ?");
                    pStmt.setInt(1,1);
                    rst = pStmt.executeQuery();

                    if(rst != null){
                        while(rst.next()){
                            System.out.println("User: " + rst.getString("USER_NAME"));
                        }
                    }
                    conn.close();
                }
                else
                    throw new Exception("Datasource was unavailable");
            }
            else
                throw new Exception ("Environment Context unavailable");

        } catch (Exception e) { }

Notes:

Oracle datasources - I haven't tested this, yet, but it looks like Oracle may have a slightly different configuration.