Getting id after insert within a transaction (Oracle)

Question

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?

Oracle has sequences. They work very well in these sorts of situations. — Dan Bracuk, Jul 03 '13 at 22:47

samlewis · Accepted Answer · 2013-07-03T23:56:32.150

12

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

edited Jul 03 '13 at 23:56

answered Jul 03 '13 at 23:06

samlewis

3,950
27
27

Thanks 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. – latj Jul 03 '13 at 23:45
I had a few obvious errors, answer edited - hopefully it works now. – samlewis Jul 03 '13 at 23:59
This 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. – latj Jul 04 '13 at 00:10
CURVAL returns the last sequence value for the current session so the problem you describe wouldn't happen. – samlewis Jul 04 '13 at 00:23
The Oracle JDBC driver **does** support `getGeneratedKeys()` if the column is populated by a trigger. – Jul 04 '13 at 06:38

score 4 · Answer 2 · answered Jul 04 '13 at 06:41

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);
}

What if getGeneratedKeys() failed due to connection loss. How inserted record will be roll backed ? — Swapnil Gangrade, Sep 15 '17 at 13:47

score 1 · Answer 3 · answered Jul 03 '13 at 23:04

1

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

answered Jul 03 '13 at 23:04

diy

3,590
3
19
16

Yeah, 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. – latj Jul 03 '13 at 23:06
2

It 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 – diy Jul 03 '13 at 23:28

Suneel Srivastava · Answer 4 · 2022-09-14T05:40:05.503

0

declare
  autorowid varchar2(100);
begin
  INSERT INTO tablename(USERID,USID,MONTH,YEAR) 
  VALUES('testuser','134659573',2,2022)returning rowid into autorowid;
  dbms_output.put_line(autorowid);
end;

// In the Oracle database, directly run this query and get the rowid. Type same 
// query in java prepared statement.

edited Sep 14 '22 at 05:40

answered Sep 14 '22 at 05:35

Suneel Srivastava

1
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 19 '22 at 11:28

Getting id after insert within a transaction (Oracle)

4 Answers4

Linked