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.

Using Javascript RegExp Object to replace a XPages Contraint Validator

I had an occasion yesterday where I needed change a partial refresh to process data without validation in order to fix another problem. Despite this I still needed to validate the data the user enters based on a regular expression.  The previous developer was was using a <xp:validateContraint> to check the data, but this check would no longer trigger since I am skipping validation. The constraint validator uses a regular expression to determine whether the validation passes or not.

Even though the regular expression in this case was simplistic, I did not want to mirror what it does using string manipulation to validate it. I remembered from one of my Javascript books (Professional JavaScript for Web Developers) that Regular Expressions are built into the language.  It was very easy to duplicate the constraint functionality by just copying the RegExp string that already worked and then creating a RegExp javascript object.   

The code here lives in a button in the SSJS onclick event: 
        
var thisID = getComponent("inputID").getValue(); 
var pattern = /^([A-Za-z]{2}[A-Za-z0-9]{0,2})$/; 
        
if(!pattern.test(thisID)){ 
     return viewScope.infoMessage = "No Can Do";
} 

The built-in RegExp object can be created in one of two ways, you can either use the slash as I do in the above code or you can create it using new RegExp("....").  Either way that you choose, it works the same. The object has a method called test() which will return a true/false depending if the passed in string matches.  Of course, you can use this in any javascript, client or serverside.