JDBC: Get autogenerated keys on a Oracle DB
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"); stmt.execute(); rs = stmt.getGeneratedKeys(); rs.next(); 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.