Using DB2 in XPages Part 5: Creating a Simple SELECT Query to Populate a Page

Now that I have finally laid the groundwork for connecting to DB2 in parts 1-4, it is time to show the fun stuff, actually using it. In this post, I will show how to populate the contents of a page using DB2 data. This example will SELECT data based on a value inputted by a user and refresh the page,

Strategy For Binding UI elements to data

When we first started using DB2 as a backend, we had each data element tied to a member of a managed bean, each with its own getter and setter. This is a fine approach, and we still use it for the older pages in our application.  An easier approach is to just bind every UI element to viewScope and update the viewScope in our java controller methods. This example will show the latter.

Company Standards

Before I begin, let me explain that my company has a few standards that must be followed for using SQL statements. The standards are that * is not allowed, and we must use prepared statements where we inject our dependencies. I don't understand the former, but completely agree with the latter. The one exception that is allowed is that the Schema value (PROD, INTG, UNIT) can be added via String addition. 

Code Example with footnotes

1 //Date converters for use in various methods 
SimpleDateFormat formatDateUI = new SimpleDateFormat("MM/dd/yyyy");  //dateformatter to convert to UI friendly format 
SimpleDateFormat formatDateDB2 = new SimpleDateFormat("yyyy-MM-dd"); //dataformatter to convert to DB2 format 

public void loadPersonInfo(String userID, String schema){ 
  2 FacesContext facesContext = FacesContext.getCurrentInstance(); 

  Map<String, Object> viewScope = facesContext.getViewRoot().getViewMap(); 
  3 XyzConnectionManager cm = new XyzConnectionManager(); 

  4 PreparedStatement ps = null; 

  ResultSet rs = null; 
  Connection c = null; 

  try { 
       // Get Connection from ConnectionManager 
       5 c = cm.getConnection(); 

       6  String sql = "Select USER_ID, FIRST_NAME, LAST_NAME, START_DATE6" + 

       "USER_BRANCH_ID, BRANCH_NAME, BRANCH_STATE, BRANCH_CITY, BRANCH_ZIP " + 
       "FROM " + schema + ".USERDETAIL01 INNER JOIN " + schema + ".USERBRANCH " + 

        "on USER_BR_ID = USER_BRANCH_ID WHERE USER_ID=?"; 

        // Create Statement Object 
        7 ps = c.prepareStatement(sql); 

        ps.setString(1, atmID); 

        // Run Query 
        boolean result = ps.execute(); 
        if (result) { 
            rs = ps.getResultSet(); 
             8  rs.next(); 

                 
            9  viewScope.put("manageFirstName", rs.getString("FIRST_NAME")); 

            viewScope.put("manageLastName", rs.getString("LAST_NAME").trim()); 
            viewScope.put("manageBranchName", rs.getString("BRANCH_NAME").trim()); 
            viewScope.put("manageBranchCity", rs.getString("BRANCH_CITY").trim()); 
            viewScope.put("manageBranchState", rs.getString("BRANCH_STATE").trim()); 
            viewScope.put("manageBranchZip", rs.getString("BRANCH_ZIP").trim());

            10 viewScope.put("manageStartDate",

            formatDateUI.format(rs.getDate("START_DATE"))); 
         } 
    } catch (SQLException e) { 
                        log.error("ERROR in loadPersonInfo: " + e.toString()); 
    } finally { 
          11 try { // close resources 

                rs.close(); 
                ps.close(); 
                c.close(); 
          } catch (SQLException e) { 
                log.error("FATAL EXCEPTION " + e.toString() + in loadPersonInfo");

                throw new RuntimeException(e); 
          } 
     } 
} 

Code Explanation using footnotes

1  - These two Date formatters are very useful for writing between XPages and DB2. The format YYYY-MM-DD is the standard date format for DB2.

2 -  We have found it necessary to get a new handle to viewScope in every method where it is used

3 -  Creates new instance of our connection manager class, see Part 3 for a detailed explanation of this class

4 - We use the PreparedStatement class to build our query, this is done to follow my company standards, and also a good practice.

5 - Creates a new connection or grabs one from the pool. This method is explained in Part 3 of this series.

6 - Our SQL statement which includes a join, and one dependency. The dependency is typically user input.

7 - Preparing our statement. This is where we insert the dependencies. The number of dependencies added here, must match exactly what is in the SQL statement. 

8 - This takes you to the first record in the result set and is necessary

9 - Populating the viewScope from the resultSet.  

10 - Using the Date Formatter explained in footnote 1

11 - The finally statement always runs, and is the recommended place to close out resources. If that operation fails then we throw the exception.

Next Post

In my next post, I will show how to populate a Combobox with the values from a DB2 Query.