Getting ID after insertion inside a transaction (Oracle)

Question

Say I have three tables: team, player, team_player. The team_player table is a bridge table that allows you to use many-many relationships.

When someone wants to create a new team, they indicate the initial players in this team.

How to insert both a team and team_player commands into a single transaction? That is, I would like to insert all the team_player entries before moving on to the new team. I use JDBC and Oracle.

When I try to execute the code below, teamId is populated with a string of letters, although team.id is a number (which is incremented with a trigger). So this is not like the id of the record I was just trying to insert (but not committed 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(); 

Here I read about RETURN_GENERATED_KEYS: How to get the insert ID in JDBC?

Answer 1

The Oracle JDBC driver does not support getGeneratedKeys() - you manually generate keys in your trigger, presumably from SEQUENCE .

You can use the Oracle return 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 take the last sequence number with the second SQL query:

 SELECT mysequence.CURRVAL FROM dual 

Answer 2

You need to tell the driver where the column will be returned.

If your id is populated with 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); } 

Answer 3

Refer to Statement.getGeneratedKeys () - it returns the result of the generated keys to you. I believe that this is what you are looking for.

Basically, Spring jdbc uses this approach to retrieve the generated identifiers ( example from the JdbcTemplate class )

A less elegant solution would be to use the Oracle RETURNING clause , but you will have to wrap you insert in the saved process to return id