Support for getting generated keys.

From: Junyan Luo <jzl106(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Support for getting generated keys.
Date: 2009-09-25 15:37:02
Message-ID: bc5f338c0909250837i25e01b1aqba352c23da1e9f3d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2009-09-25 15:37:23 Re: JDBC 4 support
Previous Message Steve Ebersole 2009-09-25 15:15:39 Re: JDBC 4 support