Getting id after insert within a transaction (Oracle)
Solution 1
The Oracle JDBC Driver does not support getGeneratedKeys()
- you are manually generating the keys in your trigger, presumably from a SEQUENCE
.
You can use Oracle's returning clause:
String query = "BEGIN INSERT INTO team (name) values (?) returning id into ?; END;";
CallableStatement cs = conn.prepareCall(query);
cs.setString(1, "cougars");
cs.registerOutParameter(2, OracleTypes.NUMBER);
cs.execute();
System.out.println(cs.getInt(2));
Or grab the last sequence number with a second SQL query:
SELECT mysequence.CURRVAL FROM dual
Solution 2
You need to tell the driver which column to return.
If your ID is populated by a trigger the following will work:
sql = "INSERT INTO team (name) values (?)";
// pass an array of column names to be returned by the driver instead of the int value
// this assumes the column is named ID (I think it has to be all uppercase)
myInsert = c.prepareStatement(sql, new String[]{"ID"});
myInsert.setString(1, "cougars");
int affectedRows = memoInsert.executeUpdate();
String teamId;
ResultSet generatedKeys = myInsert.getGeneratedKeys();
if (generatedKeys.next()) {
teamId = generatedKeys.getString(1);
}
Solution 3
Please refer to Statement.getGeneratedKeys() - it gives you back a resultset of generated keys.I believe this is what you seek.
Basically, Spring jdbc uses this approach to retrieve generated ids (example from JdbcTemplate class)
A less elegant solution would be to use Oracle's RETURNING clause , but you'll have to wrap you insert into a stored proc to get back the id
Related videos on Youtube
latj
Updated on June 08, 2022Comments
-
latj almost 2 years
Let's say I have three tables: team, player, team_player. Table team_player is a bridge table allowing a "many to many" relationship.
When someone wants to create a new team, they specify the initial players on that team.
How do I insert both the team and team_player rows in the same transaction? That is, I'd like to insert all the team_player records before committing to the new team row. I am using JDBC and Oracle.
When I try the code below, teamId is filled with a string of letters even though team.id is a number (that is incremented by a trigger). So, this does not seem to be the id of the record which I just tried to insert (but didnt commit to yet).
c = DB.getConnection(); c.setAutoCommit(false); sql = "INSERT INTO team (name) values (?)"; myInsert = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); myInsert.setString(1, "cougars"); int affectedRows = memoInsert.executeUpdate(); String teamId; ResultSet generatedKeys = myInsert.getGeneratedKeys(); if (generatedKeys.next()) { teamId = generatedKeys.getString(1); } // ...loop through players inserting each player and team.id into team_player // c.commit();
This is where I read about RETURN_GENERATED_KEYS: How to get the insert ID in JDBC?
-
latj almost 11 yearsYeah, I'm using getGeneratedKeys() in the code example in my question. The problem is it is returning something that is not the id. It is a string of letters. My id field is a number.
-
diy almost 11 yearsIt is returning you the ROWID . getGeneratedKeys documentation says that if column representing id is not specified, then driver decides it for you - apparently, for Oracle it would be ROWID
-
latj almost 11 yearsThanks for the help; Should I replace :var with a variable name? If I leave ":var" as is I get an error about missing IN or OUT parameter. If I replace ":var" with "shinyNewId" it complains about parameter type conflict. I'll keep poking at it.
-
samlewis almost 11 yearsI had a few obvious errors, answer edited - hopefully it works now.
-
latj almost 11 yearsThis is worked. Just out of curiosity... I avoided option #2 that you gave me because I dont totally understand it. If I do a second query, isnt it going to be wrong if someone else hits that sequence between my two queries? Anyway, thanks again.