JDBC: Get autogenerated keys on a Oracle DB

October 9, 2007 by Michael

With the current and latest Oracle JDBC Drivers it’s possible to retrieve one automatically generated key based on a sequence or any other arbitrary value (autogenerated-keys or identity columns in other databases).

Certainly it isn’t as simple as just use using Statement.html#getGeneratedKeys() as it simply returns an emtpy resultset.

After browsing around, i saw that java.sql.Connection can prepare a statement with a flag to return generated keys like so:

connection.prepareStatement("INSERT INTO FOOBAR VALUES(id.nextval, 1), Statement.RETURN_GENERATED_KEYS);

(By the way, it’s always a good idea to use prepared statements from a performance point of view as they can be reused)

Anyway, it wouldn’t be an Oracle product if something is different like any other products and i still was left alone in the dark with an empty result set.

Follow the path to enlightment:

final String sql = "INSERT INTO foobar(id, b) VALUES (id.nextval, ?)";
stmt = connection.prepareStatement(sql,new String[]{"ID"});			
stmt.setString(1, "bar");
rs = stmt.getGeneratedKeys();
rv = rs.getInt(1);

Telling oracle which column contains the generated value does the trick. It’s really well hidden on their website. Be aware, you cannot refer to the returned keys by name, you need to address them 1 based.

Be aware that this doesn’t work inside a 9.2.x.x or 10.2.x.x Oracle Database as a Java Stored Procedure. Either the driver isn’t JDBC 3 (9.2) or the methods are not supported (10.2). You can work around this problem with 2 statements, first select id.nextval from somewhere, then execute your insert. Lame, but i didn’t find any other solution.

Ran on the client side with the latest JDBC from Oracle on the other hand works just fine.

No comments yet

One Trackback/Pingback
  1. […] Some months ago i wrote about retrieving auto generated values with JDBC from an Oracle Database: JDBC: Get autogenerated keys on a Oracle DB. […]

Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *