How to retrieve previously auto-generated PK ID value using JDBC and HSQLDB

Question

I'm working with JDBC and HSQLDB 2.2.9. What's the most efficient and accurate way to insert a new row into a DB and, subsequently, retain its id (PK set to autoincrement) value? The reason I need to do this is probably pretty obvious, but I'll illustrate with an example for discussion:

Say there's a Customer table that has a PersonId field with a FK constraint referring to a row from a Person table. I want to create a new Customer, but to do this I need to first create a new Person and use the new Person.id value to set Customer.PersonId.

I've seen four ways to approach this:

1. Insert the Person row setting the id field to null. HSQLDB generates the next id value automatically. Then perform a query on the Person table to get the id value just created and use it to create the new Customer row.

   This seems expensive just to retrieve a single integer value.

2. Get the next id value in the Person table and use it in the INSERT statement to set the Person.id value manually. Use the same id value to set Customer.PersonId. No subsequent read from the DB is needed.

   Inconsistencies may arise if an id value is obtained, but another connection performs an INSERT in the table before my INSERT INTO Person... statement is executed.

3. Execute the INSERT statement, as in option 1 above, setting id=null to allow auto-generation. Then use the getGeneratedKeys method to retrieve keys generated in last statement.

   I thought this sounded like a good option, but I couldn't get it to work. Here's a snippet of my code:

   // PreparedStatement prepared previously...
   preparedStatement.executeUpdate();
   ResultSet genKeys = preparedStatement.getGeneratedKeys();
   int id;
   if (genKeys.next()) {
       id = genKeys.getInt(1);
   }
   // Finish up method...
   

   This code was returning an empty ResultSet for genKeys. Am I using the getGeneratedKeys method incorrectly? If I could get this to work, this might be the way to go.

4. Again, execute the INSERT statement allowing for auto-generated id. Then immediately execute CALL IDENTITY() to retrieve the last id value generated by the connection (as explained here and mentioned in this SO question).

   This also seems like a reasonable option, even though I must perform an additional executeQuery. On the positive side, I was actually able to get it to work with the following code:

   // INSERT statement executed here...
   statement = connection.createStatement();
   ResultSet rs = statement.executeQuery("CALL IDENTITY();");
   int id;
   if (rs.next()) id = rs.getInt(1);
   // Finish up method...
   

So, in summary, the first two options I'm not crazy about. The second two seem ok, but I could only get option 4 to work. Which option is preferred and why? If option 3 is the best, what am I doing wrong? Also, is there a better way that I haven't mentioned? I know words like 'better' can be subjective, but I'm working with a simple DB and want the most direct solution that doesn't open up the DB to possible inconsistencies and doesn't increase the transaction failure rate (due to trying to create a record with an id that already exists).

This seems like a basic question (and essential), but I couldn't find much guidance on the best way to do it. Thanks.

---

EDIT: I just found this question that discusses my option 3. According to the accepted answer, it appears I was leaving out the Statement.RETURN_GENERATED_KEYS parameter needed to enable that functionality. I didn't show the prepareStatement method in my code snippet, but I was using the single parameter version. I need to retry using the overloaded, two-parameter version.

There are also a few other SO questions which show up with that question that are closly related to my question. So, I guess mine could be considered a duplicate (not sure how I missed those other questions before). But I'd still like any guidance on whether one solution is considered better than the others. For now, if I get option 3 to work, I'll probably go with that.

Answer 1

I don't have enough reputation to comment on neizan's answer, but here's how I solved the same problem:

• The column looked like an ID column, but it wasn't defined as IDENTITY;
• As said above, you need to specify RETURN_GENERATED_KEYS.
• It looks like if you execute 2 INSERT in sequence, the second one won't return the generated keys. Use "CALL IDENTITY()" instead.

Example using HSQLDB 2.2.9:

CREATE TABLE MY_TABLE (
 ID INTEGER IDENTITY,
 NAME VARCHAR(30)
)

Then in Java:

PreparedStatement result = cnx.prepareStatement(
    "INSERT INTO MY_TABLE(ID, NAME) VALUES(NULL, 'TOM');",
    RETURN_GENERATED_KEYS);
int updated = result.executeUpdate();
if (updated == 1) {
    ResultSet generatedKeys = result.getGeneratedKeys();
    if (generatedKeys.next()) {
        int key = generatedKeys.getInt(1);
    }
}