It’s not uncommon that as part of a complex transaction you need to insert a row into a table with an auto-incrementing primary key and then use the key from the row you just generated as a foreign key in another insert within the same transaction. Java provides a mechanism to return the auto-generated keys from an insert query without the need to execute a second query by means of the function java.sql.Statement.getGeneratedKeys(). Unfortunately the PostgreSQL JDBC connector does not appear to support this feature (at least the version I am using does not). Given the fact that we do not have this functionality available to us when using a PostgreSQL database we need to use a separate query to retreive the value of the key we just generated. It goes without saying that we need to do this two-step process as part of a transaction but it may not go without saying that the correct way to extract the value for the key is by querying the sequence that the key is being generated from and not by querying the table directly. It is true that most of the time the highest value of the incrementing variable will be the value just inserted but that is not a guarantee.

The code to do any database processing in Java always gets long and convoluted. The following is code which has been adapted for a project I’m working on at the moment. I’ve simplified the code to remove distractions from the database logic. Please note that in the code below db is a database connection of type java.sql.Connection. The code shows the generation of a group which requires that a forum be created first and that the ID of that newly created group be included in the query to insert the group into the database.

try{
  // set the connection not to auto commit 
  // (i.e. start a transaction)
  db.setAutoCommit(false);

  //first create the group forum and get back its ID
  Statement stitf = db.createStatement();
  stitf.execute("INSERT INTO forums (type) VALUES('GROUP')");
  stitf.close();
  Statement stgfid = db.createStatement("SELECT last_value FROM forums_id_seq");
  ResultSet rsgfid = stgfid.executeQuery();
  rsgfid.next();
  int forumId = rsgfid.getInt(1);
  rsgfid.close();
  stgfid.close();

  //then create the group
  PreparedStatement psti = db.prepareStatement("INSERT INTO groups (mentor_id, name, wiki_text, anonymous, forum_id) VALUES (?, ?, ?, ?, ?)");
  psti.setString(1, validatedMentorId);
  psti.setString(2, validatedGroupName);
  psti.setString(3, wikiContent.getEve());
  psti.setBoolean(4, anonymous);
  psti.setInt(5, forumId);
  psti.executeUpdate();
  psti.close();

  //commit the transaction
  db.commit();
} catch(Exception e) {
  String errorMessage = "An exception occoured while trying to create a group.";
  try {
    db.rollback();
  } catch(Exception ee) {
    errorMessage += " A futher exception occoured when trying to rollback the transaction";
  }
  throw new Exception(errorMessage, e);
}

The important things to notice are the following:

  1. All the activity takes place within a transaction.
  2. If anything goes wrong the transaction is rolled back within the catch block.
  3. The transaction is committed at the end of the try block. If you leave the commit statement out your transaction will never actually appear in the DB!
  4. We retrieved the value of the auto incrementing field (forums(id)) by selecting last_value from the sequence which PostgreSQL automatically generates to go with a field of type SERIAL (i.e. an auto incrementing field). The name of this sequence can be trivially determined from the name of the table and the name of the column. It takes the following form: [tableName]_[columnName]_seq. In the sample code above the table name was forums and the column name was id, hence the sequence being queried being forums_id_seq.