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.

  1. try{
  2.   // set the connection not to auto commit
  3.   // (i.e. start a transaction)
  4.   db.setAutoCommit(false);
  5.  
  6.   //first create the group forum and get back its ID
  7.   Statement stitf = db.createStatement();
  8.   stitf.execute("INSERT INTO forums (type) VALUES('GROUP')");
  9.   stitf.close();
  10.   Statement stgfid = db.createStatement("SELECT last_value FROM forums_id_seq");
  11.   ResultSet rsgfid = stgfid.executeQuery();
  12.   rsgfid.next();
  13.   int forumId = rsgfid.getInt(1);
  14.   rsgfid.close();
  15.   stgfid.close();
  16.  
  17.   //then create the group
  18.   PreparedStatement psti = db.prepareStatement("INSERT INTO groups (mentor_id, name, wiki_text, anonymous, forum_id) VALUES (?, ?, ?, ?, ?)");
  19.   psti.setString(1, validatedMentorId);
  20.   psti.setString(2, validatedGroupName);
  21.   psti.setString(3, wikiContent.getEve());
  22.   psti.setBoolean(4, anonymous);
  23.   psti.setInt(5, forumId);
  24.   psti.executeUpdate();
  25.   psti.close();
  26.  
  27.   //commit the transaction
  28.   db.commit();
  29. } catch(Exception e) {
  30.   String errorMessage = "An exception occoured while trying to create a group.";
  31.   try {
  32.     db.rollback();
  33.   } catch(Exception ee) {
  34.     errorMessage += " A futher exception occoured when trying to rollback the transaction";
  35.   }
  36.   throw new Exception(errorMessage, e);
  37. }

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.

Comments

7 Responses to “Accessing Auto Increment Values from PostgreSQL with Java”

  1. Stephen McConnell on February 13th, 2008 10:11 pm

    Wouldn’t it be better to create a query like

    SELECT nextval(seq_table_id)

    and execute it before you insert and use that as the ID… The “last_value” may be executed AFTER another item has been added to the table… so the Sequence number might not be the most current even though you are encapsulated in a transaction… Unless when you start the transaction, the Database automagically reserves a block of sequence numbers for that transaction…

    I’ve been frustrated because the “getGeneratedKey()” method IS available in MySQL, but not in Postgresql, so I’ve been having to use the above method to capture unique keys associated with an Insert…

  2. Bart B on February 13th, 2008 11:13 pm

    Hi Stephen,

    The short answer is no, it’s not possible for another insert to sneak in because you do this within a transaction.

    Your way has to be done using a transaction too or you’ll run the risk of failed updates if a second query to get the next value sneaks in between your getting the value and doing your insert.

    The whole point of transactions is that all the queries that make up the transaction happen atomically, in other words, they happen as if it’s one big long query and other queries cannot sneak in between statements.

    To me it makes more sense to let the auto increment happen rather than to force in the ID yourself. That’s why I prefer to do it the way described above.

    Regards,

    Bart.

  3. Andreas Kretschmer on March 26th, 2008 5:28 pm

    You should better use currval() instead select last_value from the sequence. Within the current session is currval() multiuser-resist.
    Please read the documentation about sequences:
    http://www.postgresql.org/docs/current/static/functions-sequence.html

    Or use nextval() as suggested in the first comment.

    Regards, Andreas

  4. Java/JDBC Statment.RETURN_GENERATED_KEYS not supported ? - PG-Forum.de on March 26th, 2008 5:29 pm

    [...] 11 – 20 von ungefähr 39.100 für postgresql jdbc auto serial. (0,11 Sekunden) 1. Treffer: Accessing Auto Increment Values from PostgreSQL with Java : Bart Busschots (mit dem Hinweis, das der Weg da, ähm, unprofessionel ist, currval() existiert) Als [...]

  5. Thomas Jacobs on August 29th, 2008 3:08 pm

    Dear,

    I implemented this method to replace an other method I use normally which is to get the OID from the inserted row, and then query the primary key (autoincremented) from that row:

    long oid = ((org.postgresql.PGStatement)statement).getLastOID();
    Then a normal dB query select * from table where oid=oid.

    I had my doubts on the concurrency with the code suggested by Bart, so implemented a checking of those two strategies. I activated this method on a producten track & trace which receives a lot of gps data and inserts this in a table. Running now for about 3 hours i already saw 3 occurences of both methods producing different values.

    Haven’t looked into detail why but it proves to me that the select after the insert do not occur within an atomic transaction in the database.

    I implemented connection pooling however so this might have something to do with it.

    Thomas

  6. Vadim Shilov on January 10th, 2009 7:15 pm

    Hi Bart!
    You could probably use an INSERT statement with RETURNING clause, which will let you obtain the serial sequence number.

    See http://www.postgresql.org/docs/8.2/static/sql-insert.html

    Suppose you have an autoincrement column with serial type, you could do it like this:

    Statement stgfid = db.createStatement(“INSERT INTO forums (type) VALUES(‘GROUP’) RETURNING id”);
    ResultSet rsgfid = stgfid.executeQuery();
    rsgfid.next();
    int forumId = rsgfid.getInt(1);
    rsgfid.close();
    stgfid.close();

    Regards,
    Vadim

  7. Bart B on January 10th, 2009 7:43 pm

    Thanks Vadim!

    That looks way simpler than what I was doing!

    Bart.

Leave a Reply




Before you post a comment please remember that commenting on my blog is a privilege not a right. I won't approve comments that are obscene, offensive or insulting. For more info please read this post.

Subscribe without commenting