Using DB2 in XPages Part 6: Populating a Combobox dropdown with relational data

In the last post I showed how to perform a simple SELECT statement to populate a page. In this post I will show how to use a SELECT query to populate the contents of a ComboBox.  

Example Overview 

This example will loop through a result set, and create a SelectItem object with a value for 'label' and a value for 'value'. This allows choices that are displayed to a user in which to choose from, that are different from what is actually stored when we insert this value back into DB2.

This is similar to how, back in the day we would code in the Notes client with a label and alias separated with a pipe character (|). When the value is saved, the only thing we will save is the SelectItem value. This is because the value serves as a foreign key in a different table than what we select from in this query. 

Code Usage

I think it would  help to show first, how and where this is used. The method is called in the Values property of the ComboBox. One issue with this property in general that I find very annoying is that the UI gives the impression that you can have one formula for Label, and one for Value, but in fact you can only have one for both. The Formula Item code is a single line of SSJS that calls the java method below:

appBean.getBranchName(keywordBean.getString("SCHEMA"));

The code is added to the ComboBox Values as a Formula Item

Code Example with footnotes

public List<SelectItem> getBranchName(String schema){ 
            
1 XYZConnectionManager cm = new XYZConnectionManager(); 
 PreparedStatement ps = null; 
 ResultSet rs = null; 
 Connection c = null; 
2 List<SelectItem> options = new ArrayList<SelectItem>(); 

 try { 
      // Get Connection from ConnectionManager 
    3 c = cm.getConnection(); 
      String sql = "Select CB_NM, CB_ID FROM " + schema + ".BRANCH01"; 

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

      // Run Query 
      boolean result = ps.execute(); 
      if (result) { 
          rs = ps.getResultSet(); 
          while (rs.next()) {                 
            4 SelectItem option = new SelectItem(); 
              option.setLabel(rs.getString("CB_NM").trim()); 
              option.setValue(rs.getString("CB_ID").trim()); 
            5 options.add(option); 
              } 
          } 
       } catch(Exception e) { 
       log.error("EXCEPTION in getBranchName(): " + e.toString());                         
       } finally { 
         6 try { // close resources 
              ps.close(); 
              rs.close(); 
              c.close(); 
           } catch (SQLException e) { 
                log.error("FATAL EXCEPTION in getBranchName(): " + e.toString()); 
                throw new RuntimeException(e); 
           } 
       } 
       return options; 
} 

Code Explanation using footnotes

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

2 -  Creates a new ArrayList and stores it as a List, the superclass of ArrayList. The angle brackets tell the compiler what type of Objects are to be stored in the collection.

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

4 - Inside the while loop, it creates an new SelectItem object for each row in the result set.  The SelectItem object is a JSF object that represents a value that the user can select.

5 - Populates the ArrayList with the SelectItem object

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

Attribution

The basis for the code here is taken from this Stack Overflow question answered by Mark Leusink. Thanks Mark!

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.