Recently, using JDBC, I needed to retrieve the generated keys from a row I’d just inserted. For example you’re inserting a word into the following table and want the id:
| CREATE TABLE WORDS(ID BIGINT AUTO_INCREMENT, WORD CHAR(20)) |
Not something I’d done before, but very sane, and the solution was easily found:
| public long create(String sql) throws SQLException { | |
| try ( | |
| Connection connection = dataSource.getConnection(); | |
| Statement statement = connection.prepareStatement(sql, | |
| Statement.RETURN_GENERATED_KEYS)) { | |
| if (statement.executeUpdate() == 0) { | |
| throw new SQLException("Insert failed, no rows affected."); | |
| } | |
| try (ResultSet generatedKeys = statement.getGeneratedKeys()) { | |
| if (generatedKeys.next()) { | |
| return generatedKeys.getLong(1); | |
| } else { | |
| throw new SQLException("Creating user failed, no ID obtained."); | |
| } | |
| } | |
| } | |
| } |
This worked great in my H2 embedded database where my unit tests ran, and in the MariaDB I ran integration tests against. However when I used the code against an Oracle database … FAIL … something about a type mismatch. So what went wrong?
The issue is Oracle stuffs a RowId into every record, not because you ask it to, but because it just does. I’m sure this addition makes all sorts of Oracle magic possible. Generally one just ignores it, or at least I do, but in this case Oracle rubs your nose in its decision. How? When the code above runs against Oracle, the ResultSet for the generated keys it returns, does not contain the generated key you put in the schema, but the RowId Oracle did!
Of course there is another solution. The following, while a bit more verbose, works with all the database engines I tested:
| public long create(String sql, String[] keyColumns) throws SQLException { | |
| try ( | |
| Connection connection = dataSource.getConnection(); | |
| PreparedStatement statement = connection.prepareStatement(sql, keyColumns)) { | |
| if (statement.execute() == 0) { | |
| throw new SQLException("Insert failed, no rows affected."); | |
| } | |
| try (ResultSet generatedKeys = statement.getGeneratedKeys()) { | |
| if (generatedKeys.next()) { | |
| return generatedKeys.getLong(1); | |
| } else { | |
| throw new SQLException("Creating user failed, no ID obtained."); | |
| } | |
| } | |
| } | |
| } |
Be warned, these snippets aren’t working general solutions, but the exemplified approach is. What Oracle needs is to be told which columns were generated, otherwise it assumes the RowId, and luckily other database engines are happy to work this way too.
Worth Noting Too
JDBC offers ways to get the generated keys via both Statement and PreparedStatement. Only the PreparedStatement routes seem to work with Oracle, and the generated keys ResultSet in Oracle doesn’t seem to know the column names, only their positions (I’ll research the meta data there when I get a chance).
My name is Ozymandias 