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!