在事务中插入后获取 id (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17459094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting id after insert within a transaction (Oracle)
提问by latj
Let's say I have three tables: team, player, team_player. Table team_player is a bridge table allowing a "many to many" relationship.
假设我有三个表：team、player、team_player。表 team_player 是一个允许“多对多”关系的桥表。
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.
如何在同一事务中同时插入 team 和 team_player 行？也就是说，我想在提交到新的团队行之前插入所有 team_player 记录。我正在使用 JDBC 和 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).
当我尝试下面的代码时，即使 team.id 是一个数字（由触发器递增），teamId 也会填充一串字母。因此，这似乎不是我刚刚尝试插入（但尚未提交）的记录的 ID。
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?
这是我读到 RETURN_GENERATED_KEYS: How to get the insert ID in JDBC?
回答by samlewis
The Oracle JDBC Driver does not support getGeneratedKeys()- you are manually generating the keys in your trigger, presumably from a SEQUENCE.
Oracle JDBC 驱动程序不支持getGeneratedKeys()- 您正在触发器中手动生成键，大概是从SEQUENCE.
You can use Oracle's returning clause:
您可以使用 Oracle 的返回子句：
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:
或者使用第二个 SQL 查询获取最后一个序列号：
SELECT mysequence.CURRVAL FROM dual
回答by a_horse_with_no_name
You need to tell the driver whichcolumn to return.
您需要告诉驱动程序返回哪一列。
If your ID is populated by a trigger the following will work:
如果您的 ID 由触发器填充，则以下内容将起作用：
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);
}
回答by diy
Please refer to Statement.getGeneratedKeys()- it gives you back a resultset of generated keys.I believe this is what you seek.
请参阅Statement.getGeneratedKeys()- 它为您提供生成密钥的结果集。我相信这就是您所寻求的。
Basically, Spring jdbc uses this approach to retrieve generated ids (example from JdbcTemplate class)
基本上，Spring jdbc 使用这种方法来检索生成的 ID（来自 JdbcTemplate 类的示例）
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
一个不太优雅的解决方案是使用 Oracle 的RETURNING 子句，但您必须将插入包装到存储过程中以取回 id
