Using DB2 in XPages Part 7: Inserting a New Record into DB2

My apologies for the long break in my blog series. The project that this is the basis for this blog series is wrapping up which is causing work to be extremely busy. 

Before I get started I am excited to report that I will be co-presenting a session at the MWLUG conference in August titled Real World Experience: Integrating DB2 and XPages. The session will cover some of the material in this blog series as well a much more. I will be presenting with my coworker Dwain Wuerfel who has worked on two separate XPages projects using a DB2 backend. Come see us in Atlanta this August 19-21.

Now onto this post in which I will cover how to Insert a new record into DB2.

Example Overview

The code to perform an INSERT is fairly straightforward. However I personally find The INSERT statement in SQL to be somewhat awkward because of the way it must contain a VALUES statement that pairs with the column values that precede it. I find it easy to get values mixed up especially when you have a multitude of columns.

After you execute an INSERT query, it does not return a boolean success or failure like the SELECT. It simply returns an integer of how many rows were updated. A return value of zero would indicate that something went wrong. 

Code Usage

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

var insertResult = appBean.insertExample(keywordBean.getString("SCHEMA"));

Code Example with footnotes

public String insertExample(String schema){ 
    1 XLog log = new XLog("XYZ Bean", .....); 

    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(); 

    try{ 
          4 c = cm.getConnection(); 

          String sql = "INSERT INTO " + schema + ".PERSON_TABLE " + 
          "(FIRST_NAME, LAST_NAME, AGE, BIG_BAG_OF_NOTHING, BIRTHDAY)" + //1,2,3,4,5 
          "VALUES (?, ?, ?, ?, ?)"; 

          ps = c.prepareStatement(sql); 
                
          5 ps.setString(1, (String) viewScope.get("userFirstName")); 

          ps.setString(2, (Long) viewScope.get("userLastName")); 
          6 ps.setLong(3, (Long) viewScope.get("userAge")); 

          7 ps.setNull(4, java.sql.Types.VARCHAR); 

          8 ps.setDate(5, (Date) viewScope.get("userDOB")); 

                        
          int result = ps.executeUpdate(); 

          if(result > 0){ 
                returnValue = "Success"; 
          }else { 
                returnValue = "Failure"; 
          } 
        
      } catch(Exception e) { 
          log.error("EXCEPTION in insertATMInfo: " + e.toString());

      } finally { 
          9 try { // close resources 

              ps.close();                                 
              c.close(); 
          } catch (SQLException e) { 
              log.error("FATAL EXCEPTION in closing out resources " + e.toString()); 
             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 -  Creates a new connection or grabs one from the pool. This method is explained in Part 3 of this series.

5 - The setString method of the Prepared Statement accepts a String and writes a VARCHAR or a LONGVARCHAR depending on the size of the String

6 - The setLong method of the Prepared Statement accepts a long  and writes a BIGINT into DB2.

7 - The setNull method of the Prepared Statement is useful for writing null to a column that is set to accept a null.  You have to include the type of value so that the method knows which type of null to write.

8 - The setDate method of the Prepared Statement accepts any kinds of Date (util or sql) and writes it to a Sql DATE type.

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.