Getting id after insert within a transaction (Oracle)
java, jdbc, oracle, sql
Solution
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
Problem
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?