Using DB2 in XPages Part 3: Setting up the Database Connection

In this third entry in my blog series, I will explain the process that we used to set up a connection to the DB2 database.

Ground Rules

The major ground rule that we had to follow was that connection parameters, including username and password, cannot be stored in an XML file the way that IBM recommends. These values must be passed in to the connection manager via a parameter. All connection information must be pulled from a central location for each environment. We keep this information in a Notes database and use keywords to hold the path to this connection repository database.

It was no small effort to set up the connection manager to accept keywords. I will first explain what we needed to do for java access in this post, and in Part 4, I will explain the additional steps needed for SSJS access.  See Part 2 for an explanation of how the database architecture is setup.

Assumptions

First off, I am assuming that you are using Notes 9.  Next, I am assuming that you have downloaded the Extension Library from OpenNTF. The one that comes with Notes 9 does not contain the relational controls that you need. In November 2014, the relational controls were integrated into the main library. However, we had problems with the November version. The one we are using is the Dec 19, 2014 release. I recommend that one, or a newer release. You need to install it on your server and your Designer client. 

Setting Up Your Connection

Given the aforementioned ground rules, you will need to follow all of these steps to set up your connection. 

1) Ensure that the Extension Library is installed, and make sure the relational libraries are loaded for your database. Even though the libraries are included in the main library download, you still have to choose to add them to your database.  

2) Make sure the DB2 connection information gets created in the external connection repository database. For each connection you need these values: Username, Password, Driver, and URL

3) Create keywords lookup values. We use a Keyword Bean bound to Application Scope to hold all our keyword values. At a minimum, you will need values for:

• Schema
• DB2 Driver
• Path to connection repository database
• Key in connection repository database 

We use a managed bean for keywords; use whatever method you like.

4) Create Credentials.java class. As the name implies, this class loads the credentials and stores them in memory for all users. This class is a managed bean bound to applicationScope. See the code below in the Code Explanation section.  I think that if just using this for java (no SSJS), then you would want to make this a POJO.

5) The next step involves creating a class that extends the UIJdbcConnectionManager class. This is necessary in order to use passed in connection details. For this example, let's pretend that my employer is XYZ Corporation,  I would name this class XYZConnectionManager. This code is also explained below in the Code Explanation Section.

For java newbies, to extend a class means that we make a child class that inherits all the methods of the parent class. This allows you to add or overwrite functionality depending on your need. In our case, we implement an alternate way to read the credentials, but otherwise leave all the rest of the functionality intact.

Code Explanation - Credentials Class

>>Package, Imports, and Logging Removed

public class Credentials implements Serializable{ 

private static final long serialVersionUID = 1L;
     
public Credentials(){}  >>Default no argument constructor
        
public void load(){ 
                
HashMap<String,String> r = new HashMap<String,String>(); 
                
// Get FacesContext >>Standard Way to Get Scoped Variable
FacesContext context = FacesContext.getCurrentInstance(); 
Map applicationScope = context.getExternalContext().getApplicationMap(); 
                
// Get keywordBean >>Relates to Step 3 above
KeywordBean kw = this.getKeywords(); 
String dbpath = kw.getString("dbpasspath"); 
String dbview = kw.getString("dbpassview"); 
String dbkey = kw.getString("dbpasskey");  
                
try { 
     Session s = (Session)context.getApplication().getVariableResolver(). 
                resolveVariable(context, "sessionAsSigner"); 
      if(!s.isValid()){ 
       >>log error condition("Unable to access sessionAsSigner."); 
       return; 
       } >>Makes sure proper permissions are in place
                        
>>Get Handle to Notes Database that is connection repository
  Database db_pass = s.getDatabase(s.getServerName(), dbpath); 
  if (db_pass.isOpen()) { 
    View v = db_pass.getView(dbview); 
     if (v != null) { 
         Document doc = v.getDocumentByKey(dbkey, true); 
         if (doc != null) { 
         // ensure these HashMap variables match with the XYZConnectionManager 
         r.put("driver", kw.getString("DBDriver")); 
         r.put("url", doc.getItemValueString("PI_JDBCURL")); 
         r.put("user", doc.getItemValueString("PI_Username")); 
         r.put("password", doc.getItemValueString("PI_Password")); 
         applicationScope.put("dbpass",r); 
         doc.recycle(); 
         v.recycle(); 
         } else { 
         >>Log error condition("Unable to open document"); 
         } 
     } else { 
          >>Log error condition("Unable to open view Pass\\All"); 
     } 
  } else { 
   >>Log error condition("Unable to open connection database"); 
  } 
  if(db_pass.isOpen()){ 
      db_pass.recycle(); 
  } 
 } catch (NotesException e) { 
   >>Log error condition(e.getLocalizedMessage()); 
 } 
return; 
} 
        
private KeywordBean getKeywords(){ 
     >>Get handle to Keyword Bean for getting location to connections database          
}        
} //end Credentials.java

Code Explanation - XYZConnection Manager Class

Note: If you use this, replace XYZ with your company name. 

>>Package, Imports, and Logging Removed

public class XYZConnectionManager extends UIJdbcConnectionManager { 
>>UIjdbcConnectionManager is part of the Extension Library
 private JdbcPoolDataSource dataSource = null; 

public Connection createConnection() throws SQLException { 
>>This is the only method that we override from the superclass

// Initialize vars 
String driver = "";
String url = "";
String user = "";
String password = ""; 

>>Standard Way to Get Scoped Variable
 FacesContext context = FacesContext.getCurrentInstance(); 
Map applicationScope = context.getExternalContext().getApplicationMap();

>>If the Credentials are already in applicationScope then use them, otherwise use Credentials class to load them
  try { 
         // Read from ApplicationScope if available 
         // ensure the values being retrieved match with the HashMap values set in the Credentials class 
         if (applicationScope.containsKey("dbpass")) { 
                  HashMap dbpass = (HashMap) applicationScope.get("dbpass"); 
                  if (dbpass.containsKey("driver")) { 
                            driver = (String) dbpass.get("driver"); 
                   } 
                  if (dbpass.containsKey("url")) { 
                             url = (String) dbpass.get("url"); 
                   } 
                   if (dbpass.containsKey("user")) { 
                            user = (String) dbpass.get("user"); 
                   } 
                   if (dbpass.containsKey("password")) { 
                             password = (String) dbpass.get("password"); 
                    } 
          } else { 
               // Try to get credentials 
               x.error("Warning: DB2Pass not loaded."); 

               Credentials cred = new Credentials();

                >>Not sure why original developer creates new instance here, if it is managed already

               cred.load(); >>Loads credentials from connection database, then reads from applicationScope
               if (applicationScope.containsKey("dbpass")) { 
                       HashMap dbpass = (HashMap) applicationScope.get("dbpass"); 
                       if (dbpass.containsKey("driver")) { 
                              driver = (String) dbpass.get("driver"); 
                       } 
                       if (dbpass.containsKey("url")) { 
                               url = (String) dbpass.get("url"); 
                       } 
                       if (dbpass.containsKey("user")) { 
                                user = (String) dbpass.get("user"); 
                       } 
                       if (dbpass.containsKey("password")) { 
                                 password = (String) dbpass.get("password"); 
                        } 
                } else { 
                      >>log error condition ("Unable to access connection credentials."); 
                        return null; 
                } 

          } 

 } catch (Exception e) { 
          >>log error condition (e.getLocalizedMessage()); 
           return null; 
  } 

 // Create DataSource >>Creates the javax.sql.DataSource object which is needed to connect to DB2 using passed in parameters, only if needed, otherwise pulls from the connection pool.  Returns the Connection object.
   try { 
        if (this.dataSource == null) { 
               this.dataSource = new JdbcPoolDataSource(driver, url, user, password, 10, 20, 100, 0, 0, 0, 10000); 
       } 
       Connection c = dataSource.getConnection(); 
       initConnection(context, c); 
       return c; 

       } catch (PoolException e) { 
                 >>log error condition("PoolException creating JdbcPoolDataSource. " + e.getLocalizedMessage()); 
       } 
        return null; 
     }
        
      private KeywordBean getKeywords() { 
            >>Get handle to Keyword Bean for getting location to connections database 
       } 
} 

Sample Usage:
XYZConnectionManager cm = new XYZConnectionManager(); 
Connection c = cm.getConnection(); 

Attribution

I did not personally write any of this code, but having to explain it has helped me to understand and share this information.  The code shown here is based on code originally developed by Lauri Laanti. Here is a blog post that Lauri wrote that explains what he did. (Lauri, if you see this, I hope you don't give up on that blog).

I also need to give credit to a former coworker Eric Dyer and a current coworker Dwain Wuerfel, They did all this work in a previous project at my company.  We stole it for use in my current project. 

Next Post

In my next post, I will explain what additional steps are necessary to connect to DB2 using server side javascript.