Using DB2 in XPages Part 8: Updating an Existing Record into DB2

As this blog series is winding down, in this second to last post, I will give an example for performing an UPDATE to an existing DB2 record.

Example Overview

The UPDATE SQL is very straightforward. In my opinion, the UPDATE statement makes much more sense that the INSERT statement. 

For updates, you only need to update the columns that you want changed. You do not need to include columns that you don't want to be updated. Of course, you need to have the correct DB2 permissions to perform updates. For updating a specific record, the WHERE is required.  

Code Usage

This code is intended to be called upon an XPages event such as the onClick of a button, or from another java method,  You would keep this in your controller managed bean and call it like this:

var updateResult = appBean.updatePerson(viewScope.personID, keywordBean.getString("SCHEMA"));

Code Example with footnotes

public String updatePerson(String personID, String schema){ 
     1 XLog log = new XLog("Update Person" ....); 
     2 XyzConnectionManager cm = new XyzConnectionManager(); 
     PreparedStatement ps= null;         
     Connection c = null; 

     String returnValue = ""; 

     3 FacesContext facesContext = FacesContext.getCurrentInstance(); 
     Map<String, Object> viewScope = facesContext.getViewRoot().getViewMap(); 

     4 SimpleDateFormat formatDateDB2 = new SimpleDateFormat("yyyy-MM-dd"); //to DB2 format 

     try{ 
         5 c = cm.getConnection(); 

         //SQL for Person Table UPDATE 
         String sql = "UPDATE " + schema + ".PERSON_TABLE SET PERSON_ID = ?, " + 
         PERSON_AGE = ?, PERSON_NICKNAME = ?, PERSON_FAV_COLOR = ?, " +
         PERSON_DOB = ? WHERE PERSON_ID = ?";
                
         try{ 
           6 java.util.Date DOB_Date = formatDateDB2.parse((String)viewScope.get("DOB_UI")); 
         } catch (ParseException e){ 
            //maybe log parse error here
         } 
                                        
         ps = c.prepareStatement(sql);  //prepared statement for detail table 
         ps.setString(1, (String) viewScope.get("personID")); 
         ps.setLong(2, (Long) viewScope.get(personAge")); 
         ps.setString(3, (String) viewScope.get("personNickname")); 
         ps.setString(4, (String) viewScope.get("personFavoriteColor")); 
         ps.setDate(5, DOB_Date); 

         int result = ps.executeUpdate(); 
         7 if(result > 0){ 
            8 c.commit(); 
            returnValue = "Success"; 
         }else{ 
            returnValue = "Failure"; 
         } 
    }

    catch(Exception e) { 
    log.error("EXCEPTION in updatePerson: " + e.toString());  
    } 
    finally { 
        try { // close resources 
        c.close(); 
        } catch (SQLException e) { 
           log.error("EXCEPTION in closing out resources in updatePerson: " + e.toString()); 
           9 throw new RuntimeException(e); 
          } 
        } 
     10 return returnValue; 
} 

Code Explanation using footnotes

1  - Creates new instance of our logging utility. We had problems creating a single instance, so we resorted to creating a new instance each time.

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

3 -  Gets a handle to the viewScope in which the UI elements are bound.

4 -  Converts the date to a DB2 friendly date. Later I found out that you don't need to do this, as the setDate() method of the PreparedStatement class will do this for you. Early on in the project, the DBA set all columns to "Not Null" so we had to put a dummy date of "9999-12-31" in column.  We used this date converter which we thought we needed.  Bottom line here: Don't let your DBA get away with doing that unless you really need it. Know what you need the table to look like prior to starting coding.

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

6 - Parse and format the date.  This step is not necessary, but I left this to make the point I made in Footnote #4.  Come to our MWLUG session in Atlanta to hear more about lessons such as this one that we learned while working on this project.

7 - An Integer with the number of rows updated is returned. To check for success, we check whether the returned integer is greater than one.

8 - Our table (DB2 view actually) is set so that we have to manually commit on updates. Commit means to make the update persistent.

9 - The finally statement always runs, and is the recommended place to close out resources. If that operation fails then we throw the exception. There is some debate on whether it is best to throw an exception at this point.  

10 - Lastly we return back a String of success or failure. Error Handling is done in the calling method.