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.

Using DB2 in XPages Part 2: Application Architecture Overview

This is the second in my blog series on using an Db2 backend with XPages.  I will go over the application architecture, and do my best to explain why we did things the way we did.

Working for a very large financial company, they are extremely concerned with protecting the security of their customers.  This is a good thing of course, especially considering some of the high profile breaches in recent years.  

To protect themselves, they have a mindset of following security standards in developing applications, even internal applications like this one.  Standards are fine and good, but in the fast moving world of Web Application Development it is difficult to standardize on anything, because two years later the winds have changed. As a developer, I am very concerned with keeping up with the latest trends, and taking advantage of the collective knowledge of my blogging peers.  This can lead to some debate when the standards don't mesh with recent trends or best practices. As my coworkers could attest, I am always up for defending my position, but ultimately, you have to do what your employer says to do because hey, they are paying you.

With that background being said, I want to explain that what is going to be explained in this blog series isn't intended to demonstrate the best way to connect to relational databases.  This series is going to cover the way we DID do it.  I make no claims that this is the best, easiest or fastest way, but this is a way that did work and there is a lot to be said for that.  There are lessons that can be learned from what we did, or maybe even lessons on what not to do.

Application Architecture

From the start, I designed the application to follow a Model-View-Controller style architecture.  For the most part I felt we were able to accomplish this goal, especially in keeping code segregated. Speaking of M-V-C, I found these slides from Ulrich Krause at Engage 2015 to be a very excellent overview of M-V-C in XPages.  Wish I could have seen the session.

High Level Architecture

Some key points not readily apparent from this high level chart.  

• The UI elements are not directly bound to DB2 data via the relational controls, they are bound to either viewScope or managed bean fields and changed using java methods in the controller layer.  
• There is no Object Relational Mapping framework in place (ie: Hibernate)
• As much as possible business logic is in java, but all DB2 access is in java. 
• The java methods in the controller layer contain actual SQL statements to interact with DB2. 
• We are using the extention library relational controls, but do not use the  <xe:jdbcQuery> and <xe:jdbc:jdbcRowSet> (since all access is via java).

Next Post

In my next post, I will explain the way we needed to handle credentials, as well as how we extended the connection manager class to handle passed in credentials.  I will show actual code next time, I promise :)

New Blog Series: Using a DB2 Datasource in XPages

This post is the introduction of a blog series on using a DB2 backend datasource in XPages.  I will discuss how we set up the connections and give examples of different types of queries. There should be very little here specific to DB2, but for ease of writing, I will write 'DB2' throughout this series. Feel free to substitute any relational database, such as Oracle, MySQL, SQL Server, etc.

Background

Why did we choose to use a DB2 backend?  It is complicated... but the real reason we chose to use DB2 is that someone at my company believed that Notes was not secure enough for storing sensitive data.  Most everyone who reads this knows that this is far from the truth.  Notes is extremely secure and its security model is looked at as one of it biggest strengths.

When presented with this assertion, I did my best to present arguments against using DB2. I knew the security premise was wrong, and I knew it would dramatically increase project completion time. Lastly, I knew Notes was well suited for the project at hand.

Even though I fought hard for what I felt was right, I was not very disappointed when I lost the battle. The reason being that I knew that using a relational backend would be a fun learning experience and good for the career. In my last job, I had experience retrieving Oracle data through a java agent, but I never bound an XPage to relational data real time.

Around this time, I went to the MWLUG conference in Grand Rapids and heard an excellent presentation by Toby Samples on using Hibernate in Xpages. When I came back, I did my best to sell the use of Hibernate because I felt it would be a benefit, and I now had the information to make it work. Despite this, the decision was made to go with JDBC, mainly because we had already had another database with the same functionality that we could copy, and the developers who wrote it were now on the same project.

Series Contents

With that background, I am going to discuss the following topics in this blog series:

• Setting up your database to connect to DB2.  
• Using the Extension Library Relational Controls
• Using Connection Pooling
• Storing the DB2 Credentials in a managed bean
• Coding a simple SELECT statement
• Selecting information from two tables using a JOIN
• Coding an INSERT
• Coding an UPDATE
• Populating a combobox dropdown with values from a DB2 query
• Retrieving a collection of data in JSON format for using in a data table

Note: Due to a very heavy load at work, it might take me up to two months or more to finish this blog series.  

Assumptions

Throughout I am going to make the assumption that you know the basics of SQL. For me, SQL was the one thing I learned in college that I actually still use today.  Another assumption is that you know something about java, because all the coding is done using it. You can use SSJS to access DB2, but early on, we made the decision to have as much of the business logic as possible in java.

Credits

I was busy with another project while the groundwork was setup for connecting to DB2. Credit for doing this work goes to my coworker Dwain Weurfel. Dwain, thanks for helping me document and explain the setup, and  for helping me to proof these posts, so that I get it right! I should also give credit to former coworker Eric Dyer who had a large role in creating the groundwork while working on the first DB2 backend project at my company.