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
– Dan Bracuk, Commented Jul 3, 2013 at 22:47

samlewis · Accepted Answer · 2013-07-03 23:56:32Z

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 3, 2013 at 23:56

answered Jul 3, 2013 at 23:06

samlewis

4,05830 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

latj Over a year ago

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.

samlewis Over a year ago

I had a few obvious errors, answer edited - hopefully it works now.

latj Over a year ago

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.

samlewis Over a year ago

CURVAL returns the last sequence value for the current session so the problem you describe wouldn't happen.

user330315 Over a year ago

The Oracle JDBC driver does support getGeneratedKeys() if the column is populated by a trigger.

user330315 · Accepted Answer · 2013-07-04 06:41:06Z

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 ?

diy · Accepted Answer · 2013-07-03 23:04:48Z

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

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.
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

Suneel Srivastava · Accepted Answer · 2022-09-14 05:40:05Z

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.

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.

Collectives™ on Stack Overflow

Getting id after insert within a transaction (Oracle)

4 Answers 4

5 Comments

2 Comments

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

2 Comments

1 Comment

Linked

Related