Using DB2 in XPages Part 4: Setting up the Database Connection for use with SSJS

In this fourth entry in my blog series on XPages and DB2, I will explain the additional steps necessary for SSJS access, if using parameterized connection information. This post isn't intended to stand alone but build on what I discussed in part 3.  I will say this, if you decide to store your credentials in the db2.jdbc XML file then everything here in this post is unnecessary for you. If you are an XPages developer and have never had made your own custom tags, then this post might serve as a general example on how to go about doing that.

Create Custom Control for adding your Customized Connection Manager

To take advantage of our connection manager java class (described in part 3) in SSJS, we need to create a custom control for adding the connection manager to each page. For simplicity, we name it the same thing as our java class, which is XYZConnectionManager.  In order to make our own custom control, we have to create an faces-config extension file, or add to it, if we already have one. We cannot put our entry directly in the main face-config as this doesn't compile. The faces-config extension is a separate file that you create when necessary for the purpose of creating custom tags.

This file should be copied into the WEB-INF folder alongside the faces-config file using the Package Explorer.

Here is what the contents of the file look like.

<faces-config> 
  <faces-config-extension> 
     1<namespace-uri>http://www.xyzCompany.org/xpages/component</namespace-uri> 
     2<default-prefix>xyz</default-prefix> 
  </faces-config-extension> 
  <component> 
     <display-name>XYZ Connection Manager</display-name> 
   3<component-type>com.ibm.xsp.extlib.relational.jdbc.JdbcConnectionManager</component-type> 
   <component-class>org.xyz.atm.util.XyzConnectionManager</component-class> 
   <component-extension> 
     <base-component>com.ibm.xsp.extlib.relational.jdbc.JdbcConnectionManager</base-component> 
      4<tag-name>XyzConnectionManager</tag-name> 
      <designer-extension> 
      <category>XYZ Custom</category> 
      <generate-id>true</generate-id> 
      5<in-palette>true</in-palette> 
      <visible>true</visible> 
      </designer-extension> 
    </component-extension> 
    </component> 
</faces-config> 

Essentially what you are doing here is creating your own tag here that you can add to any page.  In this case, you will get access to the relational controls for whatever page in which you add the custom tag.  This would replace adding the built-in JDBCConnectionManager to your page.

Here is a explanation of key parts of this file:

1 - The namespace URI needs to only be a unique value.  It does not need to resolve to a real URL, just as the XSP namespace does not resolve to a real URL.

2 - This is the prefix we chose for our tag. This is analogous to "xp" for core controls, or "xe" for extension library controls.

3 - This maps the control to the java class we want to use

4 - This is the name of the tag that we show in our code

5 - Setting this to true, make the control show up in our palette where we can drag it onto a page

Sample Usage:
1) Include the new name space in your xpView tag at the very top
xmlns:xyz="http://www.xyzcompany.org/xpages/component"

2) Drag your control on the page using the palette or type it in the source. 
<xyz:XyzConnectionManager id="XyzConnectionManager1"></xyz:XyzConnectionManager> 

You will want to put this tag on any page that uses the relational controls, or put this in your application layout once, which makes it available on any page.

Known Issue:
For some reason, Designer often "forgets" about the faces-extension file and creates an unknown tag error on every page where it is used.  If you go and edit something in the faces-extension file and save the "change" (without really changing anything) and the clean your application the errors will go away. It sometimes takes two cleans.

Next Post

In my next post, I will finally get to the part where I can show how we use DB2 as our backend datastore. I will start with how to write a simple SELECT statement to populate a page. 

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 :)