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
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.
