Обсуждение: Support for getting generated keys.

Although driver 8.4 is supposed to support getting generated keys,
this issue still remains quite unclear for most people (including me)
due to the lack of documentation. I have explored older threads for a
while and figured out the current workaround and here is my findings.
Please point out any mistake I made:
1. For a Statement, you MUST use the "RETURNING" clause in a "INSERT"
statement. The code to get generated keys will be like:
       ResultSet keys = Statement.executeQuery("INSERT...RETURNING [id
column]"); // Make sure it's executeQuery() not executeUpdate()
       keys.next();  //The result set contains generated keys;
       int key = keys.getInt(1);
However, Statement.getGeneratedKeys() does NOT work for Statement (Am
I correct?)
2. For a PreparedStatement, you can EITHER use the approach above, OR
use Statement.getGeneratedKeys(), and in the latter case you have to
make sure you use Connection.prepareStatement(sql, new String[]{"id
column"}) to indicate the NAME of your id column. Setting id column by
indexes are not supported;
3. Unfortunately, batch update is still not supported in EITHER case.
I tried to use a preparedStatement created with a id column in a batch
update situation, and it always give me this exception:
       java.sql.BatchUpdateException: Batch entry 0 INSERT INTO
test.keytest (name) VALUES ('my name') RETURNING "id" was aborted.
Am I missing anything? Thanks!

REGARDS,
Junyan

On Fri, 25 Sep 2009, Junyan Luo wrote:

> Although driver 8.4 is supposed to support getting generated keys,
> this issue still remains quite unclear for most people (including me)
> due to the lack of documentation. I have explored older threads for a
> while and figured out the current workaround and here is my findings.
> Please point out any mistake I made:
>
> 1. For a Statement, you MUST use the "RETURNING" clause in a "INSERT"
> statement. The code to get generated keys will be like:
>       ResultSet keys = Statement.executeQuery("INSERT...RETURNING [id
> column]"); // Make sure it's executeQuery() not executeUpdate()
>       keys.next();  //The result set contains generated keys;
>       int key = keys.getInt(1);
> However, Statement.getGeneratedKeys() does NOT work for Statement (Am
> I correct?)

You don't need to use RETURNING and executeQuery.  You can use a plain
insert statement and executeUpdate(sql, new String[]{"id"}) and the
results will be available via getGeneratedKeys.

Kris Jurka