Updating data in tables using the PreparedStatement.executeUpdate method

The Statement.executeUpdate method works if you update data server tables with constant values. However, updates often need to involve passing values in variables to the tables. To do that, you use the PreparedStatement.executeUpdate method.

About this task

With the IBM® Data Server Driver for JDBC and SQLJ, you can also use PreparedStatement.executeUpdate to call stored procedures that have input parameters and no output parameters, and that return no result sets.

DB2® for z/OS® does not support dynamic execution of the CALL statement. For calls to stored procedures that are on DB2 for z/OS data sources, the parameters can be parameter markers or literals, but not expressions. The following types of literals are supported:
  • Integer
  • Double
  • Decimal
  • Character
  • Hexadecimal
  • Graphic

For calls to stored procedures that are on IBM Informix® data sources, the PreparedStatement object can be a CALL statement or an EXECUTE PROCEDURE statement.

When you execute an SQL statement many times, you can get better performance by creating the SQL statement as a PreparedStatement.

For example, the following UPDATE statement lets you update the employee table for only one phone number and one employee number: 
UPDATE EMPLOYEE SET PHONENO='4657' WHERE EMPNO='000010'
Suppose that you want to generalize the operation to update the employee table for any set of phone numbers and employee numbers. You need to replace the constant phone number and employee number with variables: 
UPDATE EMPLOYEE SET PHONENO=? WHERE EMPNO=?
Variables of this form are called parameter markers.

Procedure

To execute an SQL statement with parameter markers, you need to perform these steps:

  1. Invoke the Connection.prepareStatement method to create a PreparedStatement object.
  2. Invoke the PreparedStatement.setXXX methods to pass values to the input variables.

    This step assumes that you use standard parameter markers. Alternatively, if you use named parameter markers, you useIBM Data Server Driver for JDBC and SQLJ-only methods to pass values to the input parameters.

  3. Invoke the PreparedStatement.executeUpdate method to update the table with the variable values.
  4. Invoke the PreparedStatement.close method to close the PreparedStatement object when you have finished using that object.

Example

The following code performs the previous steps to update the phone number to '4657' for the employee with employee number '000010'. The numbers to the right of selected statements correspond to the previously-described steps.
Figure 1. Using PreparedStatement.executeUpdate for an SQL statement with parameter markers
Connection con;
PreparedStatement pstmt;
int numUpd;
…
pstmt = con.prepareStatement(
  "UPDATE EMPLOYEE SET PHONENO=? WHERE EMPNO=?"); 
                                  // Create a PreparedStatement object        1 
pstmt.setString(1,"4657");        // Assign first value to first parameter    2 
pstmt.setString(2,"000010");      // Assign first value to second parameter 
numUpd = pstmt.executeUpdate();   // Perform first update                     3 
pstmt.setString(1,"4658");        // Assign second value to first parameter   
pstmt.setString(2,"000020");      // Assign second value to second parameter 
numUpd = pstmt.executeUpdate();   // Perform second update                 
pstmt.close();                    // Close the PreparedStatement object       4 

You can also use the PreparedStatement.executeUpdate method for statements that have no parameter markers. The steps for executing a PreparedStatement object with no parameter markers are similar to executing a PreparedStatement object with parameter markers, except you skip step 2. The following example demonstrates these steps.

Figure 2. Using PreparedStatement.executeUpdate for an SQL statement without parameter markers
Connection con;
PreparedStatement pstmt;
int numUpd;
…
pstmt = con.prepareStatement(
  "UPDATE EMPLOYEE SET PHONENO='4657' WHERE EMPNO='000010'"); 
                                  // Create a PreparedStatement object   1 
numUpd = pstmt.executeUpdate();   // Perform the update                  3 
pstmt.close();                    // Close the PreparedStatement object  4