50 DB2 Nuggets #30 : Tech Tip - Using getDBGeneratedKeys method with Batch inserts in a JDBC application.

Body

From DB2 V9.7 Batched INSERT statements can return automatically generated keys.
If batch execution of a PreparedStatement object returns automatically generated keys, you can call the DB2PreparedStatement.getDBGeneratedKeys
method to retrieve an array of ResultSet objects that contains the automatically generated keys.

Recently I had a customer trying to use getDBGeneratedKeys method with Batch inserts in a JDBC application using JCC driver and was looking for an example.

Here is an example of how to use the getDBGeneratedKeys method in a sample Java program:

1) Create a table with an auto-generated identity column:

"CREATE TABLE EMP_PHONE (EMPNO CHAR(6), PHONENO CHAR(4), IDENTCOL INTEGER GENERATED ALWAYS AS IDENTITY)"

2) Modify the connection URL in the below program with your hostname, portnumber, dbname, userID and pwd.

Now save the program as testkeys.java.

import java.sql.*;
import com.ibm.db2.jcc.*;
public class testKeys
{
public static void main(String[] args)
{
    
try {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
ResultSet rs;
String url = "jdbc:db2://<hostname>:<portnumber>/<dbname>"+";";
    Connection con = DriverManager.getConnection(url,"<userID>","<PWD>");
    System.out.println("Connected");
 
  PreparedStatement ps = con.prepareStatement("INSERT INTO EMP_PHONE (EMPNO, PHONENO) " + "VALUES (?,?)",Statement.RETURN_GENERATED_KEYS);
  ps.setString(1,"000030");                                 
  ps.setString(2,"4444");
  ps.addBatch();                                         
  ps.setString(1,"000040");                          
  ps.setString(2,"6666");
  ps.addBatch();                                  
  int [] numUpdates=ps.executeBatch();
               
  for (int i=0; i < numUpdates.length; i++) {        
    if (numUpdates[i] == -2 )
      System.out.println("Execution " + i + ": unknown number of rows updated");
    else
      System.out.println("Execution " + i + "successful: " + numUpdates[i] + " rows updated");
  }
                                   
 ResultSet[] resultList = ((DB2PreparedStatement)ps).getDBGeneratedKeys();  
 if (resultList.length != 0)
  {
    for (int i = 0; i < resultList.length; i++)
    {
        while (resultList[i].next())
        {
          System.out.println(i);
          int idColVar = resultList[i].getInt(1);
          System.out.println("Automatically generated key value = " + idColVar);
        }
    }
  }
  else {
    System.out.println("Error retrieving automatically generated keys");
  }
} catch(Exception e) {                     
  // process BatchUpdateException

3) To Compile:  javac testKeys.java

4) To Run:  java testKeys

The output will be:

C:\> java testKeys
Connected
Execution 0successful: 1 rows updated
Execution 1successful: 1 rows updated
0
Automatically generated key value = 1
1
Automatically generated key value = 2

5) Select from the table to view the auto generated identity columns:

C:\>db2 "select * from emp_phone"

EMPNO  PHONENO IDENTCOL
------ ------- -----------
000030 4444              1
000040 6666              2

  2 record(s) selected.

Thanks!