title: How to retrieve previously auto-generated PK ID value using JDBC and HSQLDB
body: <p>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 <code>id</code> (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:</p>
  
  <p>Say there's a <code>Customer</code> table that has a <code>PersonId</code> field with a FK constraint referring to a row from a <code>Person</code> table. I want to create a new <code>Customer</code>, but to do this I need to first create a new <code>Person</code> and use the new <code>Person.id</code> value to set <code>Customer.PersonId</code>.</p>
  
  <p>I've seen four ways to approach this:</p>
  
  <ol>
  <li><p>Insert the <code>Person</code> row setting the <code>id</code> field to <code>null</code>. HSQLDB generates the next <code>id</code> value automatically. Then perform a query on the <code>Person</code> table to get the <code>id</code> value just created and use it to create the new <code>Customer</code> row.</p>
  
  <p>This seems expensive just to retrieve a single integer value.</p></li>
  <li><p>Get the next <code>id</code> value in the <code>Person</code> table and use it in the <code>INSERT</code> statement to set the <code>Person.id</code> value manually. Use the same <code>id</code> value to set <code>Customer.PersonId</code>. No subsequent read from the DB is needed.</p>
  
  <p>Inconsistencies may arise if an <code>id</code> value is obtained, but another connection performs an <code>INSERT</code> in the table before my <code>INSERT INTO Person...</code> statement is executed.</p></li>
  <li><p>Execute the <code>INSERT</code> statement, as in option 1 above, setting <code>id=null</code> to allow auto-generation. Then use the <code>getGeneratedKeys</code> method to retrieve keys generated in last statement.</p>
  
  <p>I thought this sounded like a good option, but I couldn't get it to work. Here's a snippet of my code:</p>
  
  <pre><code>// PreparedStatement prepared previously...
  preparedStatement.executeUpdate();
  ResultSet genKeys = preparedStatement.getGeneratedKeys();
  int id;
  if (genKeys.next()) {
      id = genKeys.getInt(1);
  }
  // Finish up method...
  </code></pre>
  
  <p>This code was returning an empty <code>ResultSet</code> for <code>genKeys</code>. Am I using the <code>getGeneratedKeys</code> method incorrectly? If I could get this to work, this might be the way to go.</p></li>
  <li><p>Again, execute the <code>INSERT</code> statement allowing for auto-generated <code>id</code>. Then immediately execute <code>CALL IDENTITY()</code> to retrieve the last <code>id</code> value generated by the connection (as explained <a href="http://hsqldb.org/doc/guide/ch02.html#N104B3" rel="nofollow noreferrer">here</a> and mentioned in <a href="https://stackoverflow.com/questions/10353405/how-to-return-last-inserted-auto-incremented-row-id-in-hsql">this</a> SO question).</p>
  
  <p>This also seems like a reasonable option, even though I must perform an additional <code>executeQuery</code>. On the positive side, I was actually able to get it to work with the following code:</p>
  
  <pre><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...
  </code></pre></li>
  </ol>
  
  <p>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 <code>id</code> that already exists).</p>
  
  <p>This seems like a basic question (and essential), but I couldn't find much guidance on the best way to do it. Thanks.</p>
  
  <p><hr/>
  EDIT:
  I just found <a href="https://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc/1915197#1915197">this</a> question that discusses my option 3. According to the accepted answer, it appears I was leaving out the <code>Statement.RETURN_GENERATED_KEYS</code> parameter needed to enable that functionality. I didn't show the <code>prepareStatement</code> method in my code snippet, but I was using the single parameter version. I need to retry using the overloaded, two-parameter version.</p>
  
  <p>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.</p>