在事务中插入后获取ID(Oracle) [英] Getting id after insert within a transaction (Oracle)

问题描述

比方说，我有三个表:team，player，team_player.表team_player是一个桥接表，允许多对多"关系.

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.

如何在同一交易中同时插入team和team_player行?也就是说，我想在提交到新团队行之前插入所有team_player记录.我正在使用JDBC和Oracle.

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.

当我尝试下面的代码时，即使team.id是一个数字(由触发器递增)，teamId仍由字母字符串填充.因此，这似乎不是我刚刚尝试插入的记录的ID(但尚未提交).

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

这是我读到的有关RETURN_GENERATED_KEYS的信息: 如何在JDBC中获取插入ID?

This is where I read about RETURN_GENERATED_KEYS: How to get the insert ID in JDBC?

推荐答案

Oracle JDBC驱动程序不支持getGeneratedKeys()-您正在触发器中手动生成密钥，大概是从SEQUENCE生成的.

The Oracle JDBC Driver does not support getGeneratedKeys() - you are manually generating the keys in your trigger, presumably from a SEQUENCE.

您可以使用Oracle的返回子句:

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

或使用第二个SQL查询获取最后一个序列号:

Or grab the last sequence number with a second SQL query:

SELECT mysequence.CURRVAL FROM dual

这篇关于在事务中插入后获取ID(Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！